FlawOpen

flawopen.com/sql-injection/C-cpp

● CWE-89 · Critical
FlawOpen Security Research

SQL Injection in C/C++ (SQLite, libpq)

Formatting a request value into a SQL buffer with snprintf() and passing it to sqlite3_exec() or PQexec() lets the caller rewrite the query and return every row (CWE-89). The bound-parameter calls for SQLite, libpq and the MySQL C API, and how to quote a column name safely.

💡 Plain English Explainer (ELI5)

Think of a two-stage machine: first it engraves a fixed SQL form with numbered sockets; only afterward does it attach the caller’s values as data. snprintf() puts request text into the instruction sheet before the database parses it, and a smaller buffer only limits the paper—it does not make the query safe. sqlite3_prepare_v2() compiles the form first, then sqlite3_bind_*() fills a value slot; PostgreSQL PQexecParams() sends SQL and values separately. A column name for ORDER BY is not a value slot, so choose it from an allowlist and quote the identifier with PQescapeIdentifier().

Core Concepts & Subsystem Terms

snprintf() into a SQL buffer
snprintf(sql, n, "... = %s", input) copies the caller's text into the statement. The size limit protects the buffer, not the query: truncation can even cut off a trailing condition. Format specifiers are not placeholders.
sqlite3_exec() vs sqlite3_prepare_v2()
sqlite3_exec() compiles and runs every statement in the string, one after another, and has no way to bind values. sqlite3_prepare_v2() compiles only the first statement and leaves ? or ?1 slots for values.
sqlite3_bind_*()
sqlite3_bind_int64(), sqlite3_bind_text() and friends attach a value to a compiled slot. Pass SQLITE_TRANSIENT for text whose buffer may change before sqlite3_step(). The MySQL C API equivalent is mysql_stmt_prepare() plus mysql_stmt_bind_param().
PQexecParams()
Sends the SQL and a separate array of values; PostgreSQL fills $1, $2 itself. Unlike PQexec(), it accepts only one statement per call, so a ; in the input cannot start a second one.
PQescapeIdentifier()
Placeholders cannot carry table or column names. PQescapeIdentifier() double-quotes a name and escapes embedded quotes; the result must be freed with PQfreemem(). Check the name against an allowlist first.

Framework mappings

Step-by-Step Attack Flow

Step 1

Request value reaches the handler

A CGI or embedded HTTP handler copies the id query parameter into a char * and calls find_user(). No strtoll() check ever runs.

Step 2

snprintf builds the statement

The %s specifier copies the text verbatim. With the benign test value 0 OR 1=1, the buffer holds ... WHERE id = 0 OR 1=1.

Step 3

sqlite3_exec runs whatever is in the buffer

sqlite3_exec() compiles the whole string. OR 1=1 becomes part of the WHERE clause, and a ; would start a further statement that also runs.

Step 4

The callback receives every row

print_row() is called once per row in the table. The libpq version behaves the same way through PQexec(), which also accepts multiple statements.

Source Code: Flaw vs. Secure Implementation

UNPATCHED FLAW
/* users.c: each query below formats request data into the SQL buffer */
#include <libpq-fe.h>
#include <sqlite3.h>
#include <stdio.h>

int print_row(void *ctx, int ncols, char **values, char **names);

/* SQLite: %s copies the text; sqlite3_exec runs every statement it finds */
int find_user(sqlite3 *db, const char *user_id)
{
    char sql[256];
    snprintf(sql, sizeof sql, "SELECT id, email FROM users WHERE id = %s", user_id);
    return sqlite3_exec(db, sql, print_row, NULL, NULL);
}

/* libpq: PQexec sends one finished string */
PGresult *find_orders(PGconn *conn, const char *status)
{
    char sql[256];
    snprintf(sql, sizeof sql, "SELECT id FROM orders WHERE status = '%s'", status);
    return PQexec(conn, sql);
}

/* the ORDER BY column is copied straight from the request */
PGresult *list_users(PGconn *conn, const char *sort_column)
{
    char sql[256];
    snprintf(sql, sizeof sql, "SELECT id, email FROM users ORDER BY %s", sort_column);
    return PQexec(conn, sql);
}
HARDENED SECURE PATCH
/* users.c: values are bound, column names come from an allowlist */
#include <libpq-fe.h>
#include <sqlite3.h>
#include <stdio.h>
#include <string.h>

void print_stmt_row(sqlite3_stmt *stmt);

/* SQLite: ?1 slot compiled first, value bound as a 64-bit integer */
int find_user(sqlite3 *db, sqlite3_int64 user_id)
{
    sqlite3_stmt *stmt = NULL;
    int rc = sqlite3_prepare_v2(db, "SELECT id, email FROM users WHERE id = ?1", -1, &stmt, NULL);
    if (rc != SQLITE_OK)
        return rc;
    sqlite3_bind_int64(stmt, 1, user_id);
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
        print_stmt_row(stmt);
    sqlite3_finalize(stmt);
    return rc == SQLITE_DONE ? SQLITE_OK : rc;
}

/* libpq: PostgreSQL fills $1 from the separate values array */
PGresult *find_orders(PGconn *conn, const char *status)
{
    const char *values[1] = { status };
    return PQexecParams(conn, "SELECT id FROM orders WHERE status = $1",
                        1, NULL, values, NULL, NULL, 0);
}

/* allowlist lookup, then quote the identifier with PQescapeIdentifier */
PGresult *list_users(PGconn *conn, const char *sort_column)
{
    static const char *const allowed[] = { "id", "email", "created_at" };
    const char *column = "id";
    for (size_t i = 0; i < sizeof allowed / sizeof allowed[0]; i++)
        if (strcmp(sort_column, allowed[i]) == 0)
            column = allowed[i];

    char *quoted = PQescapeIdentifier(conn, column, strlen(column));
    if (quoted == NULL)
        return NULL;
    char sql[128];
    snprintf(sql, sizeof sql, "SELECT id, email FROM users ORDER BY %s", quoted);
    PQfreemem(quoted);
    return PQexecParams(conn, sql, 0, NULL, NULL, NULL, NULL, 0);
}

Engineering & System Hardening Checklist

Sources