Summary of methods to prevent SQL injection in Node-mysql _ database other

Introduction to SQL Injection

SQL injection is one of the more common ways of network attack, it is not the use of operating system bugs to achieve the attack, but the programmer's negligence in programming, through SQL statements, no account login, or even tamper with the database.

Preventing SQL injection in Node-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 an incoming parameter:

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.esca PE (name), function (err, results) {
  //...
});
Console.log (Query.sql); SELECT * FROM users WHERE id = 1, name = ' Test '

The Escape () method encoding rule is as follows:

Numbers not to be converted;

Booleans conversion to True/false;

The date object is converted to a ' yyyy-mm-dd HH:ii:ss ' string;

Buffers converted to hex strings, such as X ' 0fa5 ';

strings for safe escaping;

Arrays is 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 is converted to ' a ', ' B ', (' C ', ' d ');

Objects will be 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 can trigger MySQL errors.

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

Can be used? As a placeholder for query parameters. When a query parameter placeholder is used, the method is automatically invoked inside it connection.escape() to encode the incoming 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 user incoming SQL identifiers (databases, tables, character names), you can encode them using the Escapeid () method. Most commonly used in sorting and so on.

escapeId()There are three methods that are similar in function:

Mysql.escapeid (identifier)
Connection.escapeid (identifier)
Pool.escapeid (identifier)

For example:

var sorter = ' Date ';
var sql  = ' SELECT * from posts ' + Connection.escapeid (sorter);
Connection.query (SQL, function (err, results) {
 //...
});

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

Prepares the query, which selects the appropriate escape method escape parameter mysql.format() to prepare the query statement, which automatically selects the appropriate method escape parameter.

For example:

var userId = 1;
var sql = "Select * from??" WHERE?? = ?";
var inserts = [' Users ', ' IDs ', userId];
sql = Mysql.format (sql, inserts); SELECT * FROM users WHERE id = 1

Summarize

The above is the entire content of this article, I hope the content of this article for everyone's study or work to bring certain help, if you have questions you can message exchange.