Connections and Raw SQL

External database connections

Use db:connection to connect to an external database. All operations inside the block run against that connection:

XML

<db:connection server="host" dbname="db" username="user" password="pw">
    <!-- database operations run against this connection -->
</db:connection>

By default, all database operations run against the ZeyOS application database. You only need db:connection for external databases.

Parameterized queries with db:prepare and db:run

db:run executes arbitrary SQL and returns the result set. Always use db:prepare to construct queries with dynamic values — this prevents SQL injection and ensures proper value escaping.

warning

Never concatenate user input directly into SQL strings. Always use db:prepare with parameter binding.

Basic pattern

Every db:run call should use a prepared statement:

XML

<!-- Prepare the query with parameter binding -->
<db:prepare var="sql">
    <param>$userInput</param>
    <db:query>SELECT firstname, lastname FROM contacts WHERE email = ?</db:query>
</db:prepare>

<!-- Execute and retrieve results -->
<db:run var="result" type="assoc">$sql</db:run>

The ? placeholder is replaced by the corresponding <param> value, properly escaped for the database.

Result set formats

The type attribute on db:run controls how results are returned:

Type	Description
assoc	Array of associative arrays (column names as keys)
num	Array of numerically indexed arrays
list	Flat array of values from the first column
value	Single scalar value (first column of first row)
self	Raw result set handle

Value binding vs identifier binding

db:prepare supports two placeholder types:

• ? — Value binding. The parameter is escaped and quoted as a value. Use for data.
• $ — Identifier binding. The parameter is quoted as an identifier. Use for table/column names.

XML

<db:prepare var="sql">
    <param>contacts</param>
    <param>active</param>
    <db:query>SELECT * FROM $ WHERE status = ?</db:query>
</db:prepare>

<db:run var="result" type="assoc">$sql</db:run>

Here, $ is replaced by the identifier contacts (quoted), and ? is replaced by the value active (escaped).

Multiple parameters

Parameters are bound in order of appearance:

XML

<db:prepare var="sql">
    <param>$minAge</param>
    <param>$maxAge</param>
    <param>$status</param>
    <db:query>
        SELECT * FROM contacts
        WHERE age BETWEEN ? AND ?
        AND status = ?
    </db:query>
</db:prepare>

<db:run var="contacts" type="assoc">$sql</db:run>

<foreach var="contacts" var_value="contact">
    <output>$contact.firstname $contact.lastname&#10;</output>
</foreach>

Write operations

db:prepare + db:run also works for INSERT, UPDATE, and DELETE:

XML

<db:prepare var="sql">
    <param>$name</param>
    <param>$email</param>
    <db:query>INSERT INTO contacts (name, email) VALUES (?, ?)</db:query>
</db:prepare>

<db:run>$sql</db:run>

For structured write operations on ZeyOS entities, prefer the object-level db:set command instead, which handles ExtData, tags, and business logic automatically.