how to make a like search in postgresql and node js

Question

I am using node js and the module pg for connect to postrgresql i want to make a search for a database of tags but i can't make it work, in the variable tag i saved the param of the url , but the consult doesn't return any results. how can i fix?

app.get("/tags", function(req, res){
        var tag = req.query.q;
        res.json(tag)
        var search = db.client.query("SELECT * FROM tags WHERE name LIKE '%$1%'", [tag], function(err, result){
                res.json(result);
            }
        );
    });

mu is too short · Answer 1 · 2014-03-27T17:00:48.857

51

I don't know the node.js PostgreSQL interface that well but I think I can see the problem. This is an SQL string literal that contains a numbered placeholder:

'%$1%'

The $1 inside that string won't be replaced with the value of tag because placeholders inside strings are not placeholders at all, they're just substrings that happen to have the same form as a placeholder.

The two usual options are:

Add the % wildcards in the client code.
Concatenate the % wildcards onto the strings inside the database.

The first one would look like this:

db.client.query("SELECT * FROM tags WHERE name LIKE $1", ['%' + tag + '%'], ...

and the second like this:

db.client.query("SELECT * FROM tags WHERE name LIKE '%' || $1 || '%'", [tag], ...

Use whichever approach you prefer.

edited Mar 27 '14 at 17:00

answered Oct 19 '13 at 23:03

mu is too short

426,620
70
833
800

I guess the first version allows less sql injections than the second. The second one seems more vulnerable. – Heribert Jan 15 '18 at 09:15
5

@Heribert Not really, `$1` will be properly escaped for SQL before the SQL string concatenation happens so it shouldn't make any difference. – mu is too short Jan 15 '18 at 16:53
You can use first one with ```[`%${tag}%`]``` must be ` not '. And ILIKE instead of LIKE to avoid case sensitive – ÄR Âmmãř Żąîñh Feb 16 '23 at 09:38

score 17 · Answer 2 · edited Jun 06 '18 at 16:46

17

an ES6 solution would be something like:

client.query('select * from users where user_name Ilike $1', [`%${req.query.q}%`], (err1, result) => { ...

edited Jun 06 '18 at 16:46

mu is too short

426,620
70
833
800

answered Jan 09 '18 at 14:53

Nir O.

1,563
1
17
26

This was down voted and its not clear why, does anyone see a security concern here? – James Jan 26 '18 at 20:40
@James I don't see any downvotes on this answer so maybe they changed their mind, perhaps someone was confusing the `$1` placeholder in single quotes with string interpolation and then realized their mistake. Anyway, looks like an ES6 adaptation of my string concatenation (but with the case-insensitive ILIKE instead of LIKE), I don't see anything wrong with it. – mu is too short Jun 06 '18 at 16:46
What's slightly "wrong" perhaps is that the % syntax is moved from the query pattern to the parameter. The second option in @mu-is-too-short's answer seems a better choice. – Nicolas Le Thierry d'Ennequin Mar 05 '21 at 15:19

how to make a like search in postgresql and node js

2 Answers2

Linked

Related