Remove slashes in node js mysql query

Question

This is my code.

pool.getConnection(function (err, connection) {
        connection.query("delete from userFiles where type = 1 and 
                          typeId = " + taskId + " and fileName 
                          NOT IN ('?') ",[oldFileNames], function (err, rows) {

        });
    });

Now the problem is , node js is generating query like this,

delete from table_name where type = 1 and typeId = 1 and 
fileName NOT IN (\'\'upload_149f2b78e5fd4096781bb5b8719b874f.png,upload_e8185e896cb0f8bb0b66d807cc60922c.png\'\')

I even tried like this,

connection.query("delete from userFiles where type = 1 and typeId = " + taskId + " and
                    fileName NOT IN ('" + oldFileNames + "') ", 
                      function (err, rows) {

This generated query like this,

delete from userFiles where type = 1 and typeId = 1 and 
fileName NOT IN (\'upload_149f2b78e5fd4096781bb5b8719b874f.png,
upload_e8185e896cb0f8bb0b66d807cc60922c.png\')

Both are causing mysql syntax error for the query(slashes are getting appended).

Can you suggest, what i can do to get rid of this error.

What's oldFileNames ? An array or a string ?

Denys Séguret
– Denys Séguret

2015-11-06 08:45:03 +00:00
Commented Nov 6, 2015 at 8:45 — Denys Séguret
– Denys Séguret, Commented Nov 6, 2015 at 8:45
@DenysSéguret its a comma separated value from form.

Niranjan N Raju
– Niranjan N Raju

2015-11-06 08:46:14 +00:00
Commented Nov 6, 2015 at 8:46 — Niranjan N Raju
– Niranjan N Raju, Commented Nov 6, 2015 at 8:46
OK, It should be an array. See updated answer.

Denys Séguret
– Denys Séguret

2015-11-06 08:48:48 +00:00
Commented Nov 6, 2015 at 8:48 — Denys Séguret
– Denys Séguret, Commented Nov 6, 2015 at 8:48

Denys Séguret · Accepted Answer · 2015-11-06 09:18:59Z

3

You're not supposed to add the quotes yourself around the ? placeholder. Remove them.

You should also pass an array, not a string. Assuming it's a clean string, you can just use split.

connection.query(
      "delete from userFiles where type = 1 and typeId = " + taskId +
      " and fileName NOT IN (?) ", [oldFileNames.split(/,\s*/)],
      function (err, rows) {

edited Nov 6, 2015 at 9:18

answered Nov 6, 2015 at 8:27

Denys Séguret

384k90 gold badges813 silver badges780 bronze badges

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

6 Comments

Niranjan N Raju Over a year ago

I got like this,

delete from userFiles where type = 1 and typeId = 1 and fileName NOT IN (\'upload_d46d172a7aaabf50f7598a334cc2ce37.png,upload_b008eaa4f8731bfab7d3aec3614cf15e.png\')

Denys Séguret Over a year ago

@NiranjanNRaju It looks like your oldFileNames string contains quotes, and that's a problem. Can you look at that ?

Niranjan N Raju Over a year ago

Nope... im passing only comma separated names.

Niranjan N Raju Over a year ago

And above query is deleting all rows.

Denys Séguret Over a year ago

What's oldFileNames ? An array or a string ? An array is needed, not a string.

|

piscator · Accepted Answer · 2015-11-06 08:24:22Z

1

A possible solution is to use connection.escape();

connection.query("delete from userFiles where type = 1 and typeId = " + taskId + " 
and fileName NOT IN (" + connection.escape(oldFileNames) + ")");

answered Nov 6, 2015 at 8:24

piscator

8,8495 gold badges26 silver badges35 bronze badges

2 Comments

Niranjan N Raju Over a year ago

none of the rows r getting deleted even if there r some rows.

piscator Over a year ago

this only works when oldFileNames is an array of strings.

Collectives™ on Stack Overflow

Remove slashes in node js mysql query

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related