How to avoid SQL injection

Short answer: put a $1 where the user’s value goes and hand the value to .bind(). Never build the SQL string with format!. Since sqlx 0.9 the compiler enforces the first half of that sentence: sqlx::query() refuses a string that was built at run time, so the format! version does not even compile. Getting past that refusal — with AssertSqlSafe — is where the actual injection lives, and this page shows what it looks like before showing the fix.

The format! that everyone writes once

A search box takes a name, the query needs a pattern, and string interpolation is right there:

use sqlx::postgres::PgPoolOptions;
use sqlx::Row;

#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .connect("postgres://postgres:dev@localhost:5432/postgres")
        .await
        .expect("failed to connect to Postgres");

    let search = "ramen".to_string();
    let sql = format!("SELECT id, name FROM demo_dishes WHERE name LIKE '%{search}%'");

    let rows = sqlx::query(&sql).fetch_all(&pool).await.expect("query failed");
    for row in &rows {
        println!("{}", row.get::<&str, _>("name"));
    }
}

It reads like any other format!. With sqlx 0.9 it does not compile:

error[E0277]: dynamic SQL strings should be audited for possible injections
   --> src/main.rs:14:28
    |
 14 |     let rows = sqlx::query(&sql).fetch_all(&pool).await.expect("query failed");
    |                ----------- ^^^^ dynamic SQL string
    |                |
    |                required by a bound introduced by this call
    |
    = help: the trait `SqlSafeStr` is not implemented for `&std::string::String`
    = note: prefer literal SQL strings with bind parameters or `QueryBuilder` to add dynamic data to a query.

            To bypass this error, manually audit for potential injection vulnerabilities and wrap with `AssertSqlSafe()`.
            For details, see the docs for `SqlSafeStr`.

    = note: this trait is only implemented for `&'static str`, not all `&str` like the compiler error may suggest
note: required by a bound in `sqlx::query`
   --> /Users/rango/.cargo/registry/src/rsproxy.cn-e3de039b2554c837/sqlx-core-0.9.0/src/query.rs:653:32
    |
653 | pub fn query<'a, DB>(sql: impl SqlSafeStr) -> Query<'a, DB, <DB as Database>::Arguments>
    |                                ^^^^^^^^^^ required by this bound in `query`
help: consider dereferencing here
    |
 14 |     let rows = sqlx::query(&*sql).fetch_all(&pool).await.expect("query failed");
    |                             +

For more information about this error, try `rustc --explain E0277`.

sqlx::query takes impl SqlSafeStr, and that trait is only implemented for &'static str — a string literal the compiler can see whole. A String assembled at run time could contain anything, and sqlx refuses to guess. The suggestion to dereference (&*sql) looks like it should work and does not: the type is still a run-time &str.

The escape hatch is AssertSqlSafe, and its name is the honest part: it asserts that you audited the string yourself. It is worth seeing what you are asserting away, so the rest of this page runs the vulnerable version through that hatch first.

What the input becomes

Same program, same format!, now wrapped in AssertSqlSafe so it runs. Three searches: one ordinary, one with a stray apostrophe, one that is not a search at all. The table is the same demo_dishes from the earlier pages in this chapter:

use sqlx::postgres::PgPoolOptions;
use sqlx::AssertSqlSafe;
use sqlx::Row;

#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .connect("postgres://postgres:dev@localhost:5432/postgres")
        .await
        .expect("failed to connect to Postgres");

    sqlx::query("DROP TABLE IF EXISTS demo_dishes")
        .execute(&pool)
        .await
        .expect("drop failed");
    sqlx::query(
        "CREATE TABLE demo_dishes (
            id          INT PRIMARY KEY,
            name        TEXT NOT NULL,
            price_cents INT NOT NULL,
            views       BIGINT NOT NULL,
            notes       TEXT
        )",
    )
    .execute(&pool)
    .await
    .expect("create failed");
    sqlx::query(
        "INSERT INTO demo_dishes (id, name, price_cents, views, notes) VALUES
            (1, 'edamame', 350, 12, NULL),
            (2, 'ramen', 1450, 4007, 'extra nori')",
    )
    .execute(&pool)
    .await
    .expect("insert failed");

    for s in ["ramen", "ramen'", "' OR '1'='1"] {
        println!("input: {s:?}");
        let sql = format!("SELECT id, name FROM demo_dishes WHERE name LIKE '%{s}%'");
        match sqlx::query(AssertSqlSafe(sql)).fetch_all(&pool).await {
            Ok(rows) => {
                for row in &rows {
                    println!("  {}", row.get::<&str, _>("name"));
                }
                if rows.is_empty() {
                    println!("  (no rows)");
                }
            }
            Err(e) => println!("  error: {e}"),
        }
    }
}

input: "ramen"
  ramen
input: "ramen'"
  error: error returned from database: unterminated quoted string at or near "'" at line 1248
input: "' OR '1'='1"
  edamame
  ramen

(The at line 1248 is the server’s position marker for where its parser gave up; that number is an internal detail and can differ.)

What happened, input by input:

"ramen" lands inside the quotes and the query is WHERE name LIKE '%ramen%' — the version that was written on purpose.
"ramen'" contains the character that ends a SQL string literal. The interpolated text is '%ramen'%', the quotes no longer pair up, and the server rejects the whole statement as a syntax error. A mere apostrophe broke the query — which is also the tell that the query can be broken on purpose.
"' OR '1'='1" closes the string, writes an OR, and opens a second string. The condition the server receives is name LIKE '%' OR '1'='1%': the first half is true for every row, so the query returns the whole table. The search box just became a query language. If that condition had been guarding a login, it would have logged the user in.

That is SQL injection in one sentence: the value was parsed as SQL. Nothing was hacked; the program handed the server a string and the server did what strings do — got parsed.

Bind: the value travels as a value

The fix is to send the SQL and the value separately. The SQL keeps a $1 where the user’s value goes, and .bind() carries the value on the side:

use sqlx::postgres::PgPoolOptions;
use sqlx::Row;

#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .connect("postgres://postgres:dev@localhost:5432/postgres")
        .await
        .expect("failed to connect to Postgres");

    sqlx::query("DROP TABLE IF EXISTS demo_dishes")
        .execute(&pool)
        .await
        .expect("drop failed");
    sqlx::query(
        "CREATE TABLE demo_dishes (
            id          INT PRIMARY KEY,
            name        TEXT NOT NULL,
            price_cents INT NOT NULL,
            views       BIGINT NOT NULL,
            notes       TEXT
        )",
    )
    .execute(&pool)
    .await
    .expect("create failed");
    sqlx::query(
        "INSERT INTO demo_dishes (id, name, price_cents, views, notes) VALUES
            (1, 'edamame', 350, 12, NULL),
            (2, 'ramen', 1450, 4007, 'extra nori')",
    )
    .execute(&pool)
    .await
    .expect("insert failed");

    for s in ["ramen", "ramen'", "' OR '1'='1", "%"] {
        println!("input: {s:?}");
        match sqlx::query("SELECT id, name FROM demo_dishes WHERE name LIKE '%' || $1 || '%'")
            .bind(s)
            .fetch_all(&pool)
            .await
        {
            Ok(rows) => {
                for row in &rows {
                    println!("  {}", row.get::<&str, _>("name"));
                }
                if rows.is_empty() {
                    println!("  (no rows)");
                }
            }
            Err(e) => println!("  error: {e}"),
        }
    }
}

input: "ramen"
  ramen
input: "ramen'"
  (no rows)
input: "' OR '1'='1"
  (no rows)
input: "%"
  edamame
  ramen

The server parses the SQL once — $1 and all — and only then attaches the bound value, already committed to being a value. ' is now just a character in a string being compared, so "ramen'" matches no dish name and " OR '1'='1" is a thirteen-character string that no dish is called. The syntax-error row is gone too: there is no longer any way for the input to reach the parser.

One row in that output deserves a second look: "%" matched everything. That is not a second injection — the value stayed a value, and the query returned what it was asked for. But the value ends up inside a LIKE pattern, and LIKE reads % as “any text” wherever it appears. Binding stops the input from changing the query; it does not stop it from using the pattern language the query invited it into. If that matters, strip or escape % and _ from the input before binding it.

What you cannot bind

$1 only fits where a value fits. A table name, a column name, an ORDER BY target — those are parsed before values exist, and $1 cannot stand in for them. format! is genuinely the tool there, which is why sqlx makes you go through AssertSqlSafe for it — and why the audit happens before the assert, not after:

let sortable = ["name", "price_cents"];
let column = "price_cents"; // arrives from the request
assert!(sortable.contains(&column), "unknown sort column: {column}");
let sql = format!("SELECT id, name, price_cents FROM demo_dishes ORDER BY {column}");
let _ = sqlx::query::<sqlx::Postgres>(sqlx::AssertSqlSafe(sql));

The assert! is the whole trick: column can only reach the format! if it is one of the names on the list. A fixed list of literals is auditable by reading it; a string from the network is not. For queries assembled from many optional pieces, QueryBuilder wraps the same idea in an API — its push_bind is .bind() for a query that is being built at run time.

So the division of labor, in one breath: values go through bind, names go through a whitelist before format!, and AssertSqlSafe is the signature on the audit — not a way to make the compiler error go away.