SQLite CLI Parameter Name Validation Mismatch Between Documentation and Implementation

Issue Overview: Discrepancy in Parameter Name Enforcement Within SQLite3 CLI

The core issue revolves around the SQLite3 Command Line Interface (CLI) accepting parameter names that violate documented naming conventions. According to SQLite documentation, valid parameter names must begin with one of the characters $, :, @, or ?. However, the CLI allows users to set parameters with names starting with arbitrary characters (e.g., ~abc or xyz) via the .parameter set command. While these parameters are stored in the temp.sqlite_parameters table, they cannot be referenced in SQL statements as parameters, leading to errors such as no such column. This creates confusion for users who expect strict adherence to the documented rules.

Key Observations

1. Documentation Claims:
   The SQLite CLI documentation explicitly states that parameter names must start with $, :, @, or ?. This aligns with SQLite’s parameter binding rules for prepared statements.
2. CLI Behavior:
   The .parameter set command does not validate parameter names. For example:

   .parameter set ~abc 'value'  -- Accepted despite invalid prefix
   SELECT ~abc;                 -- Fails: "no such column"
   

   This allows "illegal" parameter names to persist in temp.sqlite_parameters but renders them unusable as parameters.

3. Implementation Mechanics:
   The CLI populates the temp.sqlite_parameters table using SQL REPLACE statements that bypass parameter binding. For example:

   // Simplified code from shell.c (SQLite source)
   zSql = sqlite3_mprintf("REPLACE INTO temp.sqlite_parameters(key,value) VALUES(%Q,%s);", zKey, zValue);
   

   This approach treats parameter values as raw SQL literals, exposing risks of SQL injection if values are unescaped.

Impact Analysis

• User Confusion: Users may assume parameter names are validated, leading to runtime errors.
• Security Risks: Improper handling of parameter values (e.g., using %s instead of %Q in sqlite3_mprintf) can introduce SQL injection vulnerabilities.
• Documentation Misalignment: The CLI’s behavior contradicts the documentation, undermining trust in SQLite’s reliability.

Possible Causes: Design Choices and Implementation Nuances

1. Intentional Flexibility in Parameter Storage

The temp.sqlite_parameters table is designed as a general-purpose key-value store for the CLI, not strictly for parameter binding. Developers may use this table for temporary storage unrelated to SQL parameters, such as script variables or intermediate calculations. For example:

.parameter set total "(SELECT SUM(value) FROM temp.sqlite_parameters)"
SELECT @total;  -- Valid if "total" is prefixed with @

Allowing arbitrary keys provides flexibility for advanced users, even if some keys are incompatible with parameter syntax.

2. Lack of Validation in .parameter set

The .parameter set command does not enforce naming rules because:

• Historical Reasons: Earlier CLI versions lacked parameter binding features, and the .parameter command evolved organically.
• Performance Trade-offs: Validating every parameter name would add overhead for users who populate the table programmatically.
• Use Case Prioritization: The CLI prioritizes scripting convenience over strict parameter hygiene.

3. Documentation Ambiguities

The documentation conflates two distinct concepts:

• Parameter Binding: A low-level API feature (sqlite3_bind_*()) that requires valid parameter names.
• CLI Parameter Substitution: A convenience feature that mimics parameter binding by injecting values from temp.sqlite_parameters into SQL statements.

This conflation creates the false impression that .parameter set uses true parameter binding, when in reality, it performs string substitution.

4. Code Structure Limitations

The CLI’s parameter handling is implemented in shell.c as a meta-command, not as part of SQLite’s core library. This isolation limits access to internal validation routines, making it harder to enforce naming rules consistently.

Troubleshooting Steps, Solutions, and Fixes

Step 1: Verify Parameter Name Validity

Before using a parameter in a query, confirm its name starts with $, :, @, or ?. Use the LIKE operator to filter valid parameters:

SELECT key FROM temp.sqlite_parameters 
WHERE key GLOB '[$:@?]*';

Step 2: Audit Parameter Usage in Queries

Replace invalid parameter references with valid syntax. For example:

-- Invalid:
SELECT xyz FROM table;

-- Valid:
SELECT @xyz FROM table;

Step 3: Secure Parameter Value Handling

To prevent SQL injection, avoid using .parameter set with untrusted input. Instead:

1. Use Quoted Values:

   .parameter set :user "'alice'"  -- Safe: Value is string 'alice'
   

2. Leverage printf() Safely:
   In scripts, sanitize values with sqlite3_mprintf("%Q", value).

Step 4: Modify CLI Source Code for Strict Validation

For developers needing strict enforcement, modify shell.c to validate parameter names in the .parameter set handler:

// In handle_parameter_command() (shell.c):
if (!is_valid_parameter_name(zKey)) {
  utf8_printf(p->out, "Error: Invalid parameter name '%s'\n", zKey);
  return 1;
}

// Validation function:
int is_valid_parameter_name(const char *zKey) {
  return zKey[0] == '$' || zKey[0] == ':' || zKey[0] == '@' || zKey[0] == '?';
}

Step 5: Use Temporary Tables for Non-Parameter Data

If storing non-parameter data, create a dedicated temporary table:

CREATE TEMP TABLE app_vars (key TEXT PRIMARY KEY, value TEXT);
INSERT INTO app_vars VALUES ('~abc', 'value');

Step 6: Update Documentation Practices

Clarify the distinction between CLI parameters and API-level binding in documentation:

• Add a warning: "The .parameter command does not validate names or prevent SQL injection."
• Separate CLI parameter features from core SQLite parameter binding sections.

Step 7: Advocate for Enhanced CLI Features

Submit a feature request to the SQLite team for:

• A .parameter validate subcommand to check name validity.
• An optional strict mode (--strict-parameters) to enforce naming rules.

Final Workaround: Use SQLite Bindings Directly

Bypass the CLI’s parameter system entirely by using prepared statements with sqlite3_bind_*() in custom scripts or applications.

By addressing the root causes and applying these solutions, users can mitigate confusion, enhance security, and align their workflows with SQLite’s intended behavior.