Using ilike in rust sqlx with push bind

Question

I am trying to dynamically create a query with a where clause and now i have the problem that I want to use push_bind to add a value because it's user input.

But i have the following Problem. If I build my query with push_bind I don't get any values:

if let Some(test) = &param.test{
    query_builder.push(" AND \"Test\" ilike '%");
    query_builder.push_bind(test);
    query_builder.push("%'");
}

On the other side if I build my query with only push I get the expected values:

if let Some(test) = &param.test{
    query_builder.push(" AND \"Test\" ilike '%");
    query_builder.push(test);
    query_builder.push("%'");
}

Can someone explain to me what push_bind is doing internally or how i would normaly do this with sqlx?

Answer 1

push just pushes the literal string you pass it to the query, so it is vulnerable to sql injection like stated in the documentation, push_bind on the other hand pushes a query placeholder ? to the query, (and binds a value to it, but that isn't relevant here) just like the documentation says

So your variant with push_bind ends up pushing " AND \"Test\" ilike '%?%'" to the query, which is just nonesense. Instead you can concatenate the 3 different strings:

if let Some(test) = &param.test {
    query_builder.push(" AND \"Test\" ilike concat('%', ");
    query_builder.push_bind(test);
    query_builder.push(", '%')");
}

---

Hint: You can check what SQL a QueryBuilder produces with the into_sql method.