Using variables in a query

[melohagan]

Hello!

I am wondering if it's possible to use variables in the mysql2 query method?

I can successfully run this in MySQL Workbench:

set @custNum = 103;
SELECT * FROM customers WHERE customerNumber = @custNum;

but when run via client.query(...) I'm receiving the following error:

code: 'ER_PARSE_ERROR',
errno: 1064,
sql: 'set @custNum = 103; SELECT * FROM customers WHERE customerNumber = @custNum;',
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 'SELECT * FROM customers WHERE customerNumber = @custNum' at line 1"

Related discussion: Budibase/budibase#7775

[sidorares]

do you have multipleStatements connection flag enabled? See https://github.com/mysqljs/mysql#multiple-statement-queries
At a protocol level there is nothing special in variables, query with variable in it is sent using the same COM_QUERY command, its just part of a query text

[melohagan]

Thanks for the fast reply! Enabling that flag worked. I'll have to confer with the rest of the team to see if this will be an issue:

it allows for SQL injection attacks if values are not properly escaped

[sidorares]

Some unit tests are using variables in the query, example:

node-mysql2/test/integration/connection/test-multiple-results.js

Line 77 in d065991

'SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT; SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;',