Summary of methods to prevent SQL Injection in node-mysql, node-mysqlsql

Summary of methods to prevent SQL Injection in node-mysql, node-mysqlsql

SQL Injection

SQL injection is one of the most common methods of network attacks. It does not use Operating System bugs to launch attacks, but is aimed at the negligence of programmers during programming. through SQL statements, you can log on without an account, or even tamper with the database.

Preventing SQL Injection in node-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 using the placeholder query parameter, it is automatically called internally connection.escape() Method 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()There are 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 for query. This function selects the appropriate escape method escape parameter.mysql.format()Used to prepare a query statement. This function automatically selects an 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

Summary

The above is all about this article. I hope this article will help you in your study or work. If you have any questions, please leave a message.