Parameterized query with a "WHERE [string] like" clause

[saghm]

I want to use the parameterized query feature to search a database for users whose emails contain a given search key. For example, if someone searches the string 'smith', I want to search the database with the query "select id, firstname, lastname from users where email like '%$1%' " with the value array ['smith']. For some reason, when my query is parsed, it doesn't recognize the parameter placeholder in the query.

Example:

var pg = require('pg').native;
var connect = 'my connection string';
var cl = new pg.Client(connect);

cl.connect(function(err) {
    if (err) {
        console.error("no connection: " + err);
        return cl.end();
    } else {
        return cl.query(
            "select id, firstname, lastname from users where email like '%$1%'",
            ['smith'],
            function(err, res) {
                if (err) {
                    console.error(err);
                    return cl.end();
                } else {
                    return console.log(res.rows);
                }
        });
    }
});

Running this (with the correct connection string) prints out the following error:

{ [Error: bind message supplies 1 parameters, but prepared statement "" requires 0]
severity: 'ERROR',
code: '08P01',
file: 'postgres.c',
line: '1500',
routine: 'exec_bind_message' }

However, if I change the query to check for "email like '%smith%' " and remove the array argument, it queries the database without any error and prints out the results.

Am I doing something dumb here that's causing this error? I don't have a lot of experience with node, so I wouldn't put it past me.

If not, I'm guessing this has to do with the way the query is parsed for parameter placeholders (i.e. that it doesn't check for them in the "like" expression). Is there by any chance a way around this? I'd prefer not to have to use a non-parameterized query and just stick the string in the middle, since then I'd have to protect against SQL injection myself (although I'm certainly open to it if there's no other option).

[benighted]

The problem is that pg is reading the $1 as part of the literal string instead of a placeholder because it is wrapped in quotes. It will work if you call it like this without the quotes and with the wildcards in the values array element:

        return cl.query(
            "select id, firstname, lastname from users where email like $1",
            ['%smith%'],
            function(err, res) {
            ...

[saghm]

Thanks a bunch for the quick and helpful response!

[benighted]

You're welcome 😄

[SamuelKoroh]

You can make it more robust by using dynamic value from a variable using JS string interpolation
return cl.query(
"select id, firstname, lastname from users where email like $1",
[${search}%],
function(err, res) {
...