Sql Injection in Chi with Api Keys

Chi is a lightweight HTTP routing library for OCaml, and using it with API keys often involves extracting the key from request headers and passing it into application logic or downstream services. If API key handling is combined with dynamic SQL construction, the typical injection pattern becomes possible: user-controlled input (e.g., an ID or filter parameter) is concatenated into a SQL string while the API key is used only for authentication at the handler level. The presence of an API key does not sanitize inputs; if developers mistakenly trust the API key context and build queries by string interpolation, the attack surface remains wide open.

Consider a Chi route that reads an API key from headers and uses a query parameter to fetch a user record:

let get_user = Chi.Router.get "/user" @@ fun req ->
  let open Lwt.Infix in
  match Cohttp.Header.get (Cohttp.Request.headers req) "x-api-key" with
  | Some key when validate_key key ->
    let user_id = Chi.Param.get req "id" |> Option.get in
    let query = Printf.sprintf "SELECT * FROM users WHERE id = %s" user_id in
    Lwt.return (Cohttp_lwt_unix.Body.to_string (Pool.Pool.find_by_sql query))
  | _ ->
    Cohttp_lwt_unix.Server.respond_string ~status:`Unauthorized ~body:"Invalid API key" ()

In this example, the API key is validated, but user_id is taken directly from the URL parameter and interpolated into the SQL string. An attacker can supply id=1 OR 1=1 and, if the underlying pool or driver does not enforce strict parameterization, the query may bypass intended filters. The API key check is orthogonal to data access logic; it does not prevent malicious SQL execution. Even if the API key is required, the operation can still read or modify unintended rows, demonstrating that authentication and input validation are separate concerns.

OWASP API Top 10 A03:2023 (Injection) applies here. The risk is elevated when developers conflate authentication (API key presence) with safe data handling. Real-world parallels include cases where SQL injection leads to data exfiltration or privilege escalation despite token-based checks. Tools like middleBrick detect such patterns by correlating API key handling routes with dynamic SQL usage and flagging missing parameterization.

middleBrick can scan a Chi service endpoint without credentials and highlight these risky patterns in its findings, providing prioritized guidance and references to the relevant OWASP category.

Remediation centers on strict separation of authentication and data access, and using parameterized queries or an ORM that enforces compile-time safety. Never concatenate user input into SQL strings, even when an API key has been validated.

1) Use a proper SQL client with parameterized statements. For example, with postgresql-simple:

let get_user_safe req pool =
  let open Lwt.Infix in
  match Cohttp.Header.get (Cohttp.Request.headers req) "x-api-key" with
  | Some key when validate_key key ->
    let user_id = Chi.Param.get req "id" in
    (match user_id with
     | Some uid -
       Postgresql.find pool [uid]
       |> Lwt.map (function
           | [row] -> Cohttp_lwt_unix.Server.respond_string ~status:`OK ~body:(Sexplib.Sexp.to_string_hum (row_to_sexp row))
           | _ -> Cohttp_lwt_unix.Server.respond_string ~status:`Not_found ~body:"Not found")
     | None -> Cohttp_lwt_unix.Server.respond_string ~status:`Bad_request ~body:"Missing id")
  | _ ->
    Cohttp_lwt_unix.Server.respond_string ~status:`Unauthorized ~body:"Invalid API key" ()

In this version, Postgresql.find uses a parameterized query under the hood, ensuring uid is not interpreted as SQL. The API key check remains separate and early, but the SQL execution is safe.

2) Use an OCaml type-safe query builder or SORM that enforces structure. For instance, with ocaml-sqlt or a similar library, construct queries via AST rather than strings:

let build_select id =
  let open Sql.Infix in
  Sql.(select @@ from "users" @@ where ("id" =? id))

let execute_safe req pool =
  let open Lwt.Infix inn
  match Cohttp.Header.get (Cohttp.Request.headers req) "x-api-key" with
  | Some key when validate_key key ->
    let user_id = Chi.Param.get req "id" in
    (match user_id with
     | Some uid ->
       let query = build_select uid in
       Pool.execute_sql pool query
       |> Lwt.map (function
           | Ok rows -> Cohttp_lwt_unix.Server.respond_string ~status:`OK ~body:(Sexplib.Sexp.to_string_hum (rows_to_sexp rows))
           | Error _ -> Cohttp_lwt_unix.Server.respond_string ~status:`Internal_server_error ~body:"DB error")
     | None -> Cohttp_lwt_unix.Server.respond_string ~status:`Bad_request ~body:"Missing id")
  | _ ->
    Cohttp_lwt_unix.Server.respond_string ~status:`Unauthorized ~body:"Invalid API key" ()

3) Validate and parse IDs strictly. Enforce integer parsing and reject non-numeric input before it reaches SQL:

let parse_id id_str =
  try Some (int_of_string id_str) with
  | Failure _ -> None

let get_user_typed req pool =
  let open Lwt.Infix in
  match Cohttp.Header.get (Cohttp.Request.headers req) "x-api-key" with
  | Some key when validate_key key ->
    let user_id = Chi.Param.get req "id" |> parse_id in
    (match user_id with
     | Some uid ->
       let query = Printf.sprintf "SELECT * FROM users WHERE id = $1" in
       Pool.Pool.find_by_sql_typed pool query [uid]
       |> Lwt.map (fun rows -> Cohttp_lwt_unix.Server.respond_string ~status:`OK ~body:(rows_to_json rows))
     | None -> Cohttp_lwt_unix.Server.respond_string ~status:`Bad_request ~body:"Invalid id")
  | _ ->
    Cohttp_lwt_unix.Server.respond_string ~status:`Unauthorized ~body:"Invalid API key" ()

These patterns ensure that API key authentication does not inadvertently encourage unsafe SQL construction. middleBrick’s scans can verify that endpoints requiring keys do not exhibit injection-prone behaviors and align with secure coding practices.