SQL query throwing error, but linter says it's valid

JavaScript
,
#1

I have an SQL query that is racking my brain. Every SQL linter I tried says the query is valid, but my node app keeps throwing an error. Here’s the excerpt

db.connect();
db.query(`
  CREATE DATABASE IF NOT EXISTS ${json.team.id} 
    DEFAULT CHARACTER SET utf8mb4;
  USE ${json.team.id};
  CREATE TABLE IF NOT EXISTS \`tokens\`(
    \`id\` INT NOT NULL AUTO_INCREMENT,
    \`user\` VARCHAR(55) NOT NULL,
    \`token\` VARCHAR(250) NOT NULL,
    PRIMARY KEY (\`id\`)
  );`
);
db.end();

Here’s the 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 'USE TEA8BG48G;\n" +
    '      CREATE TABLE IF NOT EXISTS `tokens`(\n' +
    "        `id` INT NOT N' at line 2",
  sqlState: '42000',
  index: 0,
  sql: '\n' +
    '      CREATE DATABASE IF NOT EXISTS test \n' +
    '      DEFAULT CHARACTER SET utf8mb4;\n' +
    '      USE test;\n' +
    '      CREATE TABLE IF NOT EXISTS `tokens`(\n' +
    '        `id` INT NOT NULL AUTO_INCREMENT,\n' +
    '        `user` VARCHAR(55) NOT NULL,\n' +
    '        `token` VARCHAR(250) NOT NULL,\n' +
    '        PRIMARY KEY (`id`)\n' +
    '      );'
}

I ran that same query in a couple validators, and they both say it’s valid. I’ve tried everything, removing every line breaks, removing backticks, using single quotes and keeping backticks, but nothing seems to work. I don’t know what else to do anymore.

I’m using the Mysql node module.

Could a kind soul shed a light on this? :pray:

#2

Your query consists of multiple statements and that is disabled by default in the MySQL node module.

Splitting the query up in three queries should solve this. Enabling multiple statements as the docs outline would also work but is not recommend as has serious drawbacks for security.

So basically

db.query(`
  CREATE DATABASE IF NOT EXISTS ${json.team.id} 
    DEFAULT CHARACTER SET utf8mb4;
`);
db.query(`USE ${json.team.id};`);
db.query(`
  CREATE TABLE IF NOT EXISTS \`tokens\`(
    \`id\` INT NOT NULL AUTO_INCREMENT,
    \`user\` VARCHAR(55) NOT NULL,
    \`token\` VARCHAR(250) NOT NULL,
    PRIMARY KEY (\`id\`)
  );`
);
3 Likes
#3

You sir are a HERO! The answer was right under my nose. Thank you very very much!

1 Like