# SQL Query Security

Handwritten SQL is not inherently unsafe. The important distinction is whether data is passed separately from the SQL structure. Bind every external value; keep dynamic SQL identifiers and fragments under application control.

# Bind values with parameters

Pass request data, form values, API values, and other runtime values in the parameter array. The database adapter then treats them as values rather than SQL syntax:

$sql = <<<'SQL'
    SELECT id, bank_name, account_name
    FROM bnk_overview_personal_accounts
    WHERE product_type = :product_type
      AND is_active = :is_active
    SQL;

$accounts = $database->fetchAll($sql, [
    'product_type' => $productType,
    'is_active' => 1,
]);

Do not interpolate a variable into the SQL string:

// Unsafe: the value becomes part of the SQL text.
$sql = "SELECT id FROM bnk_overview_personal_accounts WHERE product_type = '$productType'";

The unsafe form can allow SQL injection if the value contains SQL syntax that changes the query. Validation is not a substitute for parameter binding; a weak or changing validation rule can still leave SQL structure exposed. The fact that input looks like JavaScript does not change the SQL rules. With a bound parameter, quotes and SQL-looking text remain data. Do not put quotes around the placeholder: write product_type = :product_type, not product_type = ':product_type'.

# Parameters bind values, not identifiers

A placeholder cannot stand for a table name, column name, keyword, or sort direction. For example, ORDER BY :column orders by a value; it does not select a column. Map external choices to a fixed set of identifiers and constrain the direction before composing SQL:

$allowedSortColumns = [
    'bonus' => 'cash_bonus_max',
    'bank' => 'bank_name',
];

$sortColumn = $allowedSortColumns[$sort] ?? 'bank_name';
$sortDirection = strtoupper($direction) === 'DESC' ? 'DESC' : 'ASC';

$sql = "SELECT bank_name, cash_bonus_max
        FROM bnk_overview_personal_accounts
        ORDER BY {$sortColumn} {$sortDirection}";

Interpolation is safe here only because both interpolated fragments come from application-controlled choices. Never use a request value directly as an identifier.

# Validate as well as bind

Validation checks whether input is meaningful for the application; parameter binding protects the SQL statement structure. Use both:

• Parse record IDs as the expected type and reject invalid ranges.
• Check categories and product types against allowed values.
• Bound page sizes and result limits.
• Allow only supported sort columns and ASC/DESC directions.
• Apply authorization before returning or changing records.

Even a value that passes validation must still be bound when it is used as a SQL value. SQL parameterization is also separate from HTML output escaping; a value safely queried from the database still needs context-appropriate escaping when rendered in a page.

# Dynamic SQL fragments

Some calculations or database-specific expressions are clearer as SQL fragments. A fragment such as $baseRatingSql is safe to interpolate only when it is produced by trusted application code (for example, a fixed method or constant). Keep user-controlled values in bound parameters even when the rest of the query is generated dynamically.

SqlExpression::raw() and expression-builder methods represent SQL structure, not input sanitizers. Pass them only trusted expressions. If a fragment needs a dynamic field name, derive it from an allowlist; if it needs a dynamic value, use a placeholder.

# DBM Framework parameter handling

The PDO adapter's fetchAll() path calls query(), prepares the SQL, binds values with bindValue(), and executes the statement. Named parameter keys may be supplied with or without the leading colon. DBM Platform's Doctrine adapter forwards the SQL, parameters, and optional types to Doctrine DBAL's executeQuery() or executeStatement().

The current PDO adapter enables PDO::ATTR_EMULATE_PREPARES for its non-SQLite connection. Emulated prepares do not make correctly bound values equivalent to string interpolation; continue using the parameter API. Driver behavior can differ, so avoid relying on database-specific parsing details when a portable parameterized query is available.

# SQL or Query Builder?

The Query Builder is a composition tool, not an automatic security boundary. Its values should still be bound, and dynamic identifiers must still be controlled. Complex ranking queries can remain handwritten SQL when that is clearer; DBM Framework does not require forcing every query through a builder.

# Code-review checklist

• No user-controlled data is concatenated into SQL text.
• Dynamic values are passed through parameters, including values that have already been validated.
• Dynamic table and column names come from trusted code or an allowlist.
• Sort direction is restricted to ASC or DESC.
• Input types, ranges, allowed values, and authorization are checked.
• SqlExpression::raw() and other SQL fragments contain trusted application SQL only.
• Database credentials use a least-privilege account appropriate for the application's operations.