0

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 ジャック
  • 3,682
  • 2
  • 20
  • 43
user3742929
  • 360
  • 3
  • 17
  • 1
    What's some example data? – Jack Bashford Jun 13 '19 at 08:02
  • What errors are you getting? There's not near enough detail here for anyone to help. – Jacques ジャック Jun 13 '19 at 08:03
  • Added more information to the question. Weirdly enough I'm getting no errors. – user3742929 Jun 13 '19 at 08:04
  • 1
    @Jacquesジャック The OP didn't mention errors, they just stated the code isn't functioning. – Jack Bashford Jun 13 '19 at 08:04
  • 2
    @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 ジャック Jun 13 '19 at 08:06
  • 1
    The example data point was requested in the first comment, and the OP has stated they are "getting no errors". – Jack Bashford Jun 13 '19 at 08:07
  • 1
    @JackBashford The OP added that they were getting no errors AFTER I asked what errors they were getting and AFTER you asked for example data. Look at the timestamps and stop trying to police valid questions. – Jacques ジャック Jun 13 '19 at 08:09
  • Of course I would have stated any errors in the question. So does anybody has an idea why this doesn't work? The escape comes from the mysql npm package. – user3742929 Jun 13 '19 at 08:10
  • 1
    @user3742929 Can you add more than a single line of code? Along with what the actual arrays look like. – Jacques ジャック Jun 13 '19 at 08:10
  • Does the generated query contain quotes (`'`) like you have it in the question, or backticks (`\``)? – TiiJ7 Jun 13 '19 at 08:11
  • It contains '', not backticks. I just copied the printed query. – user3742929 Jun 13 '19 at 08:11
  • What MySQL version? – FanoFN Jun 13 '19 at 08:15
  • Server version: 5.7.26-0ubuntu0.19.04.1 (Ubuntu) – user3742929 Jun 13 '19 at 08:16
  • 1
    I find it hard to believe you're not getting errors, mostly because if your column and table name are wrapped in quotes, it's not supported by mysql, and should give this 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 ''table_name'' at line 1` It looks like your issue is probably that the `escape` method returns the value wrapped with quotes instead of backticks. I also don't see anything in the docs about supporting escaping table/column names. – Jacques ジャック Jun 13 '19 at 08:19
  • 2
    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 ジャック Jun 13 '19 at 08:22
  • That worked, thank you. Didn't know you had to use another method for identifiers. – user3742929 Jun 13 '19 at 08:26

1 Answers1

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)

Jacques ジャック
  • 3,682
  • 2
  • 20
  • 43