Typeorm, Backticks vs. Colon in Query-CodePudding

I have been writing queries using backticks

const firstUser = await connection
    .getRepository(User)
    .createQueryBuilder("user")
    .where(`user.id = '${id}'`)
    .getOne();

but in typeorm documentations, examples are written with colons.

const firstUser = await connection
    .getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id", { id: 1 })
    .getOne();

So I'm wondering if there is any difference between using backticks and colons.

Any insights would be greatly appreciated.

thanks.

CodePudding user response：

DO NOT USE STRING INTERPOLATION TO INSERT VALUES INTO SQL STATEMENTS

Sorry to yell, but this is important.

Read this: https://en.wikipedia.org/wiki/SQL_injection
And then this: https://xkcd.com/327/

When you do this:

.where(`user.id = '${id}'`)

Then the string is created first and then passed to the where() function. Which means if id is 123, then it's the same as:

.where(`user.id = '123'`)

Which seems fine. But what if id is 123' OR 1=1--'

You now get this:

.where(`user.id = '123' OR 1=1--'`) // Now returns ALL users!

This is called SQL injection, it's a big security issue. It's very very bad. Attackers could alter your queries and get access to data they shouldn't have access to, or change records to give themselves admin access, or all kinds of other really bad things. It's pretty close to giving everyone full read/write access to your database.

Which brings us to this:

.where("user.id = :id", { id: 1 })

To combat this, you ask TypeORM to put values in for you, this correctly escapes any value an attacker may add so the input has no executable instructions.

If id is something nefarious, then it will get turned into something like:

.where(`user.id = '123\' OR 1=1--'`) // note the backslash
// (actual query may vary based on different databases)

Here TypeORM ensures escape that the id is processed as a value, by escaping the close quote that the attacker inserted. This makes it safe to get user provided values and use them in your queries.

In conclusion, NEVER put user provided values interpolated directly into queries (really should be all values, just to be safe), and always use query parameters to ensure that values are properly escaled.