Are the escape functions in the Node.js mysql package sufficient enough to securely query a mysql database (without the use of prepared statements)?

Question

According to Node.js' mysql package documentation:

In order to avoid SQL Injection attacks, you should always escape any user provided data before using it inside a SQL query. You can do so using the mysql.escape(), connection.escape() or pool.escape() methods.

I cannot find any documentation / reference to using prepared statements with mysql, except for in a reference to using '?' characters. It states the following:

Alternatively, you can use ? characters as placeholders for values you would like to have escaped...

This looks similar to prepared statements in MySQL, however it really just uses the same connection.escape() method internally.

From my experience with talking to other developers, the general consensus in the developer community is that prepared statements are the ONLY safe way to perform mysql queries from Node.js however, as you can see with the mysql package, there is no obvious support for prepared statements. However, it is indicated that their method for avoiding SQL injection is via the usage of the escape functions.

My Question:

Are the escape functions in the mysql package sufficient enough to securely query a mysql database (without the use of prepared statements)?

Answer 1

Escaping is actually just as safe as using parameterized queries, if you do it consistently.

But it's recommended to use parameters because it makes your code simpler. Therefore developers are probably more likely to do it.

If the Node.js library makes it just as convenient as parameters, but implements it internally by modifying query strings, replacing the ? marks with the parameter values, then you're good.

For what it's worth, Python's MySQL driver does this too, and also PHP's PDO extension when the "emulate prepares" option is in effect. As long as those libraries are well-tested, it should be safe.

FWIW, both escaping and parameters is limited in SQL injection prevention, because in both cases, you can only protect values that you would combine with your SQL query. You cannot protect identifiers (like table names, or column names), or SQL keywords, or expressions, etc. In these cases, just be careful that you have vetted any dynamic content before combining it with your SQL query.

See also:

• Preventing SQL injection in Node.js
• Difference between real_escape_string and prepare()?