Security¶

This page describes the trust model, security boundaries, and known limitations of execsql. Read it before deploying execsql in environments where scripts consume external input, run as a service account, or handle sensitive credentials.

Trust Model¶

execsql treats the script author as fully trusted. Scripts run with the same OS and database privileges as the user who invoked execsql. There is no sandboxing, privilege separation, or policy enforcement of any kind. A script can read and write files, execute OS commands, connect to databases, and send email — subject only to the permissions of the running user.

Do not run scripts from untrusted sources.

SYSTEM_CMD Command Execution¶

The SYSTEM_CMD metacommand executes arbitrary OS commands via Python's subprocess. No allowlist, blocklist, or command restriction exists.

Variable substitution is applied to the command string before execution, so any substitution variable (!!VAR!!) embedded in the command is expanded first. The subprocess is invoked using a list of arguments (not shell=True), which mitigates classic shell injection attacks — an attacker cannot inject shell operators like ;, |, or && through variable values alone. However, argument injection is still possible if a variable contains untrusted data and the target program interprets certain argument patterns as flags or paths.

-- !x! sub outdir /safe/export/path
-- !x! SYSTEM_CMD (pg_dump -Fc mydb -f !!outdir!!/backup.dump)

If outdir is derived from user input, validate or sanitize it before use in a SYSTEM_CMD command.

Disabling SYSTEM_CMD¶

To prevent scripts from executing OS commands entirely, use the --no-system-cmd CLI flag:

execsql --no-system-cmd script.sql mydb

Any script that uses SYSTEM_CMD will fail with an error. This is useful for CI pipelines, shared execution environments, or running semi-trusted scripts where shell access is not appropriate.

The same restriction can be set permanently in execsql.conf:

[config]
allow_system_cmd = No

The library API provides the same control:

import execsql
result = execsql.run("script.sql", dsn="sqlite:///my.db", allow_system_cmd=False)

The --no-system-cmd CLI flag always takes precedence — if the flag is passed, SYSTEM_CMD is disabled regardless of the config file setting.

Credential Handling¶

Interactive password prompts¶

When execsql needs a database password and none is stored or configured, it prompts interactively. In terminal mode, the prompt uses getpass (input is not echoed). In GUI mode, a dialog with a hidden-text entry field is used. Passwords entered interactively are held in process memory only and are not written to disk by execsql.

OS credential store (keyring)¶

When the optional keyring package is installed (pip install execsql2[auth]), execsql checks the OS credential store before prompting. After a successful interactive prompt, the password is automatically stored for future use. Keyring service names follow the pattern:

execsql/<db_type>/<server>/<database>

To disable keyring integration, set use_keyring = No in the [connect] section of execsql.conf.

macOS, Windows, and desktop Linux (with GNOME Keyring or KWallet running) use the OS-native credential store automatically. On headless Linux, install execsql2[auth-encrypted] for an encrypted on-disk backend (master password prompted once per session) or execsql2[auth-plaintext] for a no-prompt plaintext backend (see Usage Notes for backend selection and setup).

enc_password in execsql.conf¶

Use OS credential stores or environment variables for meaningful credential protection. Storing passwords in configuration files — whether as password (plaintext) or enc_password (obfuscated) — provides no meaningful security if the configuration file is readable by an attacker.

[email]
# Avoid this in production:
password = <plaintext password here>
enc_password = <base64 obfuscated value here>

Environment variables and log redaction¶

execsql seeds every readable environment variable into the substitution-variable pool as &NAME so SQL scripts can reference them. To keep obvious secrets out of the pool — and out of ~/execsql.log when -l / --user-logfile is enabled — env-var names whose uppercased form contains any of the following substrings are skipped:

SECRET   TOKEN   PASSWORD   PASSWD   PRIVATE_KEY   CREDENTIAL
_KEY     APIKEY  API_KEY    DSN      WEBHOOK

This catches mainstream cloud, payment, observability, and VCS conventions: AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, OPENAI_API_KEY, ANTHROPIC_API_KEY, STRIPE_KEY, SENTRY_DSN, SLACK_WEBHOOK, etc.

Known gaps. Substring matching is a denylist; it cannot catch every convention:

• GITHUB_PAT and other …_PAT suffixes are NOT filtered (the substring PAT would also match PATH, _PATCH, NODE_PATH, etc., breaking legitimate substitutions). Use GITHUB_TOKEN instead.
• DATABASE_URL and other URL-encoded DSNs (postgresql://user:pass@host/db) are NOT filtered. Use *_DSN or *_SECRET naming when the URL contains credentials.
• Any custom secret name that doesn't match a listed substring (e.g. MY_MAGIC_VALUE) passes through. Rename it or wrap your script in a process that strips it from the environment before invoking execsql.

-a (positional $ARG_n) assignments are always redacted to *** in the log — $ARG_n is a positional name with no naming convention to denylist, and the value is opaque user input that may contain any high-entropy secret. The value is still passed to the substitution machinery normally; only the log line hides it.

File System Access¶

By default, execsql can read and write any file that the process user has permission to access. Configure containment roots for shared or semi-trusted environments; otherwise path arguments may resolve outside the current directory.

The INCLUDE metacommand executes a script from any accessible path unless include_root is set. If the included path is constructed from a variable, an attacker who controls that variable can cause execsql to execute an arbitrary script file.

-- Risky: included path derived from a variable
-- !x! sub script_path !!&USER_INPUT!!
-- !x! INCLUDE !!script_path!!

Validate any variable used to construct file paths before use in file-related metacommands.

Path containment roots¶

Containment roots are opt-in. When configured, paths must resolve under the configured root; attempts to escape with ../, absolute paths, drive letters, or UNC paths are rejected.

These roots do not contain every filesystem operation. In particular, EXPORT and WRITE output paths are governed by their output-directory behavior, and remain writable anywhere the process user can write unless separately constrained by how you invoke execsql.

Email (SMTP)¶

TLS is off by default. The relevant execsql.conf settings are:

[email]
host = mail.example.com
port = 587
use_tls = yes   # STARTTLS after initial plaintext connection
use_ssl = yes   # implicit TLS from connection start (preferred)

When use_tls = yes is set, execsql calls STARTTLS but does not explicitly verify the server certificate (Python's default smtplib behavior). For production deployments:

• Prefer use_ssl = yes (implicit TLS, port 465) over use_tls = yes (STARTTLS, port 587).
• Ensure your SMTP server is configured to reject unauthenticated relay.
• Do not store SMTP passwords in execsql.conf unless the file has strict filesystem permissions.

SQL and Variable Substitution¶

execsql substitution variables (!!VAR!!) insert values directly into SQL text with no escaping or parameterization. This is by design — execsql is a scripting tool for trusted authors, not an application framework that handles end-user input.

-- !x! sub target_schema public
SELECT * FROM !!target_schema!!.mytable;
-- Expands to: SELECT * FROM public.mytable;

If a variable value is derived from external input (command-line arguments, environment variables, PROMPT responses, or data read from a file), the script author is responsible for quoting or validating that value before it is substituted into SQL.

The !'!varname!'! dereferencing form wraps the value in single quotes and doubles any embedded apostrophes, which helps when inserting text values into SQL literals:

-- !x! sub author_name !!&AUTHOR!!
SELECT * FROM documents WHERE author = !'!author_name!'!;

This does not constitute full SQL escaping. For untrusted string input, validate the value before substitution.

Recommendations¶

1. Use a dedicated service account. Run execsql under an account with only the database roles and filesystem permissions needed for the specific script. Do not run as a database superuser or as root.

2. Do not run untrusted scripts. execsql provides no sandbox. Any script you run has the same capabilities as your user account.

3. Use OS credential stores instead of config-file passwords. Install execsql2[auth] and rely on the keyring integration rather than storing passwords in execsql.conf.

4. Enable TLS for SMTP. Set use_ssl = yes in the [email] section, or at minimum use_tls = yes. Do not send credentials over an unencrypted SMTP connection.

5. Audit scripts that use SYSTEM_CMD or INCLUDE with variable-derived paths. Trace where each variable originates. If any value comes from outside the script (environment, prompt, file content), validate it before use in a command that touches the filesystem or executes a process.

6. Restrict access to execsql.conf. Configuration files may contain usernames, SMTP settings, and obfuscated passwords. Ensure file permissions prevent reads by other users (chmod 600 execsql.conf on Linux/macOS).