SQLite Data Studio: Routinen

Zurück: SQLite Data Studio

Eine Routine ist eine benannte SQL-Arbeitseinheit: ein Manifest mit Parametern und Rückgabeform, dazu die Abfrage selbst oder ein nativer Prozeduraufruf.

[Live]

$routines = plugin_load('helper', 'wksqliteds');
 
// gegen ein vorhandenes PDO:
$affected = $routines->execute($pdo, 'close_inspection', ['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_inspection', ['id' => 123, 'user' => 'wolfgang']);

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

• $connection: \PDO, ein Objekt mit getPdo(), 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.

Ein Manifest liegt als <name>.json im Ordner seiner Art, etwa sql/Functions/close_inspection.json. Die SQL-Datei, die es nennt, liegt im selben Ordner.

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

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

Multi-Backend (per-Treiber-Implementierungen):

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

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

Eine type:sql-Datei darf mehrere Statements enthalten; die Routinen-Engine trennt sie selbst (Regeln unten). 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).

Trennregeln: Getrennt wird an ; – aber nicht innerhalb von Zeichenketten ('…' mit ''-Escape), Kommentaren (-- auch hinter Code, /* … */) und gequoteten Identifiern ("…", Backticks, […]). Ein ; innerhalb eines BEGIN … END- oder CASE … END-Blocks trennt ebenfalls nicht: ein Trigger- oder Prozedurrumpf bleibt ein Statement. Ein nicht geschlossener Blockkommentar bricht mit RoutineException ab, statt den Rest der Datei zu verschlucken.

• 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 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.

// (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).

• 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.

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

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“).

• SQLite Data Studio: Verbindungen – welche Verbindung execute() anspricht
• SQLite Data Studio: Berechtigungen – Fähigkeiten-Gate für Studio-getriggerte Ausführung
• SQLite Data Studio: API-Referenz – vollständiger Helper-Methodenvertrag
• SQLite Data Studio: How-to – Rezept „Routine anlegen“