A WARNING AGAINST USING STRING INTERPOLATION FOR SQL STATEMENTS
Apologies for the shouting, but this is crucial.
When you utilize this method:
.where(`user.id = '${id}'`)
The string is constructed beforehand and then passed to the where() function. So, if id equals 123, it's essentially the same as:
.where(`user.id = '123'`)
This might seem okay. However, what if id is 123' OR 1=1--'
You will end up with:
.where(`user.id = '123' OR 1=1--'`) // Now returns ALL users!
This dangerous practice is known as SQL injection, posing severe security risks. Attackers can manipulate your queries, gain unauthorized data access, alter records to grant themselves admin privileges, or execute various malicious activities. It's almost like granting everyone unrestricted database control.
Which brings us to the solution:
.where("user.id = :id", { id: 1 })
To safeguard against this threat, TypeORM handles value insertion for you, ensuring proper escaping of any potentially harmful inputs to prevent execution of unauthorized commands.
If id contains suspicious content, it will be transformed into something like:
.where(`user.id = '123\' OR 1=1--'`) // note the backslash
// (actual query may vary based on different databases)
This approach secures the processing of user-provided values by correctly escaping manipulated characters inserted by attackers. Thus, enabling safe inclusion of such values in your queries.
In summary, AVOID directly interpolating user inputs into queries (ideally all inputs), and instead, always rely on query parameters to ensure proper value handling and prevention of SQL injection vulnerabilities.