Methods to prevent SQL injection in node-mysql

不言
Release: 2018-06-30 11:38:54
Original
2867 people have browsed it

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)
Copy after login

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'
Copy after login

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.


Method 2: Use the query parameter placeholder of connection.query():


Can be used? as a query parameter Placeholder. When using query parameter placeholders, theconnection.escape()method is automatically called internally to encode the incoming 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'
Copy after login

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'
Copy after login

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)
Copy after login

For example:

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

Method 4: Use mysql.format() to escape parameters:

Prepare the query, this function The appropriate escape method will be selected to escape the parametersmysql.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
Copy after login

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!

Related recommendations:

About the difference between fs.stat and fs.fstat in node.js

NodeJs form-data format transmission File method

About Nodejs server-side character encoding, decoding and garbled processing


The above is the detailed content of Methods to prevent SQL injection in node-mysql. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!