Common practices for preventing SQL injection in node-mysql

Note: This article focuses on mysqljs/mysql.

To prevent SQL injection, you can encode the input parameters in SQL, rather than directly concatenate strings. In node-mysql, there are four common methods to prevent SQL injection:

Method 1: Use escape () to encode the input parameters:

There are three parameter encoding methods:

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 encoding rules are as follows:
Numbers is not converted;
Booleans to true/false;
The Date object is converted to the 'yyyy-mm-dd HH: ii: SS' string;
Buffers is converted to a hex string, for example, X' 0fa5 ';
Strings for security escape;
Arrays is converted to a list. For example, ['A', 'B'] is converted to 'A', 'B ';
Convert a multi-dimensional array to a group list. For example, [['A', 'B'], ['C', 'D'] will be converted to 'A ', 'B'), ('C', 'D ');
Objects is converted to a key = value pair. The nested object is converted to a string;
Undefined/null is converted to NULL;
MySQL does not support NaN/Infinity and will trigger a MySQL error.

Method 2: Use the placeholder parameters of connection. query:

Available? As a placeholder for query parameters. When the placeholder parameters are queried, the connection. escape () method is automatically called internally to encode the input parameters.

For example:

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 program 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 3: Use escapeId () to encode the SQL query identifier:

If you do not trust the SQL identifier (database, table, and character name) passed in by the user, you can use the escapeId () method for encoding. It is most commonly used for sorting. EscapeId () has the following three similar functions:

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 4: Use the mysql. format () escape parameter:

Prepare the query. This function selects the appropriate escape method escape parameter mysql. format () to prepare the query statement. This function automatically selects the appropriate method escape 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