SQLx Dynamic Queries With QueryBuilder Without SQL Injection

Search pages have eight optional filters. Writing 256 query! variants is not an option, and format! with user input is an incident waiting to happen. QueryBuilder is the middle path: the shape of the query is code, and the values are always parameters. This post covers the pattern, the IN list and the one thing parameters cannot do. It extends the SQLx explained guide.

sqlx 0.8, PostgreSQL 17, Rust 1.90.

Optional filters

use sqlx::{PgPool, QueryBuilder, Postgres};

#[derive(Default)]
pub struct Filter {
    pub name_like: Option<String>,
    pub min_age: Option<i32>,
    pub ids: Vec<i64>,
}

#[derive(Debug, sqlx::FromRow)]
pub struct Person { id: i64, name: String, age: i32 }

pub async fn search(pool: &PgPool, f: &Filter) -> Result<Vec<Person>, sqlx::Error> {
    let mut q: QueryBuilder<Postgres> =
        QueryBuilder::new("SELECT id, name, age FROM people WHERE 1=1");

    if let Some(pat) = &f.name_like {
        q.push(" AND name ILIKE ").push_bind(format!("%{pat}%"));
    }
    if let Some(min) = f.min_age {
        q.push(" AND age >= ").push_bind(min);
    }
    if !f.ids.is_empty() {
        q.push(" AND id = ANY(").push_bind(&f.ids[..]).push(")");
    }
    q.push(" ORDER BY name LIMIT 50");

    q.build_query_as::<Person>().fetch_all(pool).await
}

With name_like = "an" and min_age = 30, the SQL sent is SELECT ... WHERE 1=1 AND name ILIKE $1 AND age >= $2 ORDER BY name LIMIT 50, with the values bound separately. ILIKE wildcards in the pattern itself (% and _ typed by the user) are interpreted by the database, so escape them if literal matching matters. PostgreSQL accepts the array parameter in = ANY($3), which is simpler than a variable-length IN (...) list.

IN lists on other databases

For MySQL or SQLite, use separated:

q.push(" AND id IN (");
let mut sep = q.separated(", ");
for id in &f.ids {
    sep.push_bind(*id);
}
sep.push_unseparated(")");

Mind the parameter limit: SQLite and PostgreSQL cap bound parameters per statement (32,766 and 65,535 are typical limits), so chunk very large lists.

Batch inserts

let mut q = QueryBuilder::new("INSERT INTO tags (post_id, name) ");
q.push_values(rows, |mut b, (post_id, name)| {
    b.push_bind(post_id).push_bind(name);
});
q.build().execute(pool).await?;

That is one round trip for many rows.

Sorting safely

A column name is not a value. Binding it would compare strings, not sort. Map the input:

let column = match sort.as_deref() {
    Some("age") => "age",
    Some("name") | None => "name",
    _ => return Err(sqlx::Error::Protocol("unsupported sort".into())),
};
q.push(" ORDER BY ").push(column);

push(column) is safe because column is one of your literals. Do the same for ASC and DESC.

The rules

1. Strings given to push are literals in your source.
2. Everything from outside goes through push_bind.
3. Test every branch against a real database. The compiler cannot check dynamic SQL.

When to avoid it

If there are only two or three filter combinations, write separate query_as! calls and keep the compile-time checks. For complex search, a full text engine fits better. See the discussion of injection in typed queries and SQL injection. The API is at QueryBuilder.