Preventing SQL injection in Node-mysql

Note: This article is for Mysqljs/mysql.

To prevent SQL injection, you can encode incoming parameters in SQL instead of string concatenation directly. In Node-mysql, there are four common ways to prevent SQL injection:

Method One: Use Escape () to encode the incoming parameters:

The parameter encoding method has the following three:

mysql.escape(param)connection.escape(param)pool.escape(param)

For example:

var userId = 1, name = ‘test‘;var query = connection.query(‘SELECT * FROM users WHERE id = ‘ + connection.escape(userId) + ‘, name = ‘ + connection.escape(name), function(err, results) { // ...});console.log(query.sql); // SELECT * FROM users WHERE id = 1, name = ‘test‘

The Escape () method encodes the following rules:

• Numbers not to convert;
• Booleans converted to True/false;
• The date object is converted to the ' Yyyy-mm-dd HH:ii:ss ' string;
• Buffers converted to a hex string, such as X ' 0fa5 ';
• strings for safe escaping;
• Arrays converted to a list, such as [' A ', ' B '] will be converted to ' a ', ' B ';
• A multidimensional array is converted to a list of groups, such as [[' A ', ' B '], [' C ', ' d ']], which are converted to ' a ', ' B '), (' C ', ' d ');
• The objects is converted to the form of a Key=value key-value pair. The nested object is converted to a string;
• Undefined/null will be converted to null;
• MySQL does not support nan/infinity and will trigger MySQL errors.

Method Two: Use the query parameter placeholder for connection.query ():

Can be used as a placeholder for query parameters. When you use the query parameter placeholder, the Connection.escape () method is automatically called inside it to encode the passed-in parameter.

Such as:

var userId = 1, name = ‘test‘;var query = connection.query(‘SELECT * FROM users WHERE id = ?, name = ?‘, [userId, name], function(err, results) { // ...});console.log(query.sql); // SELECT * FROM users WHERE id = 1, name = ‘test‘

The above procedure can also be rewritten as follows:

var post  = {userId: 1, name: ‘test‘};var query = connection.query(‘SELECT * FROM users WHERE ?‘, post, function(err, results) { // ...});console.log(query.sql); // SELECT * FROM users WHERE id = 1, name = ‘test‘

Method Three: Use Escapeid () to encode the SQL query identifier:

If you do not trust the user's incoming SQL identifier (database, table, character name), you can encode it using the Escapeid () method. Most commonly used in sorting and so on. Escapeid () has the following three functionally similar methods:

mysql.escapeId(identifier)connection.escapeId(identifier)pool.escapeId(identifier)

For example:

var sorter = ‘date‘;var sql    = ‘SELECT * FROM posts ORDER BY ‘ + connection.escapeId(sorter);connection.query(sql, function(err, results) { // ...});

Method Four: Use the Mysql.format () escape parameter:

To prepare the query, the function chooses the appropriate escape method escape parameter Mysql.format () to prepare the query statement, which automatically selects the appropriate method to escape the parameter.

For example:

var userId = 1;var sql = "SELECT * FROM ?? WHERE ?? = ?";var inserts = [‘users‘, ‘id‘, userId];sql = mysql.format(sql, inserts); // SELECT * FROM users WHERE id = 1

Reference article:

1. Https://github.com/mysqljs/mysql

2. http://itbilu.com/nodejs/npm/NyPG8LhlW.html

remark: node. JS interacts with MySQL with many libraries, which can be viewed in https://www.npmjs.org/search?q=mysql.

ref:http://www.dengzhr.com/node-js/877

Preventing SQL injection in Node-mysql