Node.js SQL 操作

1.9.1 【必須】SQL 語句默認(rèn)使用預(yù)編譯并綁定變量

• 應(yīng)使用預(yù)編譯綁定變量的形式編寫 sql 語句，保持查詢語句和數(shù)據(jù)相分離

  // bad：拼接 SQL 語句查詢，存在安全風(fēng)險(xiǎn)
  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：使用預(yù)編譯綁定變量構(gòu)造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..
  });

• 對于表名、列名等無法進(jìn)行預(yù)編譯的場景，如：__user_input__ 拼接到比如 limit, order by, group by , from tablename語句中。請使用以下方法：

方案1：使用白名單校驗(yàn)表名/列名

  // 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__ 轉(zhuǎn)換為整數(shù)

  // good
  let { orderType } = req.body;
  // 強(qiáng)制轉(zhuǎn)換為整數(shù)
  orderType = parseInt(orderType, 10);
  const sql = `SELECT * from t_business_feeds order by ${orderType}`;
  connection.query(sql, (err, result) => {
    // handle err..
  });