v. vanilla 2.0
THE VANILLA HANDBOOK / 2.0

MySQL, MariaDB & SQL Server

Parameterized queries, transactions and private connection profiles.

This reference describes the implemented native runtime. Historical paths and internal version labels are retained where relevant. Later extension chapters supersede older limitations.

API and compatibility

The existing SQLite db API is unchanged, including its read-only db query, authorizer, statement savepoints and budgets. Remote SQL is a separate sql namespace; opening a SQLite path never selects a remote driver.

sql open $handle mysql|mariadb|mssql profile-name
sql query $rows $handle "SQL with ? placeholders" $params
sql exec $affected $handle "SQL with ? placeholders" $params
sql begin $handle
sql commit $handle
sql rollback $handle
sql close $handle

Run with --allow-db --allow-network --sql-config /private/profiles.json. All three are required for remote connections. No --allow-files is needed to read the operator-designated profile. These gates are not a sandbox. SQL uses its own driver networking, not the socket DNS/TLS implementation or flags.

$params is a JSON array of null, boolean, integer, floating-point or string values. Binding is native (mysql_stmt_bind_param / SQLBindParameter), never SQL string substitution. Strings preserve explicit byte lengths, including NUL; SQL text cannot contain NUL. Use parameters for values, not table identifiers.

query returns an array of objects with unique column aliases. Integers must fit the interpreter's exact ±9007199254740991 range; real types become JSON numbers, null remains null, and text/date/time/decimal types become strings. DECIMAL is deliberately text to preserve precision. BLOB/binary results are unsupported. SQL Server input strings bind as Unicode SQL parameters; target columns must also support Unicode (e.g. NVARCHAR) for a Unicode roundtrip. exec returns the driver's affected-row count; SQL Server may return -1 when no count is available. MySQL counts changed rather than matched update rows.

Unlike SQLite db query, remote sql query is NOT a read-only guarantee. It checks the result shape; especially with ODBC, execution precedes metadata validation. A bad shape, a function, trigger or batch can have side effects before rejection. Use a database principal with SELECT-only grants when you need read-only access. Never give scripts production root/sa credentials.

Transaction commands reject nested begin and commit/rollback without a begin. Close/destruction rolls back a pending ordinary transaction. Errors during a statement/transaction operation invalidate the handle and all copied aliases; reconnect explicitly. Driver diagnostics report numeric codes / SQLSTATE, not server text that could echo SQL, data or passwords. SQLSTATE warnings other than the expected SQL Server connection database/language notices are errors.

An error is not evidence that a write did not happen. Autocommit writes, DDL implicit commits, triggers, external effects and connection loss during commit can be irreversible or have an unknown outcome. There is no automatic retry and no cross-driver per-statement savepoint promise. MySQL/MariaDB DDL can commit an active transaction even though the interpreter still requires an explicit transaction-ending command. Use transactional tables (InnoDB) and DML-only transactions for rollback-sensitive work.

Comments, semicolons outside quotes, backslash escapes in SQL quoted literals, and explicit transaction/control SQL are conservatively rejected. This is a lexical restriction, not a complete SQL parser or security boundary. SQL Server can accept multiple commands without semicolons; additional result sets are rejected only after execution. Stored-procedure APIs, output parameters, bulk loading and multiple result sets are not provided.

Credentials and TLS

A profile file is a JSON object keyed by operator-defined profile names. It must be a regular file owned by the effective UID, without group/other mode bits, at most 64 KiB; final-component symlinks are rejected using O_NOFOLLOW. Parent path components are not confined. Keep parent directories private. Do not put credentials in scripts, command arguments, committed examples or logs. The file is read on each open; credentials necessarily exist in process and driver memory and may be readable by root. This is not secret zeroization. Avoid enabling ODBC tracing, which can log connection strings.

Non-secret schema illustration (replace placeholder values privately):

{
  "reporting": {
    "driver": "mysql",
    "host": "db.example.internal",
    "port": 3306,
    "database": "reporting",
    "user": "report_reader",
    "password": "<supply privately>",
    "tls": "verify",
    "ca": "/private/database-ca.pem",
    "timeout": 5
  }
}
  • driver: exactly mysql, mariadb or mssql, and must match sql open.
  • port: integer 1..65535; no implicit socket/DSN fallback.
  • timeout: integer 1..30 seconds, default 2.
  • tls: defaults to verify. Native MySQL/MariaDB require the explicit ca path, certificate verification and an actual negotiated TLS cipher. MSSQL uses ODBC Driver 18, Encrypt=yes and TrustServerCertificate=no, with system trust. MySQL option files and ODBC DSN names are not read by this API.
  • insecure-loopback: explicit disposable-test exception, allowed only with literal 127.0.0.1 or ::1, never DNS names or remote IPs. Native drivers may connect without verified encryption; MSSQL still encrypts but does not authenticate the server certificate. Never use it for production traffic.

The shipped integration tests exercise loopback and verify fail-closed TLS rejection; they do not prove successful custom-CA verification for all three servers. No production server or credentials were used.

Limits and timing

Per interpreter: 8 simultaneous remote handles, 1024 statement/transaction calls, 16 MiB cumulative retained result accounting. Per statement: 64 KiB SQL, 256 parameters, 128 columns, 1024 rows, 64 KiB per cell, 1 MiB accounted result, and the normal JSON node/value/frame limits. Column names are unique, nonempty and at most 256 bytes. Results are fetched incrementally, not by a whole-result store_result; a temporary row and driver buffers can exceed the retained result budget. Driver-internal allocations and server memory are not controlled by the interpreter. Ordinary instruction budgets still apply between calls, not to server CPU execution.

MariaDB Connector/C sets connect, per-network-read and per-network-write timeouts and disables automatic reconnect and LOCAL INFILE. ODBC sets login and statement query timeouts. These are driver controls, not absolute wall-clock deadlines: DNS, library work, transaction completion, cleanup, streaming packets and cancellation can exceed them. A timed-out write may still finish on the server. Run the interpreter under a process supervisor when a hard elapsed-time cutoff is required. No promise of deterministic cancellation or a global SQL wall-time budget is made.

Build and actual testing

Additional build dependencies: libmariadb-dev unixodbc-dev. At runtime MSSQL requires Microsoft's ODBC Driver 18 for SQL Server (msodbcsql18) installed from its official repository, with its license accepted by the operator. No mysql/sqlcmd subprocesses are used by the interpreter.

make release sanitize
# Provisioning is opt-in; Docker creates only disposable test containers.
python3 tests/provision_remote_sql.py
python3 tests/test_remote_sql.py ./build/vanilla1
ASAN_OPTIONS=detect_leaks=1:halt_on_error=1 UBSAN_OPTIONS=halt_on_error=1 \
  python3 tests/test_remote_sql.py ./build/vanilla1-asan
make test
make test-sanitize

Provisioning uses unique vanilla1-sql-* names, loopback ephemeral host ports, random mode-0600 env/profile files and nonproduction SQL Server Developer edition. Image IDs, container IDs and ports are recorded without secrets in build/remote-sql/servers.json. Initial server setup on slow disks can take minutes; do not interpret a running Docker container as SQL readiness. Normal test profiles allow 15 seconds for I/O/DDL; timeout tests use separate one-second profiles. Harness processes also have 25-second limits.

Evidence: build/remote-sql/integration-{release,asan}.json, raw integration logs, regression-{release,asan}.log, build logs, and the final verification.json manifest. Earlier failed logs are retained, including startup-not-ready failures and driver conversion/notice fixes. The final suite checks real persistence from a separate interpreter process, parameter injection, typed values, Unicode/NUL/empty strings, transactions, rollback on close/error, stale handles, permissions, redaction, TLS refusal, limits and real server-side delay timeouts. ASan/UBSan instrument the interpreter, not prebuilt vendor libraries.

examples/remote_sql.v runs without credentials in its source; supply the driver and profile name as its two script arguments. It only selects a bound value. To remove only the recorded disposable servers, run python3 tests/cleanup_remote_sql.py build/remote-sql/servers.json. Provisioning refuses to overwrite an existing manifest. For a fresh rerun without deleting evidence, pass a new output directory to tests/provision_remote_sql.py, then pass that directory's profiles.json as the second argument to tests/test_remote_sql.py. The provisioner probes actual interpreter connections for up to five minutes after startup.

Authoritative references consulted

  • MariaDB Connector/C mysql_optionsv: https://mariadb.com/docs/connectors/mariadb-connector-c/api-functions/mysql_optionsv
  • Native prepared statements: https://mariadb.com/docs/connectors/mariadb-connector-c/api-prepared-statement-functions/mysql_stmt_prepare
  • Microsoft ODBC Linux Unicode/binding guidance: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/programming-guidelines
  • ODBC statement attributes: https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlsetstmtattr-function
  • SQL Server query-timeout semantics: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-query-timeouts
  • Official SQL Server container deployment: https://learn.microsoft.com/en-us/sql/linux/install-upgrade/quickstart-install-docker
  • Nonproduction edition/EULA configuration: https://learn.microsoft.com/en-us/sql/linux/configure/environment-variables