javascriptmysqltypeorm

Can we use parameters and String Interpolation in typeORM in the same query?

i have an sql query in which im inserting multiple values like so

INSERT INTO form_transaction_setup(value1, value2, value3, value4, value5 ,..., value19)
VALUES('${value1}', '${value2}','${value3}', '${value4}', '${value5}', ... , '${value19}')

All of these values except one are grabbed from a variety of different tables and are guaranteed to be sanitized properly. Value 5 however, comes from the client-side and can contain apostrophes, commas, and ofcourse is vulnerable to SQL Injection. I wanted to specify value 5 as a parameter in my sql query which is being built via typeORM, but it keeps giving me a syntax error where I place the question mark(?) to represent the parameter.

This is the query that I have tried

await getConnection().query(
`INSERT INTO form_transaction_setup(value1, value2, value3, value4, value5 ,..., value19)
VALUES('${value1}', '${value2}','${value3}', '${value4}', ? , ... , '${value19}',[value5])`

have also tried alternatives such as ['${value5}'] or ['value5'], thinking that the problem is in the parameters. But logging the query shows value 5 being correctly passed to the database. This is the error I get

code: 'EREQUEST',
[0]   originalError: Error: Incorrect syntax near '?'

In brief, can I use parameters and string interpolation in the same query while using typeORM, and if yes, what am I doing wrong? Thanks in advance.

Edit: Using parameters for all the entries was my initial choice but it causes a performance delay that becomes significant when the query is called repeatedly. (Depending on the function, it may be called 100+ times as more and more data is received)

Solution

  • Found the solution on this page https://github.com/typeorm/typeorm/issues/881. typeORM has multiple different ways to declare variables but which one is valid depends on the underlying driver. I was using mssql and mssql requires that we declare our parameters as @0, @1... and so on. So the correct query is

    `INSERT INTO form_transaction_setup(value1, value2, value3, value4, value5 ,..., value19)
VALUES('${value1}', '${value2}','${value3}', '${value4}', @0 , ... , '${value19}',[value5])`

    and then declaring the parameter normally as follows

    await getConnection().query("our corrected query here"), [value5]