You have an error in your SQL syntax when using ?,?

[leonk-sportsbet]

I've decided to use serverless-mysql as my "Threads_connected" count hits the limit of max_connections in a few seconds of running data ingestion.

Everything works fine for mysql2 package (except for the Threads_connected count) but with serverless-mysql package, the following code produces an error - 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 ''0','5000'' at line 2 error:

  const result = await db.query(
    `SELECT *
    FROM roles LIMIT ?,?`,
    [String(offset), String(config.listPerPage)]
  );

Interestingly enough, this works fine:

   const result = await db.query(
    `SELECT *
    FROM roles WHERE rolename=?`,
    [String(rolename)]
  );

My query function lives in a separate file and looks like this:

  // const mysql = require('mysql2/promise');
const mysql = require('serverless-mysql')();
const secretManager = require('./secrets-manager');
// const env = process.env;
const _ = require('lodash');
global.jsonString = {};

async function query(sql, params) {
  try {
    // Shaving ~100ms by not re-quering Secrets Manager
    if (_.get(jsonString, 'username', '') === '') {
      const secret = await secretManager.getDbCredentialFromSecretsManager(process.env.CREDENTIALS);
      jsonString = JSON.parse(secret);
    }

    // update cognito-custom-authorizer-rds-master stack to have DBName=usersdb instead of Users
    // then put back 'database : jsonString.dbname' below
    const config = {
      db: {
        host: jsonString.host,
        database: jsonString.dbname,
        user: jsonString.username,
        password: jsonString.password,
        multipleStatements: true
      }
    };

    mysql.config(config.db);
    // const results = await mysql.query(sql, params);
    let results = await mysql.query({
        sql: sql,
        values: params
    });

    await mysql.end();

    return results;
  } catch (err) {
    console.error('catch err: ' + err);
    const mysqlErrorList = Object.keys(HttpStatusCodes);
    // convert mysql errors which in the mysqlErrorList list to http status code
    err.status = mysqlErrorList.includes(err.code) ? HttpStatusCodes[err.code] : err.status;

    throw err;
  }
}

// like ENUM
const HttpStatusCodes = Object.freeze({
  ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: 422,
  ER_DUP_ENTRY: 409
});

module.exports = {
  query
};

Any idea why this won't work? Also tried without 'String()'.

  const result = await db.query(
    `SELECT *
    FROM roles LIMIT ?,?`,
    [String(offset), String(config.listPerPage)]
  );

[leonk-sportsbet]

Same error when not using backticks:

  const result = await db.query(
    'SELECT * FROM roles LIMIT ?,?',
    [offset, config.listPerPage]
  );

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 ''5000'' at line 1 | 2021-09-28T07:40:39.209Z 44a7448e-bfaf-49cf-b15e-1ae7ecda9644 INFO [Error] 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 ''5000'' at line 1

[trasherdk]

I would hardcode the query, to try figure out the real problem.

const result = await db.query("SELECT * FROM roles LIMIT 0, 5000")

and

const result = await db.query("SELECT * FROM roles LIMIT ?, ?", [0, 5000])

[leonk-sportsbet]

An answer to my own question: Unlike MySQL 8, while working with MySQL 5 you need to make sure numbers aren't converted to strings:

  const result = await db.query(
    `SELECT *
    FROM roles LIMIT ?,?`,
    [String(offset), String(config.listPerPage)]
  );

This works:

  const result = await db.query(
    `SELECT *
    FROM users LIMIT ?,?`,
    [Number(offset), Number(config.listPerPage)]
  );