SQL injection
Need
Prevention of SQL injection by binding user input as query parameters
Context
• Usage of Rust 1.75+ for building application services
• Usage of the actix-web crate for handling HTTP requests
• Usage of the sqlx crate with PostgreSQL for database access
Description
1. Non compliant code
use actix_web::{get, web, HttpResponse};
use serde::Deserialize;
use sqlx::PgPool;
#[derive(Deserialize)]
struct SearchQuery {
name: String,
}...The `search_users` handler below builds a SQL statement with `format!`, inserting the `name` query parameter between single quotes, and runs it with `sqlx::query_scalar`. The database receives a single string and cannot tell which part came from the developer and which from the caller. A `name` of `' OR '1'='1` returns every row, and `'; DROP TABLE users; --` can run additional statements when the driver allows it. With `UNION SELECT` payloads an attacker can read other tables, including credentials. Using `sqlx::query` with a `String` built at runtime disables the protection that sqlx offers. The same flaw appears with Diesel when `diesel::sql_query` receives a `format!` result instead of a literal with `.bind` calls.
2. Steps
• Write SQL statements as string literals with placeholders (`$1` for PostgreSQL, `?` for MySQL and SQLite) and pass request data with `.bind`.
• Never build the query string with `format!`, `push_str` or `+` from request data, whether it is used with `sqlx::query`, `sqlx::query_as` or `diesel::sql_query`.
• Prefer the compile-time checked `sqlx::query!` and `sqlx::query_as!` macros, or the Diesel query builder, which bind values automatically.
• Map dynamic identifiers such as column names and sort directions through an allowlist; placeholders cannot protect identifiers.
• Use `sqlx::QueryBuilder::push_bind` when a query has a variable number of conditions.
• Connect with a database role that has only the privileges the service needs.
3. Secure code example
use actix_web::{get, web, HttpResponse};
use serde::Deserialize;
use sqlx::PgPool;
#[derive(Deserialize)]
struct SearchQuery {
name: String,
}...The corrected handler keeps the SQL text as a string literal with a `$1` placeholder and supplies the value with `.bind(&query.name)`. With a bound parameter, PostgreSQL receives the statement and the value separately, through the extended query protocol. The value is always treated as data, so quotes, comments or semicolons inside it cannot change the structure of the query, and the statement can be prepared and cached. Placeholders only work for values. When a caller must choose a column or a sort direction, the code has to map the input to a fixed set of identifiers instead of interpolating it. The `sqlx::query!` macros go one step further by checking the statement against the database schema at compile time.
References
• 146. SQL injection