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

CodePudding user response：

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)

CodePudding user response：

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