Sql Injection in Actix with Cockroachdb

SQL injection in an Actix web service that uses CockroachDB arises when user-controlled input is incorporated into SQL strings without proper parameterization. CockroachDB follows PostgreSQL wire protocol and SQL semantics, so injection techniques that work against PostgreSQL also apply. In Actix handlers, constructing queries by concatenating or formatting strings with user input—such as path parameters, query strings, or JSON bodies—can result in unexpected behavior if the input is not strictly validated or parameterized.

For example, consider an endpoint that retrieves a user by ID directly from the request path. If the handler builds a query like format("SELECT id, email FROM users WHERE id = %s", user_id) and interpolates it into sqlx::query(), malicious input such as 1 OR 1=1 can change the logic of the query. Because CockroachDB returns results in a standard PostgreSQL-compatible format, the injected condition may cause the query to return multiple rows or sensitive accounts. Actix’s asynchronous runtime can amplify the impact by allowing more concurrent exploitation attempts, and since the attack surface is unauthenticated in middleBrick scans, such endpoints are quickly identified.

Another common pattern is using dynamic sorting or table names based on user input. For instance, accepting a sort query parameter and embedding it into an SQL ORDER BY clause without allowlisting introduces injection risks. CockroachDB’s compliance with PostgreSQL syntax means that payloads like 'name' DESC; -- can terminate the intended clause and append new statements. MiddleBrick’s checks for input validation and property authorization highlight these issues by correlating OpenAPI specifications with runtime behavior, emphasizing the need to treat all inputs as untrusted even when the database is strongly consistent.

SSRF and external connectivity configurations can further influence exposure. If Actix services are deployed in an environment where database connections are reachable from endpoints that accept URLs, an attacker might attempt to pivot through SQL injection to interact with internal services. The combination of an unauthenticated scan and complex deployment topologies increases the likelihood of overlooked injection paths. Therefore, validating and encoding inputs, using prepared statements, and restricting database permissions are essential to reduce risk when using Actix with CockroachDB.

Remediation centers on strict use of parameterized queries and compile-time verification wherever possible. Avoid string interpolation or concatenation for SQL fragments. Instead, rely on sqlx’s query-as-type features and bind variables. For Actix services, this means handling parameters in route extractors or query structs and passing them explicitly to CockroachDB.

Below is a secure example of retrieving a user by ID with proper parameterization:

use actix_web::{web, HttpResponse, Result};
use sqlx::postgres::PgPoolOptions;
use sqlx::Row;

pub async fn get_user(
    pool: web::Data<sqlx::PgPool>,
    path: web::Path<(i32,)>,
) -> Result<HttpResponse> {
    let (user_id,) = path.into_inner();
    let row = sqlx::query("SELECT id, email FROM users WHERE id = $1")
        .bind(user_id)
        .fetch_one(pool.get_ref())
        .await?;
    let email: String = row.try_get("email")?;
    Ok(HttpResponse::Ok().json(serde_json::json!({ "email": email })))
}

Note the use of $1 as a placeholder and .bind(user_id) to ensure the value is sent separately from the SQL string. This pattern prevents injection regardless of the content of user_id.

For dynamic sorting, use an allowlist approach:

use actix_web::web;

fn validate_sort_column(input: &str) -> Option<&'static str> {
    match input {
        "name" => Some("name"),
        "created_at" => Some("created_at"),
        _ => None,
    }
}

pub async fn list_users(
    pool: web::Data<sqlx::PgPool>,
    web::Query(params): web::Query<HashMap<String, String>>,
) -> Result<HttpResponse> {
    let order_column = validate_sort_column(params.get("sort").map_or("", |s| s.as_str()))
        .unwrap_or("id");
    // Safe: column name is from allowlist, interpolated as identifier via format!
    let sql = format!("SELECT id, email FROM users ORDER BY {}", order_column);
    let rows = sqlx::query(&sql).fetch_all(pool.get_ref()).await?;
    // ... serialize and respond
    Ok(HttpResponse::Ok().finish())
}

When working with CockroachDB, also consider using strongly-typed queries and migrations to keep schema and code aligned. Tools like sqlx::migrate! can ensure the database state matches expectations. Avoid constructing SQL from JSON keys or headers without strict validation, and apply least-privilege database roles to limit the impact of any residual misconfiguration.