NodeJS sql注入漏洞审计和修复

一、问题代码

async search(
  ……
  let rows = await getManager().query(`
    SELECT DISTINCT(id),
        name,
        description,
        gmt_modified
    FROM(
      SELECT a.*, 
      ……
      WHERE name LIKE '%${name}%' OR tag_name LIKE '%${name}%'
      ORDER BY gmt_modified DESC 
      LIMIT 20
  `);
  …….

二、黑盒测试过程：

/api/search?name=123’

{“success”:false,”errormsg”:”er_parse_error: you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near ‘Ͽor tag_name like ‘3’Ͽn order by gmt_modified desc \n limit’ at line 18”}

/api/search?name=123’’

{“success”:true,”data”:[]}

/api/search?name=123'and’’=‘

{“success”:true,”data”:[]}

/api/search?name='and if(1=1,exp(999),3)and'1

{“success”:false,”errormsg”:”er_data_out_of_range: double value is out of range in ‘exp(999)’”}

/api/search?name='and if(1=2,exp(999),3)and'1

{“success”:true,”data”:[{“id”:82,……}]}

三、修复方案

1. mysql.escapeId(identifier)、connection.escapeId(identifier) 或 pool.escapeId(identifier)

var sorter = 'date';
var sql    = 'SELECT * FROM posts ORDER BY ' + connection.escapeId(sorter);
connection.query(sql, function (error, results, fields) {
  if (error) throw error;
  // ...
});

var sorter = 'date';
var sql    = 'SELECT * FROM posts ORDER BY ' + connection.escapeId('posts.' + sorter);
// -> SELECT * FROM posts ORDER BY `posts`.`date`

2. reparing Queries

var sql = "SELECT * FROM ?? WHERE ?? = ?";
var inserts = ['users', 'id', userId];
sql = mysql.format(sql, inserts);

3. Custom format

connection.config.queryFormat = function (query, values) {
  if (!values) return query;
  return query.replace(/\:(\w+)/g, function (txt, key) {
    if (values.hasOwnProperty(key)) {
      return this.escape(values[key]);
    }
    return txt;
  }.bind(this));
};

connection.query("UPDATE posts SET title = :title", { title: "Hello MySQL" });

参考： https://github.com/mysqljs/mysql#escaping-query-identifiers