mysql - 将 NodeJS 变量输入到 SQL 查询中

Question

我试图让一个 mysql 语句不给我一个错误。

陈述：

app.get("/Getcompany", function(request, response) {
      let cname = request.query.cname
      var query = "select * from clientdata_nsw where companyname  = '" + connection.escape(cname) + "'"
      connection.query(query, function(err, rows) {
        if (err) {
          console.log(err);
          return;
        }
        rows.forEach(function(result) {
          console.log(result.companyname, result.service, result.phone, result.open_times, result.rating_facebook, result.rating_goggle)
        })

      });

错误信息：

Error: 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 'ADBY IT​''' at line 1
    at Query.Sequence._packetToError (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\mysql\lib\protocol\sequences\Sequence.js:51:14)
    at Query.ErrorPacket (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\mysql\lib\protocol\sequences\Query.js:83:18)
    at Protocol._parsePacket (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\mysql\lib\protocol\Protocol.js:280:23)
    at Parser.write (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\mysql\lib\protocol\Parser.js:74:12)
    at Protocol.write (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\mysql\lib\protocol\Protocol.js:39:16)
    at Socket.<anonymous> (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\mysql\lib\Connection.js:109:28)
    at Socket.emit (events.js:189:13)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
    --------------------
    at Protocol._enqueue (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\mysql\lib\protocol\Protocol.js:141:48)
    at Connection.query (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\mysql\lib\Connection.js:214:25)
    at C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\db.js:34:13
    at Layer.handle [as handle_request] (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\express\lib\router\layer.js:95:5)
    at next (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\express\lib\router\route.js:137:13)
    at Route.dispatch (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\express\lib\router\route.js:112:3)
    at Layer.handle [as handle_request] (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\express\lib\router\layer.js:95:5)
    at C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\express\lib\router\index.js:281:22
    at Function.process_params (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\express\lib\router\index.js:335:12)
    at next (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\express\lib\router\index.js:275:10)
    at SendStream.error (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\serve-static\index.js:121:7)
    at SendStream.emit (events.js:189:13)
    at SendStream.error (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\send\index.js:270:17)
    at SendStream.onStatError (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\send\index.js:421:12)
    at next (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\send\index.js:736:16)
    at onstat (C:\Users\Adam.Wolarczuk\Desktop\Projects\nodetest\node_modules\send\index.js:725:14)
    at FSReqWrap.oncomplete (fs.js:153:21)
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  index: 0 }

标签： mysqlnode.js

Answer 1

使用和附加您自己的单引号是没有意义connection.escape()的，因为它们最终都会做同样的事情，并且一起使用意味着转义要进行两次。

如果您浏览下面的代码，您会注意到我使用了模板文字，从而使您免于通过附加较小的部分来形成主字符串的麻烦。也会connection.escape()照顾逃逸的部分。Ryan 提到的票是我也想指出的，因为围绕connection.escape()进行了讨论

app.get("/Getcompany", function(request, response) {
  const cname = request.query.cname, query = `select * from clientdata_nsw where companyname  = ${connection.escape(
    cname
  )}`;
  connection.query(query, function(err, rows) {
    if (err) {
      console.log(err);
      return;
    }

    rows.forEach(function(result) {
      console.log(
        result.companyname,
        result.service,
        result.phone,
        result.open_times,
        result.rating_facebook,
        result.rating_goggle
      );
    });
  });
});