How is 'Escaping query values' safe in sql? (Or why is it dangerous?) [SQL injection]

Question

I'm following Node.js with sql examples on W3schools. here

It said the following code prevents SQL injections.

var adr = 'Mountain 21';
var sql = 'SELECT * FROM customers WHERE address = ' + mysql.escape(adr);
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});

When query values are variables provided by the user, you should escape the values.This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.

This was the explanation.

I want to understand how this is safe. (how this prevents SQL injections).

Also, how is the following code dangerous?
var sql = 'SELECT * FROM customers WHERE address = "Mountain 21"';

var adr = 'Mountain 21; DROP TABLE customers;'; - try it on your dev ;-) be careful — Alex
– Alex, Commented Apr 29, 2019 at 1:52
Take a look at stackoverflow.com/questions/332365/… and stackoverflow.com/questions/60174/… although not node.js specific there is lots of useful information about injection — Nick
– Nick, Commented Apr 29, 2019 at 1:59
The example is just plain wrong. It still has sql injection vulnerabilities. I recommend finding a better tutorial. — Sam M
– Sam M, Commented Apr 29, 2019 at 2:03
@Alex that looks like a valid string to me. No reason that couldn't be saved in a database just fine. Maybe I'm missing it too, but I think you would need an extra apostrophe to make this work. — Michael Z.
– Michael Z., Commented Apr 29, 2019 at 2:05
"It said the following code prevents SQL injections" 👈 it's wrong. Parameter binding is the optimal solution to this problem — Phil
– Phil, Commented Apr 29, 2019 at 2:10

Phil · Accepted Answer · 2019-04-29 02:22:46Z

Unprotected string concatenation to generate a SQL statement is dangerous if the injected value (i.e. "Mountain 21") is source from an uncontrolled external source. For example, it is entered by a user.

Consider a plain string concatenation as follows:

var adr = <something accepted from an external source>
var sql = `SELECT * FROM customers WHERE address = "${adr}"`;

Then consider what might happen if the user entered the following into the text field:

Mountain 21"; delete all from customers; //

The query would become: SELECT * FROM customers WHERE address = "Mountain 21"; delete all from customers; //"

If you ran that, you would probably end up with no customers in your table.

I am not personally familiar with the operation of the node.js mysql.escape function, but typically these sorts of functions "escape" special characters so they lose their "special-ness". For example, it might put a \ in front of the ; to remove it's significance as a statement separator.

Another more common example of what the escape function will typically do is convert a piece of text such as "O'Brien" to "O''Brien" (two single quotes is the way to specify a single quote in an SQL text string). A query that uses the "O'Brien" name would look something like this:

select *
from customers
where name = 'O''Brien';

The mySql.escape function will almost certainly provide the necessary conversion of "O'Brien" into "O''Brien" so that it can properly be run in an SQL query. Without the escape, the last line of the query would read:

where name = 'O'Brien';

which would result in a syntax error.

FWIW, The safest way is to use ? placeholders in your query for user supplied values (e.g. the address). This is a bit more cumbersome as you need to prepare your query, supply all of the values and then execute it. However, the benefit is that this is (should be?) completely immune to most, if not all, forms of "injection attack".

The basic flow for a parameterised query as per your example is (in java'ish pseudocode - as I don't about node.js's capabilities in this area) is:

val sql = "SELECT * FROM customers WHERE address = ?";
val preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString (1, adr);
val resultSet = preparedStatement.executeQuery();

Most if not all databases support parameterised queries, most languages expose this capability, but not all do expose it (or at least not easily). Again, I'm not sure about node.js.

I hope this helps you.

I think you did a decent job of explaining this and I'll +1 even though there are several very good explanations you could have linked to.

Wang Liang · Accepted Answer · 2019-04-29 01:55:57Z

1

var adr = 'Mountain 21';
var sql = `SELECT * FROM customers WHERE address = "${mysql.escape(adr)}"`;
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});

var sql = 'SELECT * FROM customers WHERE address = Mountain 21';
To
var sql = 'SELECT * FROM customers WHERE address = "Mountain 21"';

https://stackoverflow.com/a/33679883/11343720

The Grave accent is better quote, double quote

edited Apr 29, 2019 at 1:55

answered Apr 29, 2019 at 1:47

Wang Liang

4,4527 gold badges26 silver badges50 bronze badges

Collectives™ on Stack Overflow

How is 'Escaping query values' safe in sql? (Or why is it dangerous?) [SQL injection]

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related