SQL injection

Description

Combining untrusted values into a SQL command string can make them SQL syntax rather than data. Attackers can insert quotes, operators, comments, or statement separators to change conditions and statements. This applies both to immediate execution functions such as sqlite3_exec() and PQexec() and to passing SQL assembled from external input to sqlite3_prepare_v2(), PQprepare(), mysql_stmt_prepare(), or SQLPrepare(). A prepared statement API name does not make a query safe: keep the SQL template fixed and bind values separately.

String replacement and parameter binding provide different protections. Binding separates SQL code from values at the protocol or driver level. Manually replacing quotes or calling a function named sanitize_sql() does not establish correct handling of the database, connection encoding, value or identifier position, and surrounding quote context.

Potential impact

Authentication or access-control conditions may be bypassed, exposing other users' data.
Data may be created, changed, or deleted, and database schemas or settings altered.
Depending on the database account's permissions and server features, file access, operating-system command execution, or service disruption may be possible.
Errors and differences in responses may reveal database structure or sensitive information.

Remediation

Keep the SQL template constant and pass all external values through the database's parameter API.

In SQLite, prepare fixed SQL containing ?, ?NNN, :name, @name, or $name parameters with sqlite3_prepare_v2() or sqlite3_prepare_v3(), then bind values with sqlite3_bind_*().
With PostgreSQL libpq, use PQexecParams() for a query executed once, or prepare a fixed query with PQprepare() and supply values through PQexecPrepared(). Use the corresponding parameter APIs for asynchronous code.
With the MySQL 8.4 C API, use mysql_stmt_prepare(), mysql_stmt_bind_named_param(), and mysql_stmt_execute() in order. When using the older mysql_stmt_bind_param() in a compatible client, also bind each value to its corresponding ? marker in fixed SQL.
With ODBC, use SQLPrepare(), SQLBindParameter(), and SQLExecute(). A single SQLExecDirect() call can also use parameter markers and previously bound values, but the SQL text itself must remain fixed.
Parameters generally represent values, not structures such as table names, column names, sort directions, operators, or SQL keywords. When a structural choice is necessary, compare input with a small set of allowed identifiers and map each one to a SQL fragment fixed in code.
Check the return values of preparation, binding, and execution calls, and release statement and result objects with the correct APIs. Grant the database account only the permissions needed for its work.

If dynamic SQL is unavoidable, use the database's context-specific escaping functions and insert their results only in the intended SQL position. SQLite %q doubles single quotes without adding surrounding quotes; %Q adds quotes for a SQL string literal. libpq's PQescapeLiteral() and PQescapeIdentifier() likewise serve different syntax positions. Reusing those results elsewhere or inside already-open quotes can invalidate the protection; do not treat these functions as generic sanitizers. OWASP also prefers parameterized queries over escaping all input.

Examples

C

Before

#include <stddef.h>
#include <sqlite3.h>

int find_user_unsafe(sqlite3 *database, const char *name) {
    char *sql = sqlite3_mprintf(
        "SELECT id FROM users WHERE name = '%s'", name);
    if (sql == NULL) {
        return SQLITE_NOMEM;
    }

    int result = sqlite3_exec(database, sql, NULL, NULL, NULL);
    sqlite3_free(sql);
    return result;
}

After

#include <stddef.h>
#include <sqlite3.h>

int find_user(sqlite3 *database, const char *name) {
    static const char query[] =
        "SELECT id FROM users WHERE name = ?";
    sqlite3_stmt *statement = NULL;

    int result = sqlite3_prepare_v3(
        database, query, -1, 0, &statement, NULL);
    if (result != SQLITE_OK) {
        return result;
    }

    result = sqlite3_bind_text(
        statement, 1, name, -1, SQLITE_TRANSIENT);
    if (result != SQLITE_OK) {
        sqlite3_finalize(statement);
        return result;
    }

    result = sqlite3_step(statement);
    int finalize_result = sqlite3_finalize(statement);
    if (result != SQLITE_ROW && result != SQLITE_DONE) {
        return result;
    }
    return finalize_result;
}

Explanation:

Before: %s inserts the external value directly into the SQL string, allowing it to change quotes and SQL syntax.
After: The SQL structure and ? parameter are fixed; name is passed as data through sqlite3_bind_text(). The protection comes from fixed SQL, not merely from calling sqlite3_prepare_v3().

C++

Before

cpp

#include <sqlite3.h>
#include <string>

int find_user_unsafe(sqlite3 *database, const std::string &name) {
    const std::string sql =
        "SELECT id FROM users WHERE name = '" + name + "'";
    return sqlite3_exec(
        database, sql.c_str(), nullptr, nullptr, nullptr);
}

After

cpp

#include <memory>
#include <sqlite3.h>
#include <string>

struct StatementDeleter {
    void operator()(sqlite3_stmt *statement) const noexcept {
        sqlite3_finalize(statement);
    }
};

int find_user(sqlite3 *database, const std::string &name) {
    constexpr char query[] =
        "SELECT id FROM users WHERE name = ?";
    sqlite3_stmt *raw_statement = nullptr;

    int result = sqlite3_prepare_v3(
        database, query, -1, 0, &raw_statement, nullptr);
    if (result != SQLITE_OK) {
        return result;
    }

    const std::unique_ptr<sqlite3_stmt, StatementDeleter>
        statement{raw_statement};
    result = sqlite3_bind_text(
        statement.get(), 1, name.c_str(), -1, SQLITE_TRANSIENT);
    if (result != SQLITE_OK) {
        return result;
    }

    result = sqlite3_step(statement.get());
    if (result == SQLITE_ROW || result == SQLITE_DONE) {
        return SQLITE_OK;
    }
    return result;
}

Explanation:

Before: Using std::string does not separate code from data when an external value is concatenated into SQL. c_str() only changes the representation; it does not prevent SQL injection.
After: The code prepares fixed SQL and binds name as a value. The custom deleter for std::unique_ptr releases the prepared statement with sqlite3_finalize() on every return path.

Use a fixed mapping to select structures that cannot be bound, such as identifiers:

#include <sqlite3.h>
#include <string.h>

int list_users(sqlite3 *database, const char *sort_key) {
    const char *query = NULL;
    if (strcmp(sort_key, "name") == 0) {
        query = "SELECT id, name FROM users ORDER BY name";
    } else if (strcmp(sort_key, "created") == 0) {
        query = "SELECT id, name FROM users ORDER BY created_at";
    } else {
        return SQLITE_MISUSE;
    }

    return sqlite3_exec(database, query, NULL, NULL, NULL);
}

Description

Potential impact

Remediation

Examples

C

Before

After

C++

Before

After

References