Methods to prevent SQL injection in node-mysql

Everyone knows that SQL injection is a very dangerous problem for websites or servers. If this aspect is not handled well, the website may be injected at any time, so this article summarizes the issues in node-mysql. Friends in need can refer to several common practices to prevent SQL injection.

SQL injection introduction

SQL injection is one of the more common network attack methods. It does not use the BUG of the operating system to achieve the attack. Instead, it is aimed at the programmer's negligence in programming, through SQL statements, to achieve login without an account, and even tamper with the database.

Prevent SQL injection in node-mysql

In order to prevent SQL injection, you can encode the parameters passed in SQL instead of directly String concatenation. In node-mysql, there are four common methods to prevent SQL injection:

Method 1: Use escape() to encode the incoming 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'

escape() method encoding rules are as follows:

Numbers are not converted;

Booleans Convert to true/false;

Date object is converted to 'YYYY-mm-dd HH:ii:ss' string;

Buffers are converted to hex string, Such as ', 'b';

Multidimensional arrays are converted to group lists, such as [['a', 'b'], ['c', 'd']] will be converted to 'a' , 'b'), ('c', 'd');

Objects will be converted into key=value pairs. Nested objects are converted to strings;

undefined/null will be converted to NULL;

MySQL does not support NaN/Infinity and will trigger a MySQL error.

Can be used? as a query parameter Placeholder. When using query parameter placeholders, the connection.escape() method is automatically called internally to encode the incoming parameters. For example:

<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>var userId = 1, name = &amp;#39;test&amp;#39;; var query = connection.query(&amp;#39;SELECT * FROM users WHERE id = ?, name = ?&amp;#39;, [userId, name], function(err, results) { // ... }); console.log(query.sql); // SELECT * FROM users WHERE id = 1, name = &amp;#39;test&amp;#39;</pre><div class="contentsignin">Copy after login</div></div>

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

If you do not trust the SQL identifier passed in by the user (database, table , character name), you can use the escapeId() method to encode. Most commonly used for sorting etc. escapeId()There are three methods with 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 mysql.format() to escape parameters:

Prepare the query, this function The appropriate escape method will be selected to escape the parameters mysql.format() is used to prepare the query statement. This function will automatically select the appropriate method to escape the parameters. 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

The above is the entire content of this article. I hope it will be helpful to everyone’s study. For more related content, please pay attention to PHP Chinese website!