Security & SQL Injection Protection

PDOdb implements multiple internal layers to prevent unsafe SQL queries, injections, and misuse of expressions. Security is active by default and applies to all where*, orderBy*, groupBy*, and insert/update operations – without the need for manual escaping.

Safe WHERE clauses (heuristic)

All where() conditions pass through a secure parser. If an unsafe pattern is detected in the value, an exception is thrown before the query is executed.

This is controlled by the global constant:

define('PDOdb_HEURISTIC_WHERE_CHECK', true);

If enabled (default), PDOdb checks for suspicious input like SQL keywords (OR 1=1, --, SLEEP()) or malformed types. Example: 

// Dangerous value in WHERE
$db->where('email', "admin@example.com' OR 1=1");
$db->get('test_users'); // throws exception immediately
Heuristic validation: Integer values are explicitly cast using (int), and then verified by length. If the input contains trailing text (e.g. "1abc"), it will be rejected.

Function placeholders

PDOdb supports SQL function injection using the special [F] marker or $db->func(). These expressions are validated via a whitelist to prevent abuse.

  • Allowed: NOW(), UUID(), DATE_FORMAT(), CONCAT(), etc.
  • Blocked: SLEEP(), LOAD_FILE(), BENCHMARK(), etc.
$db->whereFunc('DATE(order_date)', $db->now(), '<=');
The full whitelist is defined in _secureAllowedFunctions() and can be extended manually if needed.

Order / Group Validation

PDOdb strictly validates orderBy() and groupBy() expressions – even those using SQL functions. This prevents raw injection such as:

$db->orderBy("1=1; DROP TABLE users"); // blocked

Expressions are only allowed if they match the allowed function list, contain safe column names, or are simple identifiers with optional aliases (AS ...).

Allowed SQL Functions

The following functions are allowed (case-insensitive):

DATE, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DATE_FORMAT, CONCAT, CONCAT_WS, LEFT, RIGHT, UPPER, LOWER, LENGTH, TRIM, ROUND, ABS, IF, IFNULL, COALESCE, SUBSTRING, SUBSTRING_INDEX, LOCATE, FIELD, GREATEST, LEAST, LPAD, RPAD, MD5, CASE, CONVERT_TZ, JSON_UNQUOTE, JSON_EXTRACT
Note: If a function is not in the whitelist, it will be rejected and cause an exception. This applies to any use of [F], func(), or SQL expression in order/group/filter clauses.

Optional Bulk Insert Check

For insertBulk(), no value validation is performed by default to ensure maximum performance. However, you can activate internal safety checks with:

$db->enableBulkCheck(true);

This forces each row to be validated individually using the same rules as insert() or insertMulti(). This includes:

  • Column name validation via _secureIsSafeColumn()
  • Data type checks and rejection of arrays or nested expressions
  • No support for [F], [I], [N] or subquery values
Warning: This option should only be enabled if your input source is untrusted or user-controlled. It significantly slows down bulk processing and is disabled by default.
Example use case: enabling this flag for a bulk CSV upload or API endpoint to block malformed records.

See Also