ZenDB AI Reference

This is a consolidated reference for AI coding assistants. It contains everything needed to write correct ZenDB code in a single file, and covers ZenDB 1.0. For human-friendly docs with tutorials and explanations, see Getting Started.

Querying Data - select(), selectOne(), count()
Modifying Data - insert(), update(), delete(), transaction()
WHERE Condition Forms - array, positional ?, named :placeholders
Placeholders & Parameters - ?, :name, backtick identifiers, :: table prefix
Type Handling - PHP-to-SQL conversion for WHERE and INSERT/UPDATE values
Custom SQL - query(), queryOne(), smart joins, clone()
Results & Values - SmartArray/SmartString hierarchy, HTML-encoding, methods
Helpers - pagination, LIKE patterns, raw SQL, date constants, table names
Connection - config options, connection management, raw mysqli access, multiple connections
Encryption (opt-in)
Template Safety Rules - what SQL templates reject and allow
Common Errors Quick Reference - exact messages and fixes
Gotchas - the silent failure cases

What is ZenDB

ZenDB is a PHP/MySQL database library where SQL injection is designed out: values can only enter a query through placeholders, which escape and quote them before it runs. SQL templates are scanned before execution — quotes, standalone numbers, backslashes, NULL bytes, and CTRL-Z are rejected outright. The one gap is an identifier interpolated into a template by hand (see Gotchas). Every value returned from queries is a SmartString that auto-HTML-encodes in string context, preventing XSS by default.

use Itools\ZenDB\DB;

DB::connect([
    'hostname' => 'localhost',
    'username' => 'dbuser',
    'password' => 'secret',
    'database' => 'my_app',
]);

$users = DB::select('users', ['status' => 'active']);
foreach ($users as $user) {
    echo "Hello, $user->name!"; // auto HTML-encoded
}

Querying Data

DB::select() — Multiple Rows

Returns SmartArrayHtml collection of rows.

// All rows
$users = DB::select('users');

// WHERE array (simple equality, joined with AND)
$users = DB::select('users', ['status' => 'Active', 'city' => 'Vancouver']);

// SQL + positional placeholders (max 3 separate args)
$users = DB::select('users', "status = ? AND city = ?", 'Active', 'Vancouver');

// SQL + named placeholders
$users = DB::select('users', "status = :status AND city = :city", [
    ':status' => 'Active',
    ':city'   => 'Vancouver',
]);

// ORDER BY and LIMIT
$users = DB::select('users', "status = ? ORDER BY name DESC LIMIT ?", 'Active', 10);

// Pagination
$users = DB::select('users', "ORDER BY name :pagingSQL", [
    ':pagingSQL' => DB::pagingSql(2, 25),
]);

DB::selectOne() — Single Row

Returns first matching row as SmartArrayHtml. Auto-adds LIMIT 1. Returns empty SmartArrayHtml if not found. Throws if you add LIMIT or OFFSET.

$user = DB::selectOne('users', ['id' => 123]);
echo $user->name;

if ($user->isEmpty()) {
    echo "Not found";
}

DB::count() — Row Count

Returns int. Throws if you add LIMIT or OFFSET.

$total  = DB::count('users');
$active = DB::count('users', ['status' => 'Active']);

Modifying Data

DB::insert() — Returns Insert ID

$newId = DB::insert('users', [
    'name'       => 'Alice',
    'status'     => 'Active',
    'created_at' => DB::rawSql('NOW()'),
]);

DB::update() — Returns Affected Rows

Signature: update($table, $values, $whereEtc, ...$params) — values first, then WHERE. WHERE is required.

$affected = DB::update('users',
    ['city' => 'Toronto', 'status' => 'Active'],      // SET
    ['id' => 123]                                     // WHERE
);

// SQL WHERE
DB::update('users', ['status' => 'Inactive'], "lastLogin < ?", '2026-01-01');

// Update all rows (must be explicit)
DB::update('users', ['status' => 'archived'], "TRUE");

Reversed argument detection: If SET clause is a single column named num, id, or ID, ZenDB assumes you reversed the arguments and throws.

DB::delete() — Returns Affected Rows

WHERE is required.

$deleted = DB::delete('users', ['id' => 123]);
$deleted = DB::delete('users', "status = ?", 'Suspended');

DB::transaction() — Atomic Operations

Commits when the callback returns, rolls back and rethrows on exception. Returns the callback’s return value. Nested transactions throw.

DML only (SELECT/INSERT/UPDATE/DELETE). DDL (CREATE, ALTER, DROP, TRUNCATE) silently commits all pending work and ends the transaction; everything after runs in autocommit mode with no rollback possible. TRUNCATE is DDL — use DELETE inside transactions.

$orderId = DB::transaction(function() use ($userId, $skus) {
    $orderId = DB::insert('orders', ['userId' => $userId]);
    foreach ($skus as $sku) {
        DB::insert('order_items', ['orderId' => $orderId, 'sku' => $sku]);
    }
    return $orderId;
});

WHERE Condition Forms

1. Array (recommended for simple equality)

All conditions joined with AND. Special type handling:

['status' => 'Active']                  // `status` = 'Active'
['age' => 25]                           // `age` = 25
['isAdmin' => true]                     // `isAdmin` = TRUE
['isAdmin' => null]                     // `isAdmin` IS NULL
['status' => ['Active', 'Inactive']]    // `status` IN ('Active','Inactive')
['created_at' => DB::rawSql('NOW()')]   // `created_at` = NOW()

2. SQL + positional `?` (max 3 values, passed as direct args)

DB::select('users', "status = ? AND age > ?", 'Active', 25);
// 4+ values: use named placeholders (next section)

3. SQL + named `:placeholders`

DB::select('users', "status = :status AND city = :city", [
    ':status' => 'Active',
    ':city'   => 'Vancouver',
]);

Named placeholders can be reused in the same query.

Placeholders & Parameters

Positional `?`

Max 3, passed as separate arguments. For 4 or more values, or any array value, use named placeholders.

DB::select('users', "name = ? AND city = ?", 'John', 'Vancouver');

// WRONG - PDO habit, one array of positional values: deprecated, and only the first element is used
DB::select('users', "id IN (?)", [1, 2, 3]);                // runs as: id IN (1)

// RIGHT - arrays go through named placeholders
DB::select('users', "id IN (:ids)", [':ids' => [1, 2, 3]]); // runs as: id IN (1,2,3)

Named `:name`

Names start with a letter, then letters/digits/underscores (:[a-zA-Z]\w*). Reusable in same query.

DB::query("SELECT * FROM ::users WHERE city = :city OR birthplace = :city", [
    ':city' => 'Vancouver',
]);

Backtick Identifier Placeholders

For dynamic table/column names. Validated: only [a-zA-Z0-9_-] allowed.

// with tablePrefix 'cms_' this runs: SELECT `name` FROM cms_users
DB::query("SELECT `?` FROM ::users", 'name');
DB::query("SELECT `:col` FROM ::users", [':col' => 'name']); // same

Table Prefix `::`

Replaced with configured tablePrefix from DB::connect().

DB::query("SELECT * FROM ::users JOIN ::orders ON ::users.id = ::orders.user_id");
// with tablePrefix 'cms_' this runs:
// SELECT * FROM cms_users JOIN cms_orders ON cms_users.id = cms_orders.user_id

// Dynamic table with prefix
// with tablePrefix 'cms_' this runs: SELECT * FROM `cms_users`
DB::query("SELECT * FROM `::?`", 'users');
DB::query("SELECT * FROM `:::table`", [':table' => 'users']); // same

Prefixed Value Placeholders `::?` / `:::name`

No backticks: the table prefix goes inside the quoted value, for matching table names as strings. Strings only (or arrays of strings); anything else throws InvalidArgumentException.

// with tablePrefix 'cms_' this runs: SHOW TABLES LIKE 'cms_user%'
DB::query("SHOW TABLES LIKE ::?", 'user%');

// with tablePrefix 'cms_' this runs: ... WHERE TABLE_NAME = 'cms_users'
DB::query("SELECT * FROM information_schema.TABLES WHERE TABLE_NAME = :::table", [':table' => 'users']);

// with tablePrefix 'cms_' this runs: ... WHERE TABLE_NAME IN ('cms_users', 'cms_orders')
DB::query("SELECT * FROM information_schema.TABLES WHERE TABLE_NAME IN (:::tables)", [':tables' => ['users', 'orders']]);

Type Handling

PHP Type	SQL Output	Example
`string`	Quoted and escaped	`'John'`
`int`	Unquoted	`42`
`float`	Unquoted	`9.5`
`null`	`NULL`	`NULL`
`bool`	`TRUE` / `FALSE`	`TRUE`
`array` in WHERE or `:name`	Comma-separated IN list	`1,2,3`
`array` anywhere else	Throws	`implode()`/`json_encode()` first
`RawSql`	As-is (no escaping)	`NOW()`

Critical: String "10" becomes '10' (quoted). Integer 10 becomes 10 (unquoted). This matters for LIMIT — always use int, not string.

SmartString, SmartNull, and SmartArray values are auto-unwrapped to their underlying types before processing.

`$values` Type Handling (INSERT/UPDATE)

DB::insert('users', [
    'name'       => 'John',                // string  -> quoted, escaped
    'age'        => 30,                    // int     -> unquoted
    'score'      => 9.5,                   // float   -> unquoted
    'isAdmin'    => true,                  // bool    -> TRUE
    'bio'        => null,                  // null    -> NULL
    'created_at' => DB::rawSql('NOW()'),   // RawSql  -> as-is
    'views'      => DB::rawSql('views + 1'), // SQL expression
]);

DB::query() / DB::queryOne() — Custom SQL

Full SQL with all safety checks still enforced; query() returns SmartArrayHtml collection; queryOne() returns the first row only. Auto-adds LIMIT 1 (for SELECT/WITH). Throws if you add LIMIT or OFFSET.

$rows = DB::query(
    "SELECT u.name, COUNT(o.order_id) AS order_count
       FROM ::users u
       LEFT JOIN ::orders o ON o.user_id = u.id
      WHERE u.status = ?
      GROUP BY u.id
      ORDER BY order_count DESC",
    'Active'
);

$row = DB::queryOne("SELECT MAX(price) AS max_price FROM ::products");
echo $row->max_price;

Smart Joins

When a query returns columns from multiple tables and useSmartJoins is true (default), ZenDB adds qualified table.column keys alongside the plain keys. Qualified keys contain a dot, so read them with ->{'table.column'} syntax.

$rows = DB::query("SELECT * FROM ::users u JOIN ::orders o ON u.id = o.user_id");

foreach ($rows as $row) {
    $row->id;                  // plain key: duplicate names keep the FIRST column's value
    $row->{'users.id'};        // always the users table's id
    $row->{'orders.id'};       // always the orders table's id
}

Qualified keys use the base table name: no prefix (users.name, never cms_users.name) and never the alias (FROM ::users u still produces users.name, not u.name). Computed columns (AS alias) get only their alias, no qualified key. Self-joins are the one exception: when the same table appears twice, alias keys are added too ($row->{'a.name'}, $row->{'b.name'}).

DB::clone() — Override Settings

$db = DB::clone(['useSmartJoins' => false]);
$rows = $db->query("SELECT u.name FROM ::users u JOIN ::orders o ON u.id = o.user_id");

Shares the same mysqli connection with different config. Original unaffected. Only tablePrefix, useSmartJoins, and useSmartStrings can be overridden; any other key throws.

Results & Values

Hierarchy

Query -> Result set (SmartArrayHtml) -> Rows (SmartArrayHtml) -> Values (SmartString)

HTML-Encoding (Automatic)

SmartString auto-HTML-encodes in string context (echo, print, interpolation):

echo $row->name;                    // HTML-encoded (safe for output)
echo $row->name->value();           // Raw original value and type
echo $row->name->rawHtml();         // Alias for value() (trusted HTML)

With useSmartStrings => false (connect or clone), results are plain SmartArray collections of raw values - nothing auto-HTML-encodes, so encode on output yourself.

Value Access & Encoding

Expression	Result
`$row->name`	HTML-encoded in string context
`$row->name->value()`	Raw value, original PHP type
`$row->name->rawHtml()`	Alias for `value()`
`$row->name->htmlEncode()`	Explicit HTML encoding
`$row->name->urlEncode()`	URL-encoded
`$row->name->jsonEncode()`	JSON-encoded
`$row->name->int()`	Cast to int
`$row->name->float()`	Cast to float
`$row->name->string()`	Cast to string (unencoded)
`$row->col ?? 'default'`	Fallback when the key is missing or the value is NULL

Text Methods

Method	Description
`->textOnly()`	Strip HTML, decode entities, trim
`->maxChars(100, '...')`	Limit to N chars with suffix
`->maxWords(20, '...')`	Limit to N words with suffix
`->nl2br()`	Encode + newlines to `<br>` (returns string)
`->trim()`	Trim whitespace

Formatting & Conditionals

Method	Description
`->dateFormat('M j, Y')`	Format date
`->numberFormat(2)`	Format number
`->or('N/A')`	Fallback if null or empty string (zero stays)
`->ifZero('None')`	Fallback if zero
`->ifNull('N/A')`	Fallback if null
`->append(' more')`	Append if present
`->prepend('$')`	Prepend if present
`->map($callback)`	Apply arbitrary function to value

Validation & Error Handling

Method	Description
`->isEmpty()`	True if empty ("", null, false, 0, “0”)
`->isNotEmpty()`	True if not empty
`->isMissing()`	True if null or empty string
`->isNull()`	True if null
`->or404()`	Send 404 and exit if value is missing
`->orDie($msg)`	Die with message if missing
`->orThrow($msg)`	Throw RuntimeException if missing
`->orRedirect($url)`	Redirect if missing

echo $row->price->numberFormat(2)->prepend('$');        // "$1,234.56"
echo $row->bio->textOnly()->maxChars(200, '...');       // truncated preview
echo $row->nickname->or('Anonymous');                   // fallback
echo $row->created_at->dateFormat('M j, Y');            // "Sep 10, 2026"

// Validation and error handling
$user = DB::selectOne('users', ['id' => $id])->or404();     // 404 if not found
if ($row->name->isMissing()) { echo "No name"; }

Result Set Methods

Method	Returns
`count($resultSet)`	int - row count
`$rs->first()`	First row, or `SmartNull` if the set is empty (chaining works, but it is not a SmartArrayHtml)
`$rs->last()`	Last row, or `SmartNull` if the set is empty
`$rs->at($index)`	Row by position (0-based, negative counts from end)
`$rs->toArray()`	Array of raw PHP arrays (no encoding)
`$rs->column('col', 'keyCol')`	Flat collection of one column, optionally keyed by another
`$rs->columnAt($index)`	Extract value at position from each row
`$rs->sortBy('col')`	Sorted result set
`$rs->filter(fn)`	Filtered result set
`$rs->where('col', $val)`	Rows where column matches (chain for multiple)
`$rs->map(fn)`	Transformed collection
`$rs->indexBy('col')`	Lookup keyed by column
`$rs->groupBy('col')`	Grouped by column value
`$rs->implode(', ')`	Join values into string
`$rs->or404()`	Send 404 if empty result set
`$rs->orThrow($msg)`	Throw RuntimeException if empty

// build HTML lists with foreach - $user->name HTML-encodes in the string
foreach ($users as $user) {
    echo "<li>$user->name</li>\n";
}

Loop Position Helpers (on rows inside foreach)

Method	Description
`$row->isFirst()`	True if first row in result set
`$row->isLast()`	True if last row in result set
`$row->position()`	1-based position in result set

Row Methods

Method	Returns
`$row->isEmpty()`	bool — true if no data
`$row->toArray()`	Raw associative array
`$row->keys()`	Column names
`$row->values()`	SmartString values

MySQL Metadata

$result->mysqli('query');          // Executed SQL
$result->mysqli('insert_id');      // Auto-increment ID from INSERT
$result->mysqli('affected_rows');  // Rows changed by INSERT/UPDATE/DELETE
$result->mysqli('baseTable');      // Base table name (no prefix)

Helpers

Pagination

DB::pagingSql($page, $perPage = 10)  // Returns RawSql: LIMIT x OFFSET y
// $page defaults to 1 if zero/non-numeric; negative becomes positive (abs)
// $perPage defaults to 10 if zero/non-numeric; negative becomes positive (abs)

LIKE Patterns

All escape % and _ in input. All return RawSql.

DB::select('users', "name LIKE ?", DB::likeContains('John'));    // %John%
DB::select('users', "name LIKE ?", DB::likeStartsWith('J'));     // J%
DB::select('users', "name LIKE ?", DB::likeEndsWith('son'));     // %son
DB::select('users', "tags LIKE ?", DB::likeContainsTSV('featured')); // %\tfeatured\t%

Raw SQL

DB::rawSql('NOW()')                  // Inserted verbatim -- NO escaping

Never pass user input to rawSql(). It bypasses all escaping.

Date/Time Constants

DB::DATETIME  // 'Y-m-d H:i:s' - format for MySQL DATETIME columns
DB::DATE      // 'Y-m-d'       - format for MySQL DATE columns
DB::TIME      // 'H:i:s'       - format for MySQL TIME columns

Table Name Helpers

// with tablePrefix 'cms_':
DB::getFullTable('users')                 // 'cms_users'
DB::getBaseTable('cms_users')             // 'users'

Schema introspection (does a table exist, list tables, column definitions) is in the internal Table class - rarely needed in application code; see src/Table.php if you do. The manual escaping methods (DB::escape(), DB::escapef(), DB::escapeCSV()) are internal too: use placeholders instead.

Connection

Configuration Options

DB::connect([
    'hostname'             => 'localhost',    // Required
    'username'             => 'dbuser',       // Required
    'password'             => 'secret',       // Required (use '' for none)
    'database'             => 'my_app',       // Required (use '' for none)
    'tablePrefix'          => 'cms_',         // Default: ''
    'useSmartJoins'        => true,           // Add table.column keys to JOIN results
    'useSmartStrings'      => true,           // Return SmartString values (auto HTML-encode); false = raw values, no encoding
    'usePhpTimezone'       => true,           // Sync MySQL timezone with PHP
    'versionRequired'      => '5.7.32',       // Minimum MySQL version or compatible
    'requireSSL'           => false,          // Encrypt connection; no cert verification
    'databaseAutoCreate'   => false,          // Create database if missing
    'connectTimeout'       => 3,              // Seconds
    'readTimeout'          => 60,             // Seconds
    'encryptionKey'        => null,           // Encrypt/decrypt MEDIUMBLOB columns (see Encryption)
    'sqlMode'              => 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION',
]);

Connection Management

DB::isConnected()          // true if default connection exists
DB::isConnected(true)      // also pings the server to verify
DB::disconnect()           // Close the default connection

Raw mysqli Access - `DB::$mysqli`

DB::$mysqli is the underlying mysqli connection (a mysqli subclass that adds query logging and lastQuery). Required for DDL: templates reject standalone numbers, so VARCHAR(255) throws in DB::query().

// Raw mysqli: no placeholders, no template guard, no :: prefix expansion
DB::$mysqli->query("ALTER TABLE cms_users ADD COLUMN nickname VARCHAR(64)");

DB::$mysqli->lastQuery;        // last SQL executed, values inlined
DB::$mysqli->insert_id;        // native mysqli properties work as usual
DB::$mysqli->affected_rows;

Returns mysqli_result|true, not ZenDB collections; failures throw mysqli_sql_exception. Results skip auto-decryption (see Encryption).

Connections are always utf8mb4. Never change it. set_charset() throws on any other charset. Raw SQL (SET NAMES, SET CHARACTER SET) is not blocked but defeats escaping and allows SQL injection: mysqli keeps escaping for utf8mb4 while the server switches.

Multiple Connections

use Itools\ZenDB\Connection;

$analytics = new Connection([
    'hostname' => 'localhost',
    'username' => 'dbuser',
    'password' => 'secret',
    'database' => 'analytics',
]);
$rows = $analytics->select('events', "created_at > NOW() - INTERVAL ? DAY", 1);

Each Connection has the same methods as DB:: (select, selectOne, insert, update, delete, count, query, queryOne, transaction).

Encryption (Opt-In)

With encryptionKey set in DB::connect(), every MEDIUMBLOB column is AES-128-ECB encrypted on insert()/update() and decrypted on read; no query changes needed. That reserves MEDIUMBLOB for encrypted data — store plain binary (images, files) in BLOB or LONGBLOB, which are left alone; NULL passes through unencrypted. Booleans throw InvalidArgumentException on encrypted columns — pass a string or number.

// Exact match: encrypt the search value in PHP (encryption is deterministic)
$user = DB::selectOne('users', ['apiToken' => DB::encryptValue($token)]);

// LIKE / ranges / functions need plaintext: {{column}} decrypts in MySQL
$users = DB::select('users', "{{apiToken}} LIKE ?", '%abc%');
// WHERE AES_DECRYPT(`apiToken`, @ek) LIKE '%abc%'

// Raw DB::$mysqli results skip auto-decryption; decrypt in place:
DB::decryptRows($rows, $result->fetch_fields());

DB::encryptValue() produces the same ciphertext as insert()/update(), so it also writes encrypted values through raw SQL. For joins, write the column reference as you would unencrypted, wrapped in braces ({{table.column}}). Inside {{}}, :: applies tablePrefix ({{::users.apiToken}} matches FROM ::users); alias qualifiers stay as written ({{u.apiToken}}).

// Build decrypt expressions for SQL you assemble yourself ({{column}} expands to this)
DB::decryptExpr('apiToken');        // "AES_DECRYPT(`apiToken`, @ek)"
DB::decryptExpr('users.apiToken');  // "AES_DECRYPT(`users`.`apiToken`, @ek)"

// Detect encrypted (MEDIUMBLOB) columns from mysqli field metadata
DB::getEncryptedColumns($result->fetch_fields());  // [fieldIndex => columnName], e.g. [0 => 'token', 3 => 'ssn']

Template Safety Rules

SQL templates are scanned before execution. The following are rejected.

Pattern	Rejected
Quotes (`'` or `"`)	Always — use placeholders
Standalone numbers	Always — use placeholders
Hex/binary/scientific (`0x1F`, `0b101`, `1e10`)	Always — count as numbers
Backslashes (`\`)	Always
NULL bytes (`\x00`)	Always
CTRL-Z (`\x1a`)	Always

The following are allowed in templates.

Pattern	Notes
`''` and `""`	Empty string literals (no injection payload)
Trailing `LIMIT #`	Literal number kept in query, skipped by the template guard

Table and column names that ZenDB inserts — table arguments, WHERE-array and $values keys, and backtick placeholders — are validated against /^[\w-]+$/ (alphanumeric, underscore, hyphen only). Identifiers interpolated into a template by hand are not; the guard has no schema to check them against.

Common Errors Quick Reference

Error	Fix
“Quotes not allowed in template”	Use placeholder: `"name = ?", 'John'`
“Standalone number in template”	Use placeholder: `"age > ?", 21`
“Max 3 positional arguments allowed”	Use named placeholders: `[':a' => 1, ':b' => 2, ...]`
“UPDATE requires a WHERE condition to prevent accidental bulk UPDATE” (same for DELETE)	Add WHERE or use `"TRUE"` for all rows
“Suspicious SET clause”	Check argument order: `update($table, $values, $whereEtc)`
“Missing value for ? parameter at position N”	Pass enough values for all `?` placeholders
“Missing value for ':name' parameter”	Add missing key to params array
“Arrays not allowed with positional ? placeholders”	Use named: `"IN (:ids)"`, `[':ids' => [1,2,3]]`
“Can’t mix positional (?) and named (:param) placeholders”	Use one placeholder style for the whole query
“This method doesn’t support LIMIT or OFFSET”	Use `select()` not `selectOne()` for custom LIMIT
“This method doesn’t support FOR UPDATE” (also FOR SHARE, LOCK IN SHARE MODE)	Use `query(...)->first()`; queryOne()‘s LIMIT 1 must come before locking clauses
“This method appends LIMIT 1 automatically”	Remove the trailing `--`/`#` comment or `;`, or use `query(...)->first()`
“Invalid table name ’…’, allowed characters: a-z, A-Z, 0-9, _, -” (also column name)	Only `a-z, A-Z, 0-9, _, -` allowed

Gotchas

NULL in WHERE: Array form ['col' => null] correctly generates IS NULL. Placeholder form "col = ?", null generates col = NULL which is always false. Use the array form for null comparisons.
String numbers in LIMIT: "LIMIT ?", "10" (string) quotes it. Use "LIMIT ?", 10 (int).
selectOne() auto-adds LIMIT 1. Don’t add your own LIMIT or OFFSET.
count() rejects LIMIT/OFFSET too. Use select() if you need them.
Empty arrays in IN(): [':ids' => []] becomes IN (SELECT 0 FROM (SELECT 0) empty_set WHERE 0), an empty set: IN matches nothing, NOT IN matches everything. Expansion also skips null elements and removes duplicates: [1, null, 1, 2] → IN (1,2).
Boolean values: true/false become SQL TRUE/FALSE keywords.
Param forms: up to 3 direct values for ? placeholders, or one array of :name params. Positional values in one array are deprecated (see Positional ? under Placeholders & Parameters).
Interpolated identifiers are the one injection path. The guard flags quotes and numbers, which every real value carries, so "WHERE city = $city" throws on the first real value. An identifier carries neither, so "ORDER BY $sort" passes the guard and is injection when $sort is user input. Use a backtick placeholder — "ORDER BY `:sort`", [':sort' => $sort] — or map the input to a known-good column in PHP first. A value placeholder won’t work: ORDER BY 'name' sorts by a constant string and silently does nothing.