The callback error query is not the same one i send, which lead to a syntax error

Question

I have this for loop that loops through a punch of user inputs and I wanna add them to mysql but I have this error that keeps popping up saying a syntax error in my query I logged the query I send and it fine the callback error query is not the same as the one i sent

here's the loop

 for (var j = 0; j <= intLength - 1; j++) {
                    console.log(intItem[j], "intents looop");
                    const query1 =
                      " INSERT INTO intents (intent, version_id,status_intent) VALUES ('" +
                      intItem[j] +
                      "', (SELECT MAX (versions.version_id) from versions), '" +
                      enableStatus +
                      "')";
                    console.log(query1, "query11");
s
                        connection.query(
                          query1,
                          params,
                          function (err, results) {
                            if (err) {
                              console.log(
                                err,
                                "error from new project in insert to intents"
                              );
                            }
                          }
                        );
                      }

and that is the callback error query sql: " INSERT INTO intents (intent, version_id,status_intent) VALUES ('what'test11'', (SELECT MAX (versions.version_id) from versions), 'enable')"

This is the exact error...

  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage: "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 'test103'', (SELECT MAX 
  (versions.version_id) from versions), 'enable')' at line 1",
  sqlState: '42000',
  index: 0,
  sql: " INSERT INTO intents (intent, version_id,status_intent) 
  VALUES ('what'test103'', (SELECT MAX (versions.version_id) from 
  versions), 'enable')"

This is the query before making the connection to the DB...

INSERT INTO intents (intent, version_id,status_intent) VALUES 
('what?', (SELECT MAX (versions.version_id) from versions), 
'enable')

Can you store `(SELECT MAX (versions.version_id) from versions)` in separate let or const and try with that variable and see if that resolves your issue. — Shariati, Dec 05 '21 at 13:21
@Shariati it returns an id, not a string or the version name — Sameer, Dec 05 '21 at 13:47
That does not matter, try putting result in separate variable and try. — Shariati, Dec 05 '21 at 14:05

score 1 · Accepted Answer · answered Dec 05 '21 at 15:25

Most developers find it easier to use the query params instead of struggling with how to escape literal quote characters. If you use query params, then you don't need to escape anything, just use the ? placeholder in place of scalar values, and then add the inputs to your params array.

const query1 = `
  INSERT INTO intents (intent, version_id, status_intent) 
  VALUES (?, (SELECT MAX (versions.version_id) from versions), ?)`;

params = [intItem[j], enableStatus];

connection.query(query1, params,
    function (err, results) {
      if (err) {
        console.log(err, "error from new project in insert to intents");
      }
    });

(Also use the backtick-delimited template literal, so you can write your SQL as a multiline string without needing to use + to concatenate fragments together.)

Silvan Bregy · Answer 2 · 2021-12-05T15:17:13.737

Your problem is that intItem[j] actually has a single quote (') in it so the query will have a wrong syntax.

Your produced query:

  INSERT INTO intents (intent, version_id,status_intent) VALUES ('what'test11', (SELECT MAX (versions.version_id) from versions), 'enable')

the first value after Values has a single quote in it, so the parser thinks the value already ended after what .

To fix it, you need to escape the single quote (') within given strings with another single quote. Result would be:

  INSERT INTO intents (intent, version_id,status_intent) VALUES ('what''test11', (SELECT MAX (versions.version_id) from versions), 'enable')

You could fix the code like this:


var escapeSqlValue = function(value) {
    if(typeof value === "string") {
        // replace all single quotes with another single quote before it!.
        // Regex with the "g" flag is used, so it will replace all occurences!.
        return value.replace(/'/g, "''")
    }

    // no string, so keep it like it is.
    return value
}

for (var j = 0; j <= intLength - 1; j++) {
    console.log(intItem[j], "intents looop");
    const query1 =
        " INSERT INTO intents (intent, version_id,status_intent) VALUES ('" +
        escapeSqlValue(intItem[j]) +
        "', (SELECT MAX (versions.version_id) from versions), '" +
        escapeSqlValue(enableStatus) +
        "')";
    console.log(query1, "query11");
    
    connection.query(
        query1,
        params,
        function (err, results) {
            if (err) {
                console.log(
                    err,
                    "error from new project in insert to intents"
                );
            }
        }
    );
}

Which now produces valid sql:

  INSERT INTO intents (intent, version_id,status_intent) VALUES ('what''test11', (SELECT MAX (versions.version_id) from versions), 'enable')

Furthermore, I recommend to create a general function which builds up your sql. So it's more readable and you can add more escape or maybe other logic to it more easily.

Example:


// little enhanced function for regonizing subquerys if you wrap em in 
// parenthesis
function escapeSqlValue(value) {    

    if (typeof value === "string") {

        // For regonizing subquerys, check if it starts with a parenthesis!
        if(value.startsWith("(")) {
            return value
        }

        // replace all single quotes with another single quote before it!.
        // Regex with the "g" flag is used, so it will replace all occurences!.
        value =  value.replace(/'/g, "''")

        // directly wrap it into commata now!
        return `'${value}'`
    }

    // no string, so keep it like it is.
    return value
}

function sqlInsertQuery(table, rawFields, rawValues) {
   
    // join the fields comma separated.
    const fields = rawFields.join(",")

    // escape the actual values and join them with comma
    const values = rawValues.map(v => escapeSqlValue(v)).join(",")

    // build up the query:
    // note the `` quotes. They allow to use variables in them directly when
    // they are wrapped within ${/*var goes here*/}  , they allow to build up 
    // strings in a more readable way.
    return `INSERT INTO ${table} (${fields}) VALUES (${values})`

}

And you could call it like

const fields = ["intent", "version_id", "status_intent"]
const values = ["what'test11", "(SELECT MAX (versions.version_id) from versions)", "enable"]
const query = sqlInsertQuery("intents", fields, values)

console.log("query", query)

Thank you for that explanation, but I tried the solution and it didn't work, because the problem is not with the input intent I logged the query before making the connection to the db, and here it is... `INSERT INTO intents (intent, version_id,status_intent) VALUES ('what?', (SELECT MAX (versions.version_id) from versions), 'enable')` and this is the query that the error callback returns... `sql: " INSERT INTO intents (intent, version_id,status_intent) VALUES ('what'test103'', (SELECT MAX (versions.version_id) from versions), 'enable')` they are not the same as you can see — Sameer, Dec 06 '21 at 08:21
@Sameer4Real Please edit the question for showing the exact error.. Which is logged. ANd refer to the other answer, it seams better.. ^^ — Silvan Bregy, Dec 06 '21 at 08:27
Please try removing the `?` in your test string. Maybe that has an impact?. — Silvan Bregy, Dec 06 '21 at 08:38
Thank you, I referred to the other answer and it solved the problem. Again, thank you for your time. — Sameer, Dec 06 '21 at 09:50

The callback error query is not the same one i send, which lead to a syntax error

2 Answers2