Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to prevent SQL injection in node-mysql

2025-01-27 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)05/31 Report--

Node-mysql in how to prevent SQL injection, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Brief introduction of SQL injection

SQL injection is one of the more common network attacks, it does not use the BUG of the operating system to achieve the attack, but aims at the programmer's negligence in programming, through the SQL statement to achieve no account login, or even tamper with the database.

Prevent SQL injection in node-mysql

To prevent SQL injection, you can encode the parameters passed in the SQL instead of directly concatenating strings. In node-mysql, there are four common ways to prevent SQL injection:

Method 1: use escape () to encode the incoming parameters:

There are three parameter coding 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 coding rules for escape () method are as follows:

Numbers does not convert

Convert Booleans to true/false

Convert a Date object to a 'YYYY-mm-dd HH:ii:ss' string

Convert Buffers to a hex string, such as Xroom0fa5'

Strings performs security escape

Arrays is converted to a list, for example, ['averse,' b'] is converted to 'averse,' b'

A multidimensional array is converted to a list of groups, for example, [['axiom,' b'], ['clocked,' d']] is converted to 'axiom,' b'), ('censor,' d')

Objects is converted to key=value key-value pairs. Convert nested objects 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 ():

Usable? As a query parameter placeholder. When the query parameter placeholder is used, the connection.escape () method is automatically called inside 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 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 don't trust the SQL identifiers (database, table, character name) passed in by the user, you can use the escapeId () method to encode them. Most commonly used for sorting, etc.

EscapeId () has three functionally similar methods:

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:

To prepare the query, the function will select the appropriate escape parameter mysql.format () to prepare the query statement, and the function will automatically select the appropriate method escape parameter.

For example:

Var userId = 1 mysql.format sql = "SELECT * FROM?? WHERE??"; var inserts = ['users',' id', userId]; sql = mysql.format (sql, inserts); / / SELECT * FROM users WHERE id = 1 is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 288

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report