An article explaining Node+mysql's SQL injection

Although we will not directly use native NodeJS to develop the backend, it is still necessary to understand SQL injection.

This article uses NodeJS MySQL to explain SQL injection.

SQL injection attack is a very old attack method. There have been SQL injection attacks since the birth of web2.0. It usually appears in front-end components such as input box and text field. Add SQL statement to the input content and pass it to the background together. [Related tutorial recommendations: nodejs video tutorial]

If the background is not careful, the SQL statement passed from the front end will be spliced ​​into its own SQL statement, and finally spliced ​​into an attack code.

Therefore, precautions must be taken, otherwise data leakage may occur, or the database may even be deleted.

SQL injection demonstration

Take login as an example, I add a users table in MySQL to store users name and password.

In the users table, I created a piece of data: insert into users (username, password, realname) values ​​('leihou', '123 ', 'Thunder Monkey');

The data means:

• username: 'leihou'
• password: '123'
• realname: 'Thunder Monkey'

At this time, in the NodeJS background, I Created a login method

const mysql = require('mysql')

// 创建连接对象
const con = mysql.createConnection({
    host: 'localhost', // 地址
    user: 'root', // 连接数据库的用户
    password: '123456', // 连接数据库的密码
    port: '3306', // 默认端口
    database: 'testdb' // 数据库名
})

// 开始连接
con.connect()

// 统一执行 sql 的函数
function exec(sql) {
  const promise = new Promise((resolve, reject) => {
    con.query(sql, (err, result) => {
      if (err) {
        reject(err)
        return
      }
      resolve(result)
    })
  })
  return promise
}

// 登录方法
const login = (username, password) => {
  const sql = `
    select username, realname from users where username='${username}' and password='${password}';
  `

  console.log(sql)
  return exec(sql).then(rows => {
    return rows[0] || {}
  })
}

The above is the login method.

Finally, you can create an interface to the front end through the method mentioned in "NodeJS http request". Since the interface part is not the focus of this article, I am going to skip it here (let me be lazy).

At this time, create another HTML page, roughly generate some content, and then use Ajax to connect with the backend.

If you are lazy, you can directly use postman Test

##You can know according to the above

Login method , you can log in successfully by entering the following content on the front end

Username: leihou
Password: 123

But if at this time, the username entered is

leihou' -- , note that there are spaces before and after --. Then the password can be entered casually.

The final spliced ​​

SQL statement is select username, realname from users where username='leihou' -- ' and password='aslkfjsaf';

Note that I entered the password casually.

In

MySQL, -- represents the meaning of comments. So the above statement becomes Query the data whose username is leihou. Naturally, the password is bypassed.

The content of

username entered above bypasses login and leaks information. But if someone else wants to delete your table, the consequences will be very serious.

For example, enter in the user name input box:

leihou'; delete from users; -- .

The

users table was deleted directly.

Prevention methods

SQL injection attack It is too old, more than ten years old. So the basic coping methods are mature.

For example, transcode the string passed from the front end.

Using

NodeJS The downloaded MySQL dependency package provides this method: escape.

// 省略部分代码
const mysql = require('mysql')

// 省略创建连接对象
// 省略开始连接
// 统一执行 sql 的函数 exec 方法

const escape = mysql.escape

const login = (username, password) => {
  username = escape(username)
  password = escape(password)
  const sql = `
    select username, realname from users where username=${username} and password=${password};
  `

  console.log(sql)
  return exec(sql).then(rows => {
    return rows[0] || {}
  })
}

The string filtered using the

escape method will be escaped.

At this time, if the user name enters

leihou' -- , the following content will be printed on the backend console:

select username, realname from users where username='leihou\' -- ' and password='123345';

You can see

leihou'# The single quotes following ## are escaped. The above are

MySQL

methods to prevent SQL injection attacks. For more node-related knowledge, please visit:

nodejs tutorial

!

The above is the detailed content of An article explaining Node+mysql's SQL injection. For more information, please follow other related articles on the PHP Chinese website!