SQLite Forum

[PoC] CLI `-param` option
Login

[PoC] CLI `-param` option

(1) By Adrian Ho (lexfiend) on 2021-06-19 08:23:11 [source]

As described in a previous comment, here are patches against v3.36.0 for a new -param NAME VALUE option, to bring shell support for bound parameters in line with pretty much every other SQLite binding. With this, one only has to deal with caller (shell) quoting issues when submitting parameter values, without having to also wrestle with SQL quoting that comes with .param set NAME VALUE.

That said, there are a couple of restrictions for -param over .param set:

  1. All values passed via -param are bound as strings, since that's the only data type the shell deals with. SQLite's dynamic typing makes this largely irrelevant when applying the parameters, but just so you know.

  2. All values passed via -param are literals, exactly as if it were passed via sqlite3_bind_XXX or its equivalent in other languages. If you need the value to be evaluated as an SQL expression (e.g. reading in a file as a BLOB), you want .param set.

Here's an example that illustrates the ramifications:

$ sqlite3 -table -header \
  -param :name Bob -cmd '.param set :nick Abe' \
  -param :age 52.2 -cmd '.param set :children 2.5' \
  -param :pc_str "readfile('sqlite3.pc')" -cmd ".param set :pc_blob readfile('sqlite3.pc')" \
  -param :greeting "\"Hi, I'm Bob,\" said he." -cmd '.param list' \
  :memory: \
  'select *,typeof(value) from sqlite_parameters; select (:age + 3), (:children - 1)'

:age      '52.2'
:children 2.5
:greeting '"Hi, I''m Bob," said he.'
:name     'Bob'
:nick     'Abe'
:pc_blob  X'23205061636B61676520496E666F726D6174696F6E20666F7220706B672D636F6E6669670A0A7072656669783D2F7573722F6C6F63616C0A657865635F7072656669783D247B7072656669787D0A6C69626469723D247B657865635F7072656669787D2F6C69620A696E636C7564656469723D247B7072656669787D2F696E636C7564650A0A4E616D653A2053514C6974650A4465736372697074696F6E3A2053514C20646174616261736520656E67696E650A56657273696F6E3A20332E33362E300A4C6962733A202D4C247B6C69626469727D202D6C73716C697465330A4C6962732E707269766174653A202D6C6D202D6C646C202D6C7A202D6C70746872656164200A43666C6167733A202D49247B696E636C7564656469727D0A'
:pc_str   'readfile(''sqlite3.pc'')'

+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
|    key    |                                                                                                                                             value                                                                                                                                              | typeof(value) |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
| :age      | 52.2                                                                                                                                                                                                                                                                                           | text          |
| :children | 2.5                                                                                                                                                                                                                                                                                            | real          |
| :greeting | "Hi, I'm Bob," said he.                                                                                                                                                                                                                                                                        | text          |
| :name     | Bob                                                                                                                                                                                                                                                                                            | text          |
| :nick     | Abe                                                                                                                                                                                                                                                                                            | text          |
| :pc_blob  | # Package Information for pkg-config

prefix=/usr/local
exec_prefix=${prefix}
libdir=${exec_prefix}/lib
includedir=${prefix}/include

Name: SQLite
Description: SQL database engine
Version: 3.36.0
Libs: -L${libdir} -lsqlite3
Libs.private: -lm -ldl -lz -lpthread 
Cflags: -I${includedir}
 | blob          |
| :pc_str   | readfile('sqlite3.pc')                                                                                                                                                                                                                                                                         | text          |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+

+------------+-----------------+
| (:age + 3) | (:children - 1) |
+------------+-----------------+
| 55.2       | 1.5             |
+------------+-----------------+

Critiques welcome.


Patch against sqlite-src-3360000:

diff --git a/src/shell.c.in b/src/shell.c.in
index 0f8de61..4c1ebfa 100644
--- a/src/shell.c.in
+++ b/src/shell.c.in
@@ -10780,6 +10780,7 @@ static const char zOptions[] =
   "   -nofollow            refuse to open symbolic links to database files\n"
   "   -nullvalue TEXT      set text string for NULL values. Default ''\n"
   "   -pagecache SIZE N    use N slots of SZ bytes each for page cache memory\n"
+  "   -param NAME VALUE    bind parameter NAME to VALUE\n"
   "   -quote               set output mode to 'quote'\n"
   "   -readonly            open the database read-only\n"
   "   -separator SEP       set output column separator. Default: '|'\n"
@@ -11026,6 +11027,9 @@ int SQLITE_CDECL wmain(int argc, wchar_t **wargv){
      || strcmp(z,"-cmd")==0
     ){
       (void)cmdline_option_value(argc, argv, ++i);
+    }else if( strcmp(z,"-param")==0 ){
+      i += 2;
+      (void)cmdline_option_value(argc, argv, i);
     }else if( strcmp(z,"-init")==0 ){
       zInitFile = cmdline_option_value(argc, argv, ++i);
     }else if( strcmp(z,"-batch")==0 ){
@@ -11319,6 +11323,36 @@ int SQLITE_CDECL wmain(int argc, wchar_t **wargv){
           if( bail_on_error ) return rc;
         }
       }
+    }else if( strcmp(z,"-param")==0 ){
+      char *pname, *pval;
+      sqlite3_stmt *pStmt;
+      pname = cmdline_option_value(argc,argv,++i);
+      pval = cmdline_option_value(argc,argv,++i);
+      open_db(&data, 0);
+      bind_table_init(&data);
+      rc = sqlite3_prepare_v2(data.db,
+                   "REPLACE INTO temp.sqlite_parameters(key,value)"
+                   "VALUES(?,?);", -1, &pStmt, 0);
+      if( rc!=SQLITE_OK ){
+        utf8_printf(data.out, "/**** ERROR(param_prepare): (%d) %s *****/\n", rc,
+                    sqlite3_errmsg(data.db));
+      }else{
+        rc = sqlite3_bind_text(pStmt, 1, pname, -1, SQLITE_STATIC);
+        if( rc!=SQLITE_OK ){
+          utf8_printf(data.out, "/**** ERROR(param_bind_name): (%d) %s *****/\n", rc,
+                      sqlite3_errmsg(data.db));
+          goto param_cleanup;
+        }
+        rc = sqlite3_bind_text(pStmt, 2, pval, -1, SQLITE_STATIC);
+        if( rc!=SQLITE_OK ){
+          utf8_printf(data.out, "/**** ERROR(param_bind_value): (%d) %s *****/\n", rc,
+                      sqlite3_errmsg(data.db));
+          goto param_cleanup;
+        }
+        sqlite3_step(pStmt);
+param_cleanup:
+        sqlite3_finalize(pStmt);
+      }
 #if !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_HAVE_ZLIB)
     }else if( strncmp(z, "-A", 2)==0 ){
       if( nCmd>0 ){

Patch against sqlite-amalgamation-3360000 or sqlite-autoconf-3360000:

diff --git a/shell.c b/shell.c
index bf5be5c..b983a92 100644
--- a/shell.c
+++ b/shell.c
@@ -21722,6 +21722,7 @@ static const char zOptions[] =
   "   -nofollow            refuse to open symbolic links to database files\n"
   "   -nullvalue TEXT      set text string for NULL values. Default ''\n"
   "   -pagecache SIZE N    use N slots of SZ bytes each for page cache memory\n"
+  "   -param NAME VALUE    bind parameter NAME to VALUE\n"
   "   -quote               set output mode to 'quote'\n"
   "   -readonly            open the database read-only\n"
   "   -separator SEP       set output column separator. Default: '|'\n"
@@ -21968,6 +21969,9 @@ int SQLITE_CDECL wmain(int argc, wchar_t **wargv){
      || strcmp(z,"-cmd")==0
     ){
       (void)cmdline_option_value(argc, argv, ++i);
+    }else if( strcmp(z,"-param")==0 ){
+      i += 2;
+      (void)cmdline_option_value(argc, argv, i);
     }else if( strcmp(z,"-init")==0 ){
       zInitFile = cmdline_option_value(argc, argv, ++i);
     }else if( strcmp(z,"-batch")==0 ){
@@ -22261,6 +22265,36 @@ int SQLITE_CDECL wmain(int argc, wchar_t **wargv){
           if( bail_on_error ) return rc;
         }
       }
+    }else if( strcmp(z,"-param")==0 ){
+      char *pname, *pval;
+      sqlite3_stmt *pStmt;
+      pname = cmdline_option_value(argc,argv,++i);
+      pval = cmdline_option_value(argc,argv,++i);
+      open_db(&data, 0);
+      bind_table_init(&data);
+      rc = sqlite3_prepare_v2(data.db,
+                   "REPLACE INTO temp.sqlite_parameters(key,value)"
+                   "VALUES(?,?);", -1, &pStmt, 0);
+      if( rc!=SQLITE_OK ){
+        utf8_printf(data.out, "/**** ERROR(param_prepare): (%d) %s *****/\n", rc,
+                    sqlite3_errmsg(data.db));
+      }else{
+        rc = sqlite3_bind_text(pStmt, 1, pname, -1, SQLITE_STATIC);
+        if( rc!=SQLITE_OK ){
+          utf8_printf(data.out, "/**** ERROR(param_bind_name): (%d) %s *****/\n", rc,
+                      sqlite3_errmsg(data.db));
+          goto param_cleanup;
+        }
+        rc = sqlite3_bind_text(pStmt, 2, pval, -1, SQLITE_STATIC);
+        if( rc!=SQLITE_OK ){
+          utf8_printf(data.out, "/**** ERROR(param_bind_value): (%d) %s *****/\n", rc,
+                      sqlite3_errmsg(data.db));
+          goto param_cleanup;
+        }
+        sqlite3_step(pStmt);
+param_cleanup:
+        sqlite3_finalize(pStmt);
+      }
 #if !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_HAVE_ZLIB)
     }else if( strncmp(z, "-A", 2)==0 ){
       if( nCmd>0 ){