How can I extend an SQL query in a variable?

Question

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);

The driver shouldn't allow you to inject SQL in ID. It's not clear to me what API you are using but generally drivers will sanatize or precompile your statement before inserting values. SQL injection generally happens when you manually concatenate. — AminM
– AminM, Commented Jan 29, 2022 at 19:43
Looks like a security hole in your language or API. Do not concatenate parameters, but use "prepared statements" instead. — The Impaler
– The Impaler, Commented Jan 29, 2022 at 19:48

O. Jones · Accepted Answer · 2022-01-29 20:18:51Z

2

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.

edited Jan 29, 2022 at 20:18

answered Jan 29, 2022 at 20:13

O. Jones

110k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

O. Jones Over a year ago

Why is SQL injection dangerous? See haveibeenpwned.com for some hints.

Arcanus Over a year ago

Thank you for the very clear and detailed explanation. Just learned a lot, thanks.

Joel Coehoorn · Accepted Answer · 2022-01-29 20:05:24Z

1

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.

answered Jan 29, 2022 at 20:05

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

2 Comments

Arcanus Over a year ago

Can you please elaborate a little on what exactly parametrized is in your answer? Is it built-in? If so, then how are sql-injections possible?

Joel Coehoorn Over a year ago

It's also called "prepared statements", and most libraries (at least, those worth using) do have the capability built in. That SQL injections are NOT possible is the whole point. SQL injection happens when someone uses raw string concatenation instead of the parameterized query feature.

Collectives™ on Stack Overflow

How can I extend an SQL query in a variable?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related