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
Section titled “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 suggestnote: 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
Section titled “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" rameninput: "ramen'" error: error returned from database: unterminated quoted string at or near "'" at line 1248input: "' 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 isWHERE 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 anOR, and opens a second string. The condition the server receives isname 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
Section titled “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" rameninput: "ramen'" (no rows)input: "' OR '1'='1" (no rows)input: "%" edamame ramenThe 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
Section titled “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 requestassert!(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.