How can this Parametrized Query Prevent SQL Injection?

Question

I know that parametrized queries are used to prevent SQL injection, but how can this prevent an SQL injection? Can't someone just set their id equal to ; DROP TABLE * -- and just insert that into the parametrized query anyway?

let updateQueryData = `UPDATE table SET lookups = $1 WHERE id = $2`;
        let updateQueryValues = [numLookups, data.rows[0].id];
        pool.query(updateQueryData, updateQueryValues, err => {

`WHERE id = DROP TABLE *` would fail due to syntax error. Try running that in a query. — Ryan Wilson, Jan 14 '20 at 17:20
When you use a parameterised querys the query engine doesn't just parse your params and place them into the query string for you. It gets injected directly into the query engine. As such it santatizes the input preventing query injection. — Keith, Jan 14 '20 at 17:22
They key difference is that parameters instruct the database query engine that these are *values* and should be treated as such, whereas direct string concatenation conveys no such information to the query engine and it simply executes whatever *code* you give it. In the former the placeholders and values are kept separate until they get to the DB and allows the DB to handle them properly, which it knows how to do. A user certainly can supply what looks like code as a value, but it would just be a string value and nothing more. — David, Jan 14 '20 at 17:31
Does this answer your question? [How can prepared statements protect from SQL injection attacks?](https://stackoverflow.com/questions/8263371/how-can-prepared-statements-protect-from-sql-injection-attacks) — Thomas F, Jan 14 '20 at 17:46

score 1 · Answer 1 · answered Jan 14 '20 at 17:21

No. The data is not simply inserted into the text representation of the query. It is sent separately.

To prevent injection, the data must be separate from the command, so that there is no ambiguity between the data and the command. This is exactly what a parameterized query does.

(Note: There are some libraries that do still send the query with the data all-in-one, but all the data is automatically "escaped" so that it is still safe for use.)

Also, I would highly recommend removing those backticks and replacing with regular quotes so you don't accidentally concatenate data into that query in the future.

score 0 · Answer 2 · answered Jan 14 '20 at 17:21

UPDATE table SET lookups = $1 WHERE id = $2

Your query is parameterized already.

Here is what would happen if someone passes a malicious value like '; DROP TABLE * --':

if the corresponding column is of string datatype, then the query becomes something like:

UPDATE table SET lookups = '; DROP TABLE * --' WHERE id = 1

if the column is numeric, you will get a runtime error because '; DROP TABLE * --' is not a number

How can this Parametrized Query Prevent SQL Injection?

2 Answers2