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, Apr 29 '19 at 01:52
Take a look at https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work?rq=1 and https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 although not node.js specific there is lots of useful information about injection — Nick, Apr 29 '19 at 01:59
The example is just plain wrong. It still has sql injection vulnerabilities. I recommend finding a better tutorial. — Sam M, Apr 29 '19 at 02: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., Apr 29 '19 at 02:05
_"It said the following code prevents SQL injections"_ it's wrong. Parameter binding is the optimal solution to this problem — Phil, Apr 29 '19 at 02:10
SQL Injection occurs when un-escaped user data is allowed to get into your SQL. This is dangerous because a malicious user could end your SQL statement with an apostrophe and then start a new SQL statement that your code will execute. — Michael Z., Apr 29 '19 at 02:10
Possible duplicate of [Preventing SQL injection in Node.js](https://stackoverflow.com/questions/15778572/preventing-sql-injection-in-node-js) — Phil, Apr 29 '19 at 02:12
@Michael Z. thank you. I'll think on it harder to understand. — Jin Lee, Apr 29 '19 at 02:16

score 12 · Accepted Answer · edited Apr 29 '19 at 02:22

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. — Michael Z., Apr 29 '19 at 02:20

Wang Liang · Answer 2 · 2019-04-29T01:55:57.923

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

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

2 Answers2