Node.js SQL 操作

1.9.1 【必须】SQL 语句默认使用预编译并绑定变量

• 应使用预编译绑定变量的形式编写 sql 语句，保持查询语句和数据相分离

  // bad：拼接 SQL 语句查询，存在安全风险
  const mysql  = require("mysql");
  const connection = mysql.createConnection(options);
  connection.connect();

  
  const sql = util.format("SELECT * from some_table WHERE Id = %s and Name = %s", req.body.id, req.body.name);
  connection.query(sql, (err, result) => {
    // handle err..
  });  

  
  // good：使用预编译绑定变量构造SQL语句
  const mysql  = require("mysql");
  const connection = mysql.createConnection(options);
  connection.connect();

  
  const sql = "SELECT * from some_table WHERE Id = ? and Name = ?";
  const sqlParams = [req.body.id, req.body.name];
  connection.query(sql, sqlParams, (err, result) => {
    // handle err..
  });

• 对于表名、列名等无法进行预编译的场景，如：__user_input__ 拼接到比如 limit, order by, group by , from tablename语句中。请使用以下方法：

方案1：使用白名单校验表名/列名

  // good
  const tableSuffix = req.body.type;
  if (["expected1", "expected2"].indexOf(tableSuffix) < 0) {
    // 不在表名白名单中，拒绝请求
    return ;
  }
  const sql = `SELECT * from t_business_${tableSuffix}`;
  connection.query(sql, (err, result) => {
    // handle err..
  });

方案2：使用反引号包裹表名/列名，并过滤 __user_input__ 中的反引号

  // good
  let { orderType } = req.body;
  // 过滤掉__user_input__中的反引号
  orderType = orderType.replace("`", "");
  const sql = util.format("SELECT * from t_business_feeds order by `%s`", orderType);
  connection.query(sql, (err, result) => {
    // handle err..
  });

方案3：将 __user_input__ 转换为整数

  // good
  let { orderType } = req.body;
  // 强制转换为整数
  orderType = parseInt(orderType, 10);
  const sql = `SELECT * from t_business_feeds order by ${orderType}`;
  connection.query(sql, (err, result) => {
    // handle err..
  });