How do I prevent SQL injection when using the LIKE operator?

Question

I am building a website using Node and the node-mysql package.

app.get('/api/tags', function(req, res) {
  var term = req.query.term;
  var query =
  'SELECT \
     t.tagName \
   FROM tags t \
   JOIN screencastTags st \
     ON st.tagName = t.tagName \
   JOIN screencasts s \
     ON s.screencastId = st.screencastId \
   WHERE s.status = \'approved\' AND t.tagName LIKE \'%' + term + '%\' \
   GROUP BY t.tagName \
   LIMIT 5';
  connection.queryAsync(query).spread(function(tags) {
    tags = tags.map(function(tag) { return tag.tagName });
    res.send(tags);
  })
})

Here I use a value from the query string - term - to return a list of tags.

My question is: How do I prevent against SQL injection when I am using the LIKE operator?

I tried

  var query =
  'SELECT \
     t.tagName \
   FROM tags t \
   JOIN screencastTags st \
     ON st.tagName = t.tagName \
   JOIN screencasts s \
     ON s.screencastId = st.screencastId \
   WHERE s.status = \'approved\' AND t.tagName LIKE \'%' + conneciton.escape(term) + '%\' \
   GROUP BY t.tagName \
   LIMIT 5';

But that produces invalid SQL.

Jerome WAGNER · Accepted Answer · 2015-06-02 10:02:39Z

1

Try to never build an sql request by concatenation. It indeed always increases the risk of the SQL injection footprint.

A placeholder should work even with the LIKE condition.

var sql = '... LIKE ? GROUP BY ...';
connection.query(sql, ['%' + term + '%'], function(err, res) {})

A prepared statement would even be better for security concerns. you should read https://github.com/felixge/node-mysql/#escaping-query-values

answered Jun 2, 2015 at 10:02

Jerome WAGNER

22.6k10 gold badges64 silver badges79 bronze badges

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

1 Comment

Angular noob Over a year ago

Thank you - I will test this now. I use ? everywhere else in my application but I thought it would be simpler to inspect in the current form. Also, ? is not equivalent to a prepared statement.

Collectives™ on Stack Overflow

How do I prevent SQL injection when using the LIKE operator?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related