# KenedoDatabase, the database layer

> A reference for KenedoDatabase — the one class every query in CBX goes through. It is a thin, deliberate wrapper over mysqli: you set a query, you run it, yo…

Source: CBX documentation, version 4.0 preview (unreleased). Canonical page: https://docs.configbox.at/docs/4.0-preview/technical/kenedo_database. Last updated 2026-08-02.

---
A reference for **`KenedoDatabase`** (`external/kenedo/classes/KenedoDatabase.php`, 1235 lines) — the
one class every query in CBX goes through. It is a thin, deliberate wrapper over **mysqli**: you set a
query, you run it, you fetch through a `load*` method. There is no query builder and no ORM at this
level — the "ORM" is the property-driven `KenedoModel` sitting on top (see
`com_configbox_kenedo_model.md`). What this class *does* own is the things that must not vary per
caller: the connection's `sql_mode` and charset, the `#__` prefix replacement, escaping/quoting
helpers, an opt-in prepared statement path, savepoint-emulated nested transactions, and one logged,
thrown failure path for every query that goes wrong. Line references are to `KenedoDatabase.php`
unless another file is named.

---

## 1. Getting the instance

```php
$db = KenedoPlatform::getDb();
```

That is the whole access pattern (`KenedoPlatform.php:65` — it delegates to the active platform
object, which holds **one `KenedoDatabase` per request**). There is no DI and no per-caller
connection; every model, helper and property shares the same instance, which is also what makes the
savepoint stack (§6) coherent — nesting only works because everyone pushes and pops on the same
object. The constructor connects immediately and configures the session (§7).

## 2. The classic path — `setQuery()` + `query()` + `load*()`

The traditional Kenedo way is a **manually built SQL string**:

```php
$db->setQuery("
    SELECT ".$db->getQuoted('id').", ".$db->getQuoted('title')."
    FROM #__configbox_products
    WHERE ".$db->getQuoted('published')." = '".$db->getEscaped($published)."'
");
$products = $db->loadObjectList();
```

`setQuery($query, $start, $limit)` (`:202`) stores the SQL (appending a `LIMIT` clause when
`$start`/`$limit` are given); `query()` (`:286`) replaces **`#__` with the real table prefix**
(`:294`), runs it, and either returns the result or **throws** (§7). The `load*` methods each call
`query()` for you.

### 2.1 The escaping discipline — `getQuoted()` vs `getEscaped()`

The two helpers answer two different questions, and mixing them up is the classic mistake:

- **`getQuoted($text)`** (`:175`) is for **identifiers** — table and column names. It wraps the text
  in backticks and does **nothing else**: no escaping, no validation. It exists so an identifier can
  collide with a reserved word, not so an identifier can carry user input. Never feed it anything
  request-derived.
- **`getEscaped($text)`** (`:171`) is for **values**, and only *inside quotes you write yourself*:
  `"… = '".$db->getEscaped($value)."'"`. It is `mysqli_real_escape_string()` — correct for the
  connection charset, useless without the surrounding quotes.

IDs are conventionally `intval()`-cast at interpolation instead of escaped — cheaper and
self-documenting (`KenedoModel::getRecord()` and friends do exactly this).

### 2.2 The `load*` family

All of them run the current query and fetch; pick by the shape you want back:

| Method | Returns |
|---|---|
| `loadResult()` | the first column of the first row, or `null` |
| `loadResultList($indexField, $valueField)` | a flat array — first column by default, or key/value by the named columns |
| `loadRow()` / `loadRowList($indexField)` | one numeric row / a list of them |
| `loadAssoc()` / `loadAssocList($indexField)` | one assoc row / a list of them |
| `loadObject($class)` / `loadObjectList($indexField, $class)` | one object / a list (default class `KenedoObject`) |
| `getCount()` | the number of rows the query returns |

### 2.3 Writing objects — `insertObject()`, `updateObject()`, `replaceObject()`

`insertObject($table, &$object, $keyName)` (`:928`) builds an `INSERT` from the object's **scalar**
properties (non-scalars are skipped, `NULL` becomes SQL `NULL`) and — unless the primary key is
empty — appends `ON DUPLICATE KEY UPDATE`, which is what lets `KenedoModel::store()` use one call
for insert *and* update. On an insert it **back-fills the new id** into `$object->{$keyName}`.
`updateObject()` (`:1043`) is the plain `UPDATE … WHERE key = …` variant; `replaceObject()` (`:898`)
issues a `REPLACE INTO`; `insertObjects()` (`:983`) is the batched multi-row insert (optionally
chunked via `$batchSize`).

## 3. Prepared statements — `setPreparedQuery()` (`:253`)

Since 2026-08 there is a second, **preferred-for-new-queries** path: real mysqli prepared statements.
The values never touch the SQL string, so there is nothing to escape and no way to forget escaping.
Every `load*` method, `getAffectedRows()` and `insertid()` work unchanged on top.

```php
// Named placeholders (recommended)
$db->setPreparedQuery("
    SELECT * FROM #__configbox_questions
    WHERE product_id = :productId AND published = :published
", array('productId' => $productId, 'published' => 1));
$questions = $db->loadObjectList();

// Positional
$db->setPreparedQuery("UPDATE #__configbox_products SET title = ? WHERE id = ?", array($title, $id));
$db->query();

// IN lists — an array value expands to one placeholder per element
$db->setPreparedQuery("SELECT * FROM #__cbcheckout_orders WHERE id IN (:ids)", array('ids' => $orderIds));
```

The rules (`:214-245`, enforced in `compilePreparedQuery()` `:489`):

- **Either `:named` placeholders (string keys) or `?` placeholders (plain list)** — not both in one
  query.
- Values may be scalars, `NULL`, or **arrays of scalars** (IN lists). An **empty array throws** —
  guard before querying: an empty `IN ()` is not valid SQL, and quietly making it "match nothing"
  would make `NOT IN` mean the opposite of what the caller intended.
- ints bind as ints, floats as doubles, bools as 0/1, everything else as string, `NULL` as SQL NULL.
- Placeholders inside string literals, quoted identifiers and comments are **left alone** — a colon
  in a time literal or a `?` in a `LIKE` string never binds anything.
- **Every named parameter must have a placeholder and every placeholder a parameter** — a typo
  throws instead of silently binding nothing.
- `#__` prefixing and the `$start`/`$limit` clause work exactly as in `setQuery()`. But mind that
  the prefix replacement only sees the SQL string — a **bound value** carrying a `#__` table name is
  not replaced (resolve it yourself, as `getColumnInfo()` does, `:1098-1103`).

Under the hood, `executePrepared()` (`:403`) requires the **mysqlnd** driver
(`mysqli_stmt_get_result()`), captures the statement-level `affected_rows`/`insert_id` before the
statement closes (the connection-level counters do not reliably cover prepared statements — that is
why `getAffectedRows()` `:655` and `insertid()` `:1080` check the stmt-level memo first), and
normalizes the host platforms' differing global mysqli report modes so a failed prepared query
always takes the same logged path as a failed classic one (§7).

**When to use which:** reach for `setPreparedQuery()` in **new** queries — it is the one sanctioned
modernization in Kenedo code. When *editing* existing string-built queries, follow the neighbouring
code's concatenation discipline (§2.1) rather than half-converting a query; a rewrite to the prepared
form is welcome when you are reworking the query anyway, not as a drive-by.

## 4. The `#__` prefix

Every query — classic or prepared — writes table names as `#__configbox_products`; `query()` replaces
`#__` with the install's real prefix (`:294`), which comes from the platform's connection data. Never
hardcode a prefix, and remember §3's caveat: replacement happens on the SQL string only, not on bound
values.

## 5. What the connection guarantees (`createDatabaseLink()`, `:61`)

The constructor sets session state **explicitly**, so ConfigBox behaves the same on every host
regardless of how the server's globals are tuned (`:95-110`):

- **Strict `sql_mode`** — the MySQL 8.0 defaults (`STRICT_TRANS_TABLES`, `ONLY_FULL_GROUP_BY`, …).
  This used to be `sql_mode = ''`, lax mode everywhere, which quietly converted real write errors
  into silent implicit defaults: an INSERT omitting a NOT NULL column stored `''` with a warning
  instead of refusing. Every write path is expected to satisfy strict mode now; the
  platform-filtered-property default fill in `KenedoModel::store()` covers the one case that could
  not (see `com_configbox_kenedo_model.md` §5).
- **`utf8mb4`**, not MySQL's three-byte "utf8" — the tables have been utf8mb4 for years; the
  three-byte connection was the only thing rejecting four-byte characters (emoji, plenty of CJK),
  and it did so as a hard "Incorrect string value" on write.
- **`REPEATABLE-READ`** isolation, `group_concat_max_len = 20000`.
- A customization hook: `system_overrides/getInitQueries.php` may define
  `getPostDbConnectQueries()` returning extra session queries (`:112-127`).

## 6. Transactions — savepoint-emulated nesting (`:1141-1232`)

MySQL has no nested transactions; Kenedo needs them (`copy()` recurses, `store()` runs inside the
controller's `copy()` transaction). The emulation is a **stack of savepoint ids**:

- `startTransaction()` (`:1160`) — issues `START TRANSACTION` when the stack is empty, else creates
  a `SAVEPOINT`, and **pushes** a level.
- `commitTransaction()` (`:1181`) — issues `COMMIT` at depth one, else `RELEASE SAVEPOINT`, and
  **pops** — but only once the statement has run. It used to pop first, so a `COMMIT` that threw
  left the level off the stack while its caller still believed it open, and that caller's catch
  block then rolled back its *parent's* savepoint.
- `rollbackTransaction()` (`:1208`) — `ROLLBACK` at depth one, else `ROLLBACK TO SAVEPOINT`, and
  pops the same way. (Rolling back to a savepoint does not release it in MySQL, but the stack models
  *nesting*, not MySQL's savepoint list: one push per start, one pop per commit-or-rollback.)

The rule that follows, and that `KenedoModel::store()`/`copy()` spell out in their comments:
**exactly one rollback per level, in the catch block, never both**. A level that rolls back inside
its try and then again in its catch pops **two** savepoints for one nesting level — every enclosing
rollback then pops the wrong one, and the outermost throws "no transaction was started", replacing
the real failure with a spurious one. Corollary: catch `Throwable`, not `Exception`, around a
transaction — a `TypeError` sailing past an `Exception`-only catch leaves the level's savepoint on
the stack with the same off-by-one result.

## 7. Error handling and logging

**A failed query throws — always.** Both the classic and the prepared path route through
`handleQueryFailure()` (`:339`), which walks the backtrace to name the **calling** class/method/file
(not this class), logs the full detail — caller, error number, error message, the SQL as sent, and
for prepared queries the bound parameters — to the **`db_error` log**, appends to the in-memory
failed-query log (`getFailedQueryCount()`/`getFailedQueryLog()`), and throws an `Exception` whose
**code is the MySQL error number**. That code is load-bearing: `KenedoModel::delete()` catches code
`1451` (FK violation) and turns it into the operator-readable "linked with other records" refusal.
Under strict mode (§5), writes that lax mode used to wave through — omitted NOT NULL columns (1364),
out-of-range values — surface here as real, logged failures; that is the intended behaviour, not a
regression. The prepared path additionally handles hosts where the global mysqli report mode is
PHP's strict default (throwing `mysqli_sql_exception` instead of returning `false` — Magento and
standalone) so failures land in the same logged path on every platform (`:414-418`).

Query metrics ride along: `getQueryCount()`, and — when `CONFIGBOX_ENABLE_PERFORMANCE_TRACKING` is
defined — a per-caller timing list via `getQueryList()`/`getTotalQueryTime()` (`:666`).

## 8. Gotchas

- **`getQuoted()` does not escape.** It backticks an identifier, verbatim. Anything request-derived
  in an identifier position is an injection waiting to happen — identifiers must come from code or
  developer-authored metadata only.
- **`getEscaped()` needs your quotes.** It escapes the value; the surrounding `'…'` is your job.
- **An empty IN-list array throws** on the prepared path — deliberately. Guard the query with a
  `count()` check instead of expecting an empty `IN ()` to "match nothing".
- **Don't mix `:named` and `?`** in one prepared query; don't expect `#__` replacement inside bound
  values.
- **One rollback per level.** Rolling back in both the try and the catch pops two savepoints and
  corrupts the stack for every enclosing level (§6). And catch `Throwable`, so an `Error` cannot
  skip the rollback.
- **`insertObject()` is an upsert** (`ON DUPLICATE KEY UPDATE`) whenever the key field is non-empty —
  which is exactly what `storeExternally` properties rely on, and why their foreign key must carry a
  UNIQUE index (see `com_configbox_property_types.md` §3).
- **Prepared statements need mysqlnd.** On a host without `mysqli_stmt_get_result()` the prepared
  path throws with a message saying to use the classic path; virtually every modern PHP build has it.
- **`getAffectedRows()`/`insertid()` after a prepared query** come from the statement-level capture,
  not the connection — that is handled for you, but only through this class; do not reach for
  `mysqli_insert_id()` on the raw link.

## See also

- `technical/com_configbox_kenedo_model.md` — the main consumer: the property-driven SQL assembly
  (§4), the SQL-building discipline in context (§4.3), and `store()`/`copy()`/`delete()` as the
  transaction-nesting case studies (§5, §8).
- `technical/com_configbox_kenedo_mvc.md` — where the database layer sits in the framework (§8:
  the string-built-SQL caveat and the modernization anchors around `InterfaceKenedoDatabase`).
- `technical/com_configbox_migrations.md` — the update scripts run through this same class, with
  idempotency guards instead of transactions (migration scripts get **no** transaction wrapper).
- Key source: `external/kenedo/classes/KenedoDatabase.php` (`createDatabaseLink` `:61`, `getEscaped`/
  `getQuoted` `:171/:175`, `setQuery` `:202`, `setPreparedQuery` `:253`, `query` `:286`,
  `handleQueryFailure` `:339`, `executePrepared` `:403`, `compilePreparedQuery` `:489`,
  `insertObject` `:928`, transactions `:1141-1232`), `external/kenedo/classes/KenedoPlatform.php`
  (`getDb` `:65`).