How can I extend an SQL query in a variable?

I am testing possible SQL injections on my DB, and I am running a simple function to get results which a user should not be getting. The return value is correct based on id, however, the rest of the query is completely being ignored.

I want to return all the data from the data table.

Is there something wrong in my syntax?

Here is my implementation:

function test(id) {
  db.query("SELECT * FROM users WHERE id = ?", [id], (err, result) => {
    console.log(result[0]);
  });
}

const id = "122 UNION SELECT * FROM data";
test(id);

Answer

This looks like nodejs Javascript with the npm mysql driver package. And, I guess your id column is defined as an INT or BIGINT, not as some kind of text string.

The way you use the .query() method is the correct way to prevent SQL injection. It’s parameterized. That means each parameter in the SQL is represented by a ? placeholder. The second argument to .query() is an array of parameter values to substitute for the placeholders. For your use case the driver generates a query looking like this.

 SELECT * FROM users WHERE id = '122 UNION SELECT * FROM data' 

and passes it to the MySQL server. The server then takes the string you passed and attempts to interpret it as a number. Due to a quirk in MySQL, it interprets your '122 UNION SELECT * FROM data' string as the number 122, and so looks up WHERE id = 122. (MySQL coerces strings to integers by looking for a leading number. So 123RedLight gives 123, and Hello gives 0. It can be confusing. Other makes and models of RDBMS throw errors when given strings where they expect integers.)

It correctly ignores the rest of your string.

If you wanted to make your code vulnerable to SQL injection (you do not want to do that!) you would write

function test(id) {   /* danger: sql injection in next line */
  db.query("SELECT * FROM users WHERE id = " + id, (err, result) => { /* wrong ! */
    console.log(result[0]);
  });
}

This would send

 SELECT * FROM users WHERE id = 122 UNION SELECT * FROM data

to the server, and give you your data leak.