SQLite Data Studio: Routinen

[Live]

Begriffe

Begriff	Bedeutung
Routine	Benannte SQL-Arbeitseinheit: ein Manifest (`.json`) + eine/mehrere SQL-Dateien (`.sql`) oder ein nativer Prozeduraufruf.
Routine-Set	Verzeichnis mit Routinen. Das Set `default` ist der `sql/`-Ordner des Plugins; Consumer-Plugins registrieren eigene Sets (siehe API-Referenz). Adressierung: `name` (Default-Set) oder `set:name`.
Manifest	JSON mit `parameters`, `returns` und einer/mehreren per-Backend-`implementations`.
Backend	Engine-spezifisches Verhalten (Identifier-Quoting, Savepoints, `lastInsertId`, nativer Aufrufdialekt), gewählt aus dem PDO-Treiber der Verbindung.

Schnellstart

$routines = plugin_load('helper', 'wksqliteds');
 
// gegen ein vorhandenes PDO / SQLiteDB:
$affected = $routines->execute($pdo, 'close_pruefung', ['id' => 123, 'user' => 'wolfgang']);
 
// gegen eine benannte Verbindung:
$rows = $routines->execute('reporting', 'reports:monthly', ['month' => '2026-07']);
 
// über den wkcore-Service:
$svc = ServiceHub::fromDokuWiki()->service('sql.routines');
$svc?->execute($pdo, 'close_pruefung', ['id' => 123, 'user' => 'wolfgang']);

execute($connection, $routine, $params):

$connection: \PDO, etwas mit getPdo() (z. B. SQLiteDB), ein Verbindungs-Name, oder null (nutzt die Option default_connection).
$routine: „name“ (Default-Set) oder „set:name“; ein angehängtes .json wird toleriert.
$params: ['name' => wert, ...].
Rückgabe gemäß returns des Manifests, sonst wird eine RoutineException geworfen.

Manifest

Flach (eine Implementierung — ursprüngliche Spezifikation, weiterhin unterstützt):

{
  "name": "close_pruefung",
  "type": "nonquery",
  "file": "procedures/close_pruefung.sql",
  "transaction": true,
  "parameters": { "id": "integer", "user": "string" },
  "returns": "rows_affected"
}

Multi-Backend (per-Treiber-Implementierungen):

{
  "name": "close_pruefung",
  "parameters": { "id": "integer", "user": "string" },
  "returns": "rows_affected",
  "implementations": {
    "default": { "type": "sql",       "file": "procedures/close_pruefung.sql", "transaction": true },
    "mysql":   { "type": "procedure", "call": "close_pruefung",                 "transaction": true },
    "pgsql":   { "type": "sql",       "file": "procedures/close_pruefung.pgsql.sql", "transaction": true }
  }
}

Feld	Werte
`name`	`[A-Za-z0-9_-]+`; muss dem Dateinamen entsprechen.
`parameters`	Map `name` → Typ: `integer`, `string`, `boolean`, `float`. Suffix `[]` = IN-Liste (Array → `(?, ?, …)`); Suffix `?` = nullable.
`returns`	`rows_affected` \| `insert_id` \| `rows` \| `row` \| `scalar` \| `none`.
`type`	`sql` (Datei-Statements) oder `procedure` (nativer Aufruf). Alt-Wörter `nonquery`/`query` → `sql`; `proc`/`call`/`function` → `procedure`. Die Ergebnisform steuert `returns`, nicht `type`.
`file`	(sql) relativer Pfad im Set, Endung `.sql`, kein `..`.
`call`	(procedure) `[A-Za-z_][A-Za-z0-9_.]*`.
`transaction`	bool (Default `true`).

Auflösung: der treiberspezifische Schlüssel gewinnt, sonst default.

SQL-Dateien

Eine type:sql-Datei darf mehrere Statements enthalten (Trennung durch den quote-/kommentar-bewussten Splitter des DokuWiki-sqlite-Plugins). Jeder :name-Platzhalter wird in ein positionelles ? umgeschrieben und gebunden (nie konkateniert) — einheitlich für alle Backends inkl. ODBC/Access. Ein Statement bindet nur die tatsächlich vorkommenden Platzhalter (kein PDO-HY093).

Splitter-Grenzen: Trennung an ;; erkannt werden einfache Strings ('…', ''-Escape) und Zeilenanfang---. Nicht unterstützt: /* … */, Inline--- nach Code, sowie ; innerhalb doppelt-/backtick-/eckig-gequoteter Identifier.

Dynamisches SQL

IN-Listen: Parameter als integer[]/string[] deklarieren, Array übergeben → (?, ?, …). Leeres Array → (NULL). Im SQL-Text steht der Platzhalter ohne umschließende Klammern (WHERE id IN :ids, nicht IN (:ids) — sonst entsteht nach der Listen-Expansion das ungültige IN ((?, ?))).
Dynamische Identifier / Inline-PHP: Eine Routinen-Datei darf PHP enthalten; im Scope stehen $params und $ctx. $ctx->ident('spalte') liefert einen validierten, engine-gequoteten Identifier (einziger sicherer Weg für dynamische Tabellen-/Spaltennamen). Werte bleiben stets :name-Platzhalter.

Inline-PHP: Bedrohungsmodell

Inline-PHP läuft mit voller Anwendungsberechtigung (eval, wie bei wksnippet). Daher mehrfach abgesichert:

Benötigt sowohl die Plugin-Option allowPhp als auch die globale Option $conf['phpok'] (Doppel-Gate). Default: aus. Enthält eine Datei PHP bei geschlossenem Gate, schlägt die Ausführung fail-closed fehl (RoutineException).
Seit dem Berechtigungsmodell Stufe 2 (Details): selbst wenn beide Config-Gates offen sind, dürfen nur echte Superuser eine Routinen-SQL-Datei mit PHP-Inhalt speichern (design-Fähigkeit allein reicht nicht) oder über die Studio-Oberfläche interaktiv auslösen (use-Fähigkeit allein reicht nicht) — unabhängig davon, was die Fähigkeiten-Matrix sonst erlaubt. Grund: ohne diese zusätzliche Bindung könnte ein Nicht-Superuser mit design-Freigabe PHP-Code hinterlegen, der später mit vollen Anwendungsrechten läuft (durch sich selbst, durch einen Superuser oder durch den sql.routines-Servicepfad). Der sql.routines-Servicepfad selbst (programmatische Aufrufer wie wkblog) bleibt von dieser zusätzlichen Bindung unberührt — er vertraut der bereits gespeicherten Routinen-Datei, genau wie vor Stufe 2.

Routine-Sets aus einem anderen Plugin registrieren

// (a) per Event
$controller->register_hook('WKSQLITEDS_REGISTER_ROUTINE_SETS', 'BEFORE', $this, 'sets');
public function sets(\dokuwiki\Extension\Event $event) {
    $event->data->register('reports', __DIR__ . '/sql');
}
 
// (b) programmatisch
plugin_load('helper', 'wksqliteds')->registerSet('reports', __DIR__ . '/sql');

Adressierung dann als reports:monthly. Vollständiger Methodenvertrag: API-Referenz (inkl. des Legacy-Event-Shims aus der Rename-Migration).

Konfiguration

Option	Default	Bedeutung
`allowPhp`	`0`	Inline-PHP in Routinen erlauben (zusätzlich globales `phpok` nötig; seit Stufe 2 zusätzlich strukturell an Superuser gebunden).
`default_connection`	''''	Verbindung, wenn `execute()` keine bekommt.
`slow_ms`	`200`	Routinen protokollieren, die länger dauern (ms); `0` = aus.

Sicherheit

CSRF: jeder Admin-Schreibvorgang via checkSecurityToken().
Autorisierung: Ausführung über einen Web-Zugriff (Studio-Oberfläche) folgt der Berechtigungs-Matrix; kein direkter Web-Endpunkt führt eine Routine allein aus Request-Eingaben ohne Gate aus.
Path Traversal (CWE-22): Namen allowlisted; file per realpath im Set-Verzeichnis eingeschlossen, Endung .sql.
SQL-Injection (CWE-89): Werte immer gebunden; Identifier nur via $ctx→ident(); SQL nie aus Werten zusammengesetzt.
Credentials: nie im Klartext im JSON; nicht in Fehlermeldungen — Details: Verbindungen.
XSS (CWE-79): alle Admin-Ausgaben durch hsc().
Transaktionen: transaction:true atomar (begin/commit bzw. SAVEPOINT bei Schachtelung), Rollback bei Fehler.

Backend-Hinweise

Backend	Treiber	Identifier	Savepoints	Prozeduraufruf	insert_id
SQLite	`sqlite`	`„x“`	ja	— (nur SQL)	`lastInsertId()`
MySQL/MariaDB	`mysql`	`x`	ja	`CALL p(…)`	`LAST_INSERT_ID()`
PostgreSQL	`pgsql`	`„x“`	ja	`SELECT * FROM p(…)`	`lastval()` — besser `RETURNING` + `returns:scalar`
SQL Server	`sqlsrv`	`[x]`	`SAVE TRANSACTION`	`EXEC p …`	`SCOPE_IDENTITY()`
MS Access	`odbc`	`[x]`	keine (an äußere TX, geloggt)	`{CALL p(…)}`	`SELECT @@IDENTITY`

MS Access benötigt den ACE/Jet-ODBC-Treiber „Microsoft Access Driver (*.mdb, *.accdb)„ und pdo_odbc (Windows).

Häufige Fehler

Symptom: PDOException „HY093“ bei einem Multi-Statement-Manifest.

Ursache: ein Statement bindet einen Platzhalter, der in einem anderen Statement der Datei deklariert, aber in diesem konkreten Statement nicht verwendet wird.

Lösung: entfällt in der Praxis — der Executor bindet je Statement nur die dort tatsächlich vorkommenden Platzhalter. Tritt der Fehler dennoch auf, liegt meist ein Tippfehler im Platzhalternamen vor (Groß-/Kleinschreibung muss übereinstimmen).

Symptom: IN-Filter mit Listenparameter liefert ein SQL-Syntaxfehler.

Ursache: der Platzhalter steht mit eigenen Klammern im SQL-Text (IN (:ids)); nach der automatischen Listen-Expansion entsteht das ungültige IN ((?, ?)).

Lösung: Klammern im SQL-Text weglassen — IN :ids (siehe Abschnitt „Dynamisches SQL“).