CODEWITHSUNDEEP
javascriptmysqlsqlnode.js
user2360509
user2360509

Reputation: 13

LIKE clause problems

I'm working on a small mobile web app. I have a query that has a LIKE clause. For example:

SELECT from posts WHERE (title LIKE '%car%') or (content LIKE '%car%');

The problem I'm having is variables in javascript have single quotes around their values. So I get the following:

SELECT from posts WHERE (title LIKE '%'car'%') or (content LIKE '%'car'%');

Actual string I'm building this way:

"SELECT from posts WHERE (title LIKE '%" + client.escape(input) + "%') or (content LIKE '%" + client.escape(input) + "%');"

Upvotes: 1

Views: 95

Answers (1)

Fabrício Matté
Fabrício Matté

Reputation: 70139

The enclosing 's are added by the escape function automatically, so move the %s to inside the variable and escape it, and remove the 's from your query:

input = client.escape('%' + input + '%'); //=== "'%escaped_input%'"
client.query("SELECT * FROM posts WHERE (title LIKE " + input + ") or (content LIKE " + input + ")", function(err, results) {
     // ...
});

Reference - Escaping query values

Alternatively, you should be able to use the emulated prepared statements syntax as well:

input = '%' + input + '%';
client.query("SELECT * FROM posts WHERE (title LIKE ?) or (content LIKE ?)", [input, input], function(err, results) {
     // ...
});

This will perform something similar to sprintf, replacing the ? placeholders by the properly escaped items (internally using the same escape method as above) from second argument array, in the order they were passed - the placeholders' order corresponds to the array items' order.

Side-note: I assume you've oversimplified the query removing all the fields from SELECT statement, remember to select at least a field or all of them (*) otherwise it is not valid SQL syntax.

Upvotes: 1

Related Questions