Search code examples
javascriptmysqlnode.jsmysqljs

Mysql query doesn't work anymore when escaping values

I have query for adding columns to a database in my Node server:

const query = 'ALTER TABLE ' + mysql.escape(tableData[i]) + ' ADD ' + mysql.escape(attributeData[0]) + ' FLOAT DEFAULT NULL;'

If I use they query without mysql.escape() it adds the columns as it is supposed to. However when using the escape function for preventing sql injections it no longer works. Why is that?

Here is an example query with the escape function, it returns no error but also doesn't add the column to the table:

ALTER TABLE '1$0_Test_2018' ADD 'Eigenschaft_3' FLOAT DEFAULT NULL;

This query works just fine, however I want to make sure to escape user data:

ALTER TABLE 1$0_Test_2018 ADD Eigenschaft_3 FLOAT DEFAULT NULL;
Solution

  • It looks like your issue is probably that the escape method returns the value wrapped with quotes instead of backticks.

    In the github docs it looks like you just need to change escape to escapeId.

    If you can't trust an SQL identifier (database / table / column name) because it is provided by a user, you should escape it with mysql.escapeId(identifier)