Mysql query doesn't work anymore when escaping values

Question

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;

@Jacquesジャック The OP didn't mention errors, they just stated the code isn't functioning. — Jack Bashford
– Jack Bashford, Commented Jun 13, 2019 at 8:04
@JackBashford You are correct, but I'm sure with your 25k rep, you know as well as I do that questions with this sparse of details tend to leave out important information, like example data, errors. etc. — Jacques
– Jacques, Commented Jun 13, 2019 at 8:06
The example data point was requested in the first comment, and the OP has stated they are "getting no errors". — Jack Bashford
– Jack Bashford, Commented Jun 13, 2019 at 8:07
Ah, I found this in the docs: 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) So change escape to escapeId in your code for column/table names. — Jacques
– Jacques, Commented Jun 13, 2019 at 8:22

Jacques · Accepted Answer · 2019-06-13 08:26:31Z

1

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)

answered Jun 13, 2019 at 8:26

Jacques

3,7942 gold badges23 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql query doesn't work anymore when escaping values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related