Do "$1" parameters in an ilike expression need to be escaped when used with the postgres crate's query function?

Question

I'm using the postgres crate which makes a query with postgres::Connection. I query a table based on a string value in an ilike '%search_string%' expression:

extern crate postgres;
use std::error::Error;

//DB Create queries
/*
CREATE TABLE TestTable (
    Id  SERIAL primary key,
    _Text varchar(50000) NOT NULL
);

insert into TestTable (_Text) values ('test1');
insert into TestTable (_Text) values ('test1');
*/

fn main() -> Result<(), Box> {
    let conn = postgres::Connection::connect(
        "postgres://postgres:postgres@localhost:5432/notes_server",
        postgres::TlsMode::None,
    )?;

    let text = "test";

    // //Does not work
    // let query = &conn.query(
    //     "
    //         select * from TestTable where _text ilike '%$1%'
    //         ",
    //     &[&text],
    // )?;

    //Works fine
    let query = &conn.query(
        "
            select * from TestTable where Id = $1
            ",
        &[&1],
    )?;

    println!("Rows returned: {}", query.iter().count());

    Ok(())
}

If I uncomment the //Does not work part of the code, I will get the following error:

thread 'main' panicked at 'expected 0 parameters but got 1'

It appears it doesn't recognize the $1 parameter that is contained in the ilike expression. I've tried escaping the single quotes and that doesn't change it.

The only dependencies are:

postgres = { version = "0.15.2", features = ["with-chrono"] }

user_1523613 · Answer

To my surprise, here was the fix:

let text = "%test%";

let query = &conn.query(
    "
    select * from TestTable where _text like $1
    ",&[&text],
)?;

Apparently the postgres function knows to add single quotes around strings in this scenario.

I found out about this from here: https://www.reddit.com/r/rust/comments/8ltad7/horrible_quote_escaping_conundrum_any_ideas_on/

user_18230255 · Answer

An example should do the magic.

At the top, I am using pg. So, import it. My code looks like

const title = req.params.title;

const posts = await db.query("SELECT * FROM postsTable INNER JOIN usersTable ON postsTable.author = usersTable.username WHERE title ILIKE $1 ORDER BY postsTable.created_on DESC LIMIT 5;", [`%${title}℅`])

Do "$1" parameters in an ilike expression need to be escaped when used with the postgres crate's query function?

2 Answers