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);
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.
You can't do it this way. In fact, not being able to do this is the WHOLE POINT of parameterized queries. It prevents an attacker from giving you a string like 122; DROP Table users; as the input.