How to remove quotes in prepared statements for LIKE operator?

Question

I am using MySQL alongside an Express Server.

    var qstr = "SELECT * FROM fruits WHERE fruitname LIKE '%?%'";

        var q = con.query(qstr,req.query.sq, function(err, result) {

            if (err) {
                res.json({ "status": "error", "code": "DB_ERROR", "time": t });
                console.log(err + '@' + t);
            } else
                res.json(result);
        });

Now when I run the query, The actual query is shown as

SELECT * FROM fruits WHERE fruitname LIKE '%'banana'%'

How do I remove the quotes from the query string ?

score 1 · Accepted Answer · answered Jul 21 '17 at 19:43

1

You must use placeholders in isolation, they won't work if embedded inside of quotes:

con.query(
  "SELECT * FROM fruits WHERE fruitname LIKE ?",
 '%' + req.query.sq + '%',
 function() { ...

});

This constructs a string like %banana% for binding to the ? placeholder which in effect converts it to '%banana%'

answered Jul 21 '17 at 19:43

tadman

208,517
23
234
262

Binding values to placeholders work as prepared statements. Here you are introducing code vulnerable to SQL injection. – skiilaa Jul 21 '17 at 20:38
@skiilaa I'm not sure what you're saying here. If this driver supports placeholders, which the default `mysql` one does, then this should be fine. – tadman Jul 22 '17 at 23:03
You should use `mysql.escape()` to avoid SQL injections. Example value that `sq` could be: `test%; SELECT 1+1; --` (this doesn't do much, but it's possible to execute SQL commands) – skiilaa Aug 30 '17 at 07:42
@skiilaa If you're using prepared statements you don't need to escape, and if you do you'll end up double-escaping your values which mangles them. I'm one of the most vocal here about preventing SQL injection bugs, so I'm well aware of what's going on in this code. – tadman Aug 30 '17 at 16:57
@skiilaa No worries. We all have days like that. – tadman Aug 30 '17 at 17:14
Or a whole month :) – skiilaa Aug 30 '17 at 17:18

score 1 · Answer 2 · answered Jul 21 '17 at 19:43

1

Node MySQL escape LIKE statement

Already answered ^

Can just move it to the second parameter.

con.query('SELECT * FROM fruits WHERE fruitname LIKE ?', `%${req.query.sq}%`, callback)

answered Jul 21 '17 at 19:43

WakeskaterX

1,408
9
21

How to remove quotes in prepared statements for LIKE operator?

2 Answers2