input a NodeJS variable into an SQL query

Question

I am trying to get a mysql statement to not give me an error.

Statement:

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 message:

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 }

ABC · Answer 1 · 2019-03-22T00:31:03.533

Formatting errors, you needed to fix first

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)
        })

    });

});

If you look at your error message, you are escaping the variable incorrectly. Error messages are very helpful, if you notice the trailing 'ADBY IT'''

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'''

Instead of using escape you can use ? characters as placeholders for values you would like to have escaped like this:

Arrays are turned into list, e.g. ['a', 'b'] turns into 'a', 'b'
Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')

Example:

let cname = request.query.cname;
let sql = mysql.format("SELECT * FROM clientdata_nsw WHERE companyname=?", [cname]);
connection.query(sql, function(err, rows) {
    if (err) {
       console.log(err);
          return;
       }
});

Multiple placeholders are mapped to values in the same order as passed. For example, in the following query foo equals a, bar equals b, baz equals c, and id will be userId:

connection.query('UPDATE users SET foo = ?, bar = ?, baz = ? WHERE id = ?', ['a', 'b', 'c', userId], function (error, results, fields) {
  if (error) throw error;
  // ...
});

Cited & Useful: https://github.com/mysqljs/mysql

score 1 · Accepted Answer · answered Mar 22 '19 at 02:50

There is no point using connection.escape() and appending your own single quotes because both of them end up doing the same thing and using together means escaping is done twice.

If you go through my code below you can notice that I have made use of template literals saving you from hassle of forming the main string by appending smaller parts. Also connection.escape() would take care of escaping part. The ticket mentioned by Ryan is something that I would also like to point you to, because there was a discussion surrounding 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
      );
    });
  });
});

Thanks for the comment mate i tried it and getting an error (node:15104) [DEP0096] DeprecationWarning: timers.unenroll() is deprecated. Please use clearTimeout instead. { Error: ER_CANT_AGGREGATE_2COLLATIONS: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' — Adam Wolarczuk, Mar 22 '19 at 05:49
@AdamWolarczuk Glad to help, but this error is something entirely different. See if this [SO](https://stackoverflow.com/a/1008336/3672474) helps — PrivateOmega, Mar 22 '19 at 06:10
all good my silly fault i had somes issues with my database i fixes it and all worked, now i need to work on my json — Adam Wolarczuk, Mar 22 '19 at 09:34

score -1 · Answer 3 · answered Mar 22 '19 at 00:15

You shouldn't supply your own quotes when using connection.escape. This is how you can write your query:

var query = "select * from clientdata_nsw where companyname = " + connection.escape(cname)

You can also use placeholders like so:

connection.query('select * from clientdata_nsw where companyname = ?', [cname], 
    function(err, rows) {
      // ...
    }
);

This page shows how to use connection.escape: https://www.w3resource.com/node.js/nodejs-mysql.php

By the way, you are not the first one to have this confusion. Someone opened a ticket around this behavior:

https://github.com/mysqljs/mysql/issues/594

They explain that since numerical values don't need quotes, the responsibility of adding quotes should fall upon the escape function.

input a NodeJS variable into an SQL query

3 Answers3