05 · SQL via Queequeg

Talking to Queequeg from Pequod

Pequod reaches the queequeg SQL engine through four built-ins. Combined with the web chapter, this is the point where a Pequod page becomes a real database-backed application.

The four built-ins

Function	Use
`qq_query($db, $sql)`	Run SQL. `SELECT` → array of rows; write → affected count.
`qq_exec($db, $sql)`	Alias — use when you only want the affected count.
`qq_error()`	The last error message (empty string if the query was fine).
`qq_quote($s)`	Quote a value for safe inclusion in SQL. Your defense against injection.

Under the hood Pequod runs the query against the database files directly (no network) — it sees the same /data the caller does, and uses the same engine as the queequeg server, so results are identical.

SELECT: rows come back as arrays

A SELECT returns an array of rows. Each row is an associative array keyed by column name. Iterate with foreach, index by column.

$rows = qq_query("shop", "SELECT * FROM users");
echo count($rows) . " users\n";
foreach ($rows as $r) {
    echo $r["id"] . ": " . $r["name"] . " (" . $r["age"] . ")\n";
}

Writes: you get the affected count

INSERT, UPDATE and DELETE return the number of rows affected. A CREATE TABLE returns 0.

$n = qq_query("shop", "INSERT INTO users (name, age) VALUES ('ada', 36)");
echo "inserted $n row\n";      // inserted 1 row

Handling errors

On failure a query returns 0 and qq_error() holds the message. Check it after anything that might fail.

$rows = qq_query("shop", "SELECT * FROM ghosts");
if (qq_error() != "") {
    echo "query failed: " . qq_error() . "\n";   // query failed: no such table: ghosts
} else {
    echo count($rows) . " rows\n";
}

Injection safety with `qq_quote`

Never concatenate a raw request value into SQL. A value like x'); DROP TABLE users; -- can break out of your query. Wrap every string value in qq_quote(), and coerce numbers with int().

qq_quote() returns the value already wrapped in quotes with the dangerous characters escaped, exactly matching how Queequeg parses string literals. Drop its result straight into the query:

// unsafe — do NOT do this
$sql = "... WHERE name = '" . $_POST["name"] . "'";

// safe
$sql = "... WHERE name = " . qq_quote($_POST["name"]);

A malicious value then becomes inert data: qq_quote("x'); DROP TABLE users; --") yields the harmless literal 'x\'); DROP TABLE users; --', stored as text.

Full example: a database-backed page

Everything together — a CRUD page served by tintin. GET lists users from SQL; POST inserts one (safely) and re-lists. This is the shape of a real Pequod web app.

default/www/users.psh

<!doctype html>
<html><body><h1>Users</h1>
<? if ($_SERVER["REQUEST_METHOD"] == "POST") {
       $sql = "INSERT INTO users (name, age) VALUES ("
            . qq_quote($_POST["name"]) . ", " . int($_POST["age"]) . ")";
       qq_query("shop", $sql);
       if (qq_error() != "") { ?>
         <p>Error: <?= htmlspecialchars(qq_error()) ?></p>
<?     } else { ?><p>Added <?= htmlspecialchars($_POST["name"]) ?>.</p><? }
   } ?>
<table><tr><th>ID</th><th>Name</th><th>Age</th></tr>
<? foreach (qq_query("shop", "SELECT * FROM users") as $r) { ?>
  <tr><td><?= $r["id"] ?></td><td><?= htmlspecialchars($r["name"]) ?></td><td><?= $r["age"] ?></td></tr>
<? } ?>
</table>
<form method="post">
  <input name="name"> <input name="age"> <button>Add</button>
</form>
</body></html>

Setting up the database

Create the database and table once with the Queequeg tools (or qq_query itself):

bob@pros> qq-cmd createdb shop
bob@pros> qq-query shop "CREATE TABLE users (id INT AUTO, name VARCHAR(20), age INT)"

Supported column types include INT (with AUTO for auto-increment ids), VARCHAR(n), TEXT, CHAR(n), BOOL and DATE. Queries support CREATE, INSERT, SELECT, UPDATE and DELETE with a single WHERE condition.

Toward injection-proof by default. qq_quote is manual discipline — you must remember to call it. Bound parameters (passing values separately from the SQL) are the eventual ergonomic fix; until then, quote every string and int() every number.

Caps. Result capture is bounded by tintin's 8 KiB page limit, and each table file by the 4096-byte store cap — comfortable for demos and small apps, and the same ceilings the rest of PiROS grows past over time.