Using of mysql.format(…) / con.format(…) for dynamic queries to avoid SQL syntax error

[pubmikeb]

mysql2: 2.1.0

I paid attention, if I execute a dynamic query:

let [rows] = await pool.query(QUERIES.myQuery, [selectArg, fromArg, whereArgField, whereArgVal]);

where the table name (from) is passed as a parameter, then I get an SQL syntax error, but when I wrap the same query with mysql.format(…)/con,format(…):

let [rows] = await pool.query(mysql.format(QUERIES.myQuery, [selectArg, fromArg, whereArgField, whereArgVal]).replace(/'/ug, ""));

then everything is working great. At the same time, the dynamic queries, where the table name (from) is hard-coded into SQL-query don't require applying a query formatting.

I'm a bit confused why and when exactly should I use mysql.format(…)/con.format(…)?

[pubmikeb]

I assume, the problem is with ' surrounding the table name in a dynamic query, but it looks like such kind of issue is too standard and could be «fixed» under the hood by default.

[sidorares]

both conn.format() and mysql.fromat() call SqlString.format

Can you add some logging and compare what's passed to sqlstring?

https://github.com/sidorares/node-mysql2/blob/7ee4a7bbac0ca330728e371a53879fd3df78855f/lib/connection.js#L460

[pubmikeb]

@sidorares, I've reproduced the issue with and without mysql.fromat().

A query execution with mysql.fromat()

SQL:

# noinspection SqlResolve, SqlMissingColumnAliases
    SELECT
        documentID
    FROM
        tbltrainingdocuments
    WHERE
          isDeleted = 0
      AND documentID = 66;

Values:

Array(0) []

A query execution without mysql.fromat()

SQL:

# noinspection SqlResolve, SqlMissingColumnAliases
    SELECT
        ?
    FROM
        ?
    WHERE
          isDeleted = 0
      AND ? = ?;

Values:

Array(4) [documentID,
tbltrainingdocuments,
documentID,
66]

And then the error:

{
  "message": "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 ''tbltrainingdocuments'\n\tWHERE\n\t\t  isDeleted = 0\n\t  AND 'documentID' = '66'' at line 5",
  "code": "ER_PARSE_ERROR",
  "errno": 1064,
  "sqlState": "42000",
  "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 ''tbltrainingdocuments'\n\tWHERE\n\t\t  isDeleted = 0\n\t  AND 'documentID' = '66'' at line 5"
}

It looks like mysql.fromat() performs a parsing of the dynamic query and ensure that MySQL receives the query in a proper way. But if it's the case, why not to built-in mysql.fromat() into pool.query() by default? Why should I call mysql.fromat() manually?