[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:
All values passed via
-paramare 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.All values passed via
-paramare literals, exactly as if it were passed viasqlite3_bind_XXXor 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 filesn"
" -nullvalue TEXT set text string for NULL values. Default ''n"
" -pagecache SIZE N use N slots of SZ bytes each for page cache memoryn"
- " -param NAME VALUE bind parameter NAME to VALUEn" " -quote set output mode to 'quote'n" " -readonly open the database read-onlyn" " -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 ){ ```