Parameters, Safe Values, and the CLI .parameter Facility

Learning outcomes

SQL text describes an operation; user or application values are data supplied to that operation. Combining the two by string concatenation is fragile and can become dangerous. This lesson introduces SQL parameters through the CLI's .parameter facility, while keeping a clear boundary: production applications must use their driver's real binding API.

The fragile approach: build SQL text out of values

Imagine searching maintenance notes for a technician-entered phrase. A naive script might construct a statement by inserting the phrase between quote characters. That appears to work until the value itself contains an apostrophe, a newline, a delimiter, or SQL-like text. The fundamental mistake is that the value has been made part of the SQL source code.

# Conceptual anti-patternvalue="O'Brien"sql="SELECT note_id, note_text FROM maintenance_note WHERE note_text = '$value';"# The generated SQL now contains quote characters whose meaning depends on the value.

Manual escaping can patch one symptom, but it forces every caller to reproduce SQL literal rules perfectly. Binding solves the problem at the correct layer by keeping statement structure and value representation separate.

Parameters are placeholders for literal values

SQLite supports anonymous ?, numbered ?NNN, and named parameters beginning with :, @, or $. Parameters can appear where a literal value is permitted. They are not a general textual macro system, so a parameter cannot stand in for an arbitrary table name, column name, keyword, or ORDER BY direction.

SELECT note_id, device_id, noted_at, note_textFROM maintenance_noteWHERE device_id = @device_id  AND noted_at >= @sinceORDER BY noted_at;

The CLI parameter facility is shell-managed binding

The core SQLite API expects an application to bind values through functions such as the sqlite3_bind_* family. The command-line shell cannot ask you to write C code for each experiment, so it provides .parameter as a convenience. The shell stores named values in a temporary table named temp.sqlite_parameters and supplies matching values when it prepares SQL.

sqlite> .parameter initsqlite> .parameter set @device_id 1sqlite> .parameter set @since "'2026-08-12T00:00:00Z'"sqlite> .parameter listsqlite> SELECT note_id, note_text   ...> FROM maintenance_note   ...> WHERE device_id=@device_id AND noted_at>=@since;sqlite> .parameter unset @sincesqlite> .parameter clear

The temporary parameter table belongs to the CLI connection/session. Closing the session does not turn those bindings into database schema or persistent application configuration.

Text values need special care in .parameter set

The CLI allows the value supplied to .parameter set to be evaluated as an SQL expression. If evaluation fails, it falls back to text. That flexibility is useful but can surprise learners: a numeric-looking string may become a number when you intended text. Current SQLite documentation therefore shows a quoting technique for reliable text binding.

.parameter init.parameter set @a 1365.parameter set @b "'001365'".parameter set @c "'O''Brien'".parameter set @d NULLSELECT typeof(@a), quote(@a);SELECT typeof(@b), quote(@b);SELECT typeof(@c), quote(@c);SELECT typeof(@d), quote(@d);

Use typeof() and quote() as diagnostic tools. They let you see whether the shell bound an integer, text, or NULL rather than trusting how a value happens to print.

Anonymous ? parameters behave differently in the CLI

In application APIs, anonymous question-mark parameters are commonly bound by position. In the command-line shell, current documentation says unnamed parameters remain unbound and therefore evaluate as SQL NULL. Named parameters are the practical choice for .parameter experiments.

SELECT ? AS anonymous_value,       typeof(?) AS anonymous_type;-- Unbound anonymous parameters are NULL in the CLI..parameter set @status "'active'"SELECT @status AS named_value,       typeof(@status) AS named_type;

Shell variables are not SQL parameters

A Bash variable such as $STATUS or a PowerShell variable such as $Status exists in the operating-system shell. A SQLite named parameter such as @status exists in the prepared SQL statement/binding layer. If you paste a shell variable into an SQL string, you are doing textual construction, not database parameter binding.

Harmless injection demonstration: why code/data separation matters

Use only the disposable Chapter 2 database. Suppose a program wants to find a device by code and constructs SQL by concatenation. A malicious-looking value can change the meaning of the statement because the generated source code changes. We do not need to damage anything to prove the issue.

-- Intended value:PUMP-007-- Malicious-looking value:PUMP-007' OR '1'='1-- Unsafe textual construction could become:SELECT device_code, device_nameFROM deviceWHERE device_code = 'PUMP-007' OR '1'='1';-- Result: the predicate is true for every row, so all devices can be returned.

.parameter init.parameter set @code "'PUMP-007'' OR ''1''=''1'"SELECT device_code, device_nameFROM deviceWHERE device_code = @code;-- Expected: zero rows, because the entire text is treated as one value.

Parameters do not replace identifiers

A common next mistake is trying to parameterize a table or column name. Values can be bound; SQL grammar elements cannot. If an application lets a user choose a sort column, the application must map that choice through an allowlist of known identifiers, not bind the identifier as a value.

-- Valid concept: value parameterSELECT * FROM device WHERE status = @status;-- Not a way to parameterize an identifierSELECT @column_name FROM device;-- This selects the bound value once per row; it does not choose a column.

Parameter lab: difficult values

Bind each of the following as data and prove the type/value with typeof() and quote(): O'Brien, 00123 as text, a Unicode phrase such as پمپ شماره ۷, an empty string, and SQL NULL. Then insert them into a temporary scratch table using named parameters.

CREATE TEMP TABLE parameter_lab(label TEXT, value);.parameter init.parameter set @label "'name'".parameter set @value "'O''Brien'"INSERT INTO parameter_lab(label,value) VALUES(@label,@value);.parameter set @label "'numeric-looking text'".parameter set @value "'00123'"INSERT INTO parameter_lab(label,value) VALUES(@label,@value);.parameter set @label "'unicode'".parameter set @value "'پمپ شماره ۷'"INSERT INTO parameter_lab(label,value) VALUES(@label,@value);.parameter set @label "'null'".parameter set @value NULLINSERT INTO parameter_lab(label,value) VALUES(@label,@value);SELECT label, typeof(value), quote(value)FROM parameter_lab;

Production judgment: CLI binding is a teaching bridge

.parameter is excellent for understanding prepared-statement semantics and for safe CLI experiments. It is not a substitute for application-driver binding, and it does not magically make a shell script safe if the shell still interpolates untrusted text into SQL before sqlite3 sees it. Chapter 15 will implement binding in real application APIs; Chapter 19 will return to injection and trust boundaries in more depth.

Summary and next lesson

SQL code and data values belong in separate channels. SQLite parameters provide the structural boundary; the CLI's .parameter facility makes that boundary visible. The final lesson of Chapter 2 uses the same discipline for files: CSV is a data interchange format, not a database backup, and safe imports need staging and validation before they reach final tables.

References

• SQLite — Command Line Shell For SQLite.
• SQLite — Query Result Formatting In The CLI.
• SQLite — Download Page.
• SQLite — Release History.