SQLite Forum

Extract files from sqlar with a directory prepended
Login
While technically true, note that using `-cmd` forces the same sort of string substitution that make security-conscious folks very nervous indeed, and can fail even if security were not a concern.

For instance, strings with embedded single quotes break `.param` if not passed in a somewhat non-obvious way:

```sh
$ sqlite3 -cmd ".param set :name 'Bobby's Tavern'" :memory: 'select * from sqlite_parameters'
.parameter CMD ...       Manage SQL parameter bindings
   clear                   Erase all bindings
   init                    Initialize the TEMP table that holds bindings
   list                    List the current parameter bindings
   set PARAMETER VALUE     Given SQL parameter PARAMETER a value of VALUE
                           PARAMETER should start with one of: $ : @ ?
   unset PARAMETER         Remove PARAMETER from the binding table
Error: no such table: sqlite_parameters

# How about the standard SQL practice of doubling internal quotes?
$ sqlite3 -cmd ".param set :name 'Bobby''s Tavern'" :memory: 'select * from sqlite_parameters'
.parameter CMD ...       Manage SQL parameter bindings
   clear                   Erase all bindings
   init                    Initialize the TEMP table that holds bindings
   list                    List the current parameter bindings
   set PARAMETER VALUE     Given SQL parameter PARAMETER a value of VALUE
                           PARAMETER should start with one of: $ : @ ?
   unset PARAMETER         Remove PARAMETER from the binding table
Error: no such table: sqlite_parameters

# OK, will double quotes work?
$ sqlite3 -cmd ".param set :name \"Bobby's Tavern\"" :memory: 'select * from sqlite_parameters'
:name|Bobby's Tavern
```
And if the string you want to substitute contains both single and double quotes? Have fun.

With my hypothetical `-param`, strings with pretty much any content can be passed safely to your SQLite queries:

```
sqlite3 -param :name "Bobby's Tavern" ...
sqlite3 -param '$quote' "\"I'm full,\" said Jane." ...
```