SQLite Forum

SQLite3 shell doing math operation on parameter substitution
Login

SQLite3 shell doing math operation on parameter substitution

(1) By AlexJ (CompuRoot) on 2022-01-10 23:53:41 [link] [source]

I believe it is a bug: SQLite3 (v3.37.0) doing math calculation on parameter setting instead of saving in database data as it is.

Test case:

------------------------- DDL ----------------------------------------

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "ips" (
  ipid      INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  ip        TEXT NOT NULL UNIQUE,
  block     INTEGER NOT NULL,
  country   TEXT,
  contact   TEXT,
  note      TEXT,
  created   TEXT DEFAULT CURRENT_TIMESTAMP,
  updated   TEXT DEFAULT CURRENT_TIMESTAMP
) STRICT;
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "ips" (
  ipid      INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  ip        TEXT NOT NULL UNIQUE,
  block     INTEGER NOT NULL,
  country   TEXT,
  contact   TEXT,
  note      TEXT,
  created   TEXT DEFAULT CURRENT_TIMESTAMP,
  updated   TEXT DEFAULT CURRENT_TIMESTAMP
) STRICT;
CREATE INDEX IF NOT EXISTS "idx_block" on "ips" ("block");
CREATE UNIQUE INDEX IF NOT EXISTS "idx_ip" on "ips" ("ip");
CREATE TRIGGER IF NOT EXISTS "update_timestamp"
AFTER UPDATE OF "ip", "country", "contact", "note" ON "ips" FOR EACH ROW
WHEN OLD.updated = NEW.updated
BEGIN
    UPDATE "ips" SET "updated" = datetime('now') WHERE ipid = NEW.ipid;
END;
COMMIT;
CREATE UNIQUE INDEX IF NOT EXISTS "idx_ip" on "ips" ("ip");
CREATE TRIGGER IF NOT EXISTS "update_timestamp"
AFTER UPDATE OF "ip", "country", "contact", "note" ON "ips" FOR EACH ROW
WHEN OLD.updated = NEW.updated
BEGIN
    UPDATE "ips" SET "updated" = datetime('now') WHERE ipid = NEW.ipid;
END;
COMMIT;

Bellow is a simplified script that inserting data into database:


#!/bin/sh                                                                                                                                                         
                                                                                                                                                                  
ip='184.82.96.0/19'                                                                                                                                               
block='TRUE'                                                                                                                                                      
country='TN'                                                                                                                                                      
contact='+66-2-615-3964' # it doing math operation on this parameter                                                                                                               
note='{abuse:"Password brute-forcers", {org:"AIS Fibre","abuse-email":"xxxx@ais.co.th"}}'                                                                    
                                                                                                                                                                  
################################################################################                                                                                  
echo "                                                                                                                                                            
.parameter set @ip          '${ip}'                                                                                                                               
.parameter set @block        ${block}                                                                                                                             
.parameter set @country     '${country}'                                                                                                                          
.parameter set @contact     '${contact}'                                                                                                                          
.parameter set @note        '${note}'                                                                                                                             
                                                                                                                                                                  
INSERT OR IGNORE INTO ips (ip,block,country,contact,note) VALUES (@ip,@block,@country,@contact,@note);" | sqlite3 test.db                                         
                                                                                                                                                                  
echo -------------------------------------------------------                                                                                                      
echo ".parameter set @ip '${ip}'                                                                                                                                  
SELECT * FROM ips WHERE ip = @ip; "  | sqlite3 -line test.db                                                                                                      
echo -------------------------------------------------------                                                                                                      
                                                                                                                                                                  
exit

And here is result:


-------------------------------------------------                                                                                                                 
   ipid = 1                                                                                                                                                       
     ip = 184.82.96.0/19                                                                                                                                          
  block = 1                                                                                                                                                       
country = TN                                                                                                                                                      
contact = -4515                                                                                                                                                   
   note = {abuse:"Password brute-forcers", {org:"AIS Fibre","abuse-email":"xxxx@ais.co.th"}}                                                                 
created = 2022-01-10 22:59:40                                                                                                                                     
updated = 2022-01-10 22:59:40              
                                                                                                                       
-------------------------------------------------

Bug: instead of inserting parameter @contact, SQLite3 did first calculation on this parameter and saved inside of database result of arithmetic calculation instead of saving phone number (+66-2-615-3964 = -4515)

(2) By Ryan Smith (cuz) on 2022-01-11 00:15:11 in reply to 1 [link] [source]

That's seemingly a PERL/PHP (or whatever language that is) problem, or perhaps the wrapper being used, but not an SQLite problem.

In SQLite, using the C API, that "Parameter setting" would be bound using specialized API functions, such as "sqlite3_bind_text()" or "sqlite3_bind_int64()" etc. which cannot and will not be misconstrued.

I'm assuming PHP would try to determine the type of the parameter and then decide which bind-function to use, and in there it (PHP) actually misinterprets the value to be Integer and also parses it as an expression. SQLite wouldn't do that, even if it was passed using the wrong "bind" function (if that is even possible).

Perhaps a PHP forum might shed some light on it.

(3) By Ryan Smith (cuz) on 2022-01-11 00:20:21 in reply to 2 [link] [source]

Nevermind the previous post, it's late here, that is obviously command-line arguments using the CLI.

My guess is the error is in the CLI code, not the SQLite engine code.

(4) By RandomCoder on 2022-01-11 00:20:21 in reply to 2 [link] [source]

The user is using the SQLite shell. A simpler/smaller example:

$ sqlite3
SQLite version 3.37.0 2021-11-27 14:13:22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .parameter set @contact '+66-2-615-3964'
sqlite> .parameter list
@contact -4515

In this example, they expected @contact to be +66-2-615-3964, not -4515.

I honestly have no idea what the expected behavior is here, but this would surprise me if I ran into it.

(5) By Richard Hipp (drh) on 2022-01-11 00:21:28 in reply to 1 [link] [source]

Much simpler repro script:

.parameter set @one '+1+2-3+4'
SELECT @one;

(6) By jake on 2022-01-11 00:33:28 in reply to 1 [link] [source]

This behaviour has been reported previously.

A workaround to preserve the value is to place it in parentheses. e.g.

sqlite> .param set @contact ('+66-2-615-3964')
sqlite> SELECT @contact;
+66-2-615-3964

Probably just requires some clarification in the docs.

(11) By AlexJ (CompuRoot) on 2022-01-11 03:29:18 in reply to 6 [link] [source]

Thank you for suggestion !

At least this syntax isn't so confusing as suggested bellow "'xxx'", but still, I believe it shouldn't be a guess or workaround, but either must be a fix to sqlite3 CLI processing or set it as official rule in documentation.

(7) By Richard Hipp (drh) on 2022-01-11 00:38:30 in reply to 1 [link] [source]

Not a bug.

The command that is giving you trouble is this line:

.parameter set @contact     '+66-2-615-3964'

The single-quotes on the argument are being stripped off by the CLI itself, leaving @contact with the value as a bare +66-2-615-3964. This gets evaluated to -4515, as you observe. You work-around is to bind @contact to a quoted string instead:

.parameter set @contact     "'+66-2-615-3964'"

The prior line of the form:

.parameter set @ip          '184.82.96.0/19'

Works because the bare string 194.82.96.0/19 is a syntax error, and after seeing that syntax error, the CLI tries putting the string inside of quotes to see if that works better, and it does. But +66-2-615-3964 is not a syntax error, so it never gets quoted.

You are correct that the details of this behavior are not documented. You might even argue that they are unintuitive. But the behavior is as originally intended.

Perhaps we need to invent an easier way for shell scripts to get parameter values into the CLI.

(9) By Larry Brasfield (larrybr) on 2022-01-11 00:48:29 in reply to 7 [link] [source]

This is the sort of issue that motivates me to have a meta-command named "seeargs" in new shell code. Seeing what actually results from the CLI pre-processing helps when puzzles like the OP's are encountered.

(10.2) By AlexJ (CompuRoot) on 2022-01-11 03:13:50 edited from 10.1 in reply to 7 [link] [source]

Not a bug.

Im sorry, but to me it is still looks like a bug.

The single-quotes on the argument are being stripped off by the CLI itself

No, it is wrong assumption. Let debug what shell did:

#!/bin/bash -x                                                                                                                                                    
                                                                                                                                                                  
echo "                                                                                                                                                            
  some 'quoted' stuff                                                                                                                                              
"

as you can see:

+ echo '
  some '\''quoted'\'' stuf
'

  some 'quoted' stuf

+ exit

the shell kept single quotes as it is in my original posting, so it is the same as you suggested: "'xxx'"

Let get rid of operation system's shell at all and run sqlite3 shell directly:

sqlite> .parameter set @a '+1+2+3'
sqlite> select @a;
6

Why the sqlite evaluating enclosed in single quotes data at all ?

If one will do workaround by double quoting as

sqlite> .parameter set @a "'+1+2+3'"

then first of all, it is very confusing and second it's breaking the rule that double quotes allowed only to identificators only, such as table or column names and single quotes for using on STRING data (which is the case for my example)

In anyone unix's shell, stuff enclosed in a single quotes supposed to be a raw data and must be kept as it is.

The only unquoted parameter in my original example is ${block} and intentionaly passed unquoted to assist sqlite to evaluate it to "1" or "0" from "TRUE" or "FALSE" instead of treating it as a string.

I don't really understand, why sqlite3 trying to evaluate quoted value at all, especially on assigning to prepared statement ?

Why this works as expected:

sqlite> select '2+2';
2+2

but in most critical operation from the point of security - in prepared statement assignment, sqlite trying to be smart and converts itself into calculator? If user supplied data going to be evaluated by database engine, then it isn't prepared statement, but pandora box IMHO.

(13) By AlexJ (CompuRoot) on 2022-01-11 04:49:50 in reply to 7 [link] [source]

I believe the quickest "fix" to this issue is to correct "help" in sqlite3 shell as well online documentation as @jake suggested: require to wrap arbitrary literal strings with parentheses with single quotes in the same way as it going with VALUES for example to match syntax:


.parameter set @str1 ('string')
.parameter set @str2 ('+1+2+3')

.parameter set @num (2+3+4)

in these cases, @str1 & @str2 will be strings and parameter @num will be 9.

IMHO it will be more intuitively understandable and may be some one would find calculation on assignment as a feature.

And as always, - thank you for the sqlite !

(16.4) By AlexJ (CompuRoot) on 2022-01-12 17:07:35 edited from 16.3 in reply to 7 [link] [source]

Deleted

(17) By AlexJ (CompuRoot) on 2022-01-12 17:10:09 in reply to 7 [link] [source]

What to do with JSON's key/value when supplying it to .parameter .dot command?

If one would use proposed workaround by double quoting, then it looks like the only way to supply JSON is to escape all double quotes in original content, which is wrong and unacceptable from security point of view to attempt processing raw data.

All of below will fail

.param set @j "'{"key":"value"}'"
.param set @j "\"{"key":"value"}\""
.param set @j '\'{"key":"value"}\''
.param set @j '\\'{"key":"value"}\\''
.param set @j '''{"key":"value"}'''

This works:

.param set @j ('{"key":"value"}')

but this below doesn't again, because of space after comma:

.param set @jjj ('{"key1":"value1", {"key2":"value2"}')

(26.1) By Adrian Ho (lexfiend) on 2022-01-13 13:19:22 edited from 26.0 in reply to 7 [link] [source]

Perhaps we need to invent an easier way for shell scripts to get parameter values into the CLI.

Perhaps something like the PoC I posted last year?

(28) By AlexJ (CompuRoot) on 2022-01-13 17:53:27 in reply to 26.1 [link] [source]

I wish I could read it erlier :(

Thank you for the link !

(8) By Larry Brasfield (larrybr) on 2022-01-11 00:45:02 in reply to 1 [link] [source]

Ryan has the essence of your trouble; this is just added detail.

The first echo command in your sh script is having sh parameter expansion done, as you intend (I surmise.) However, the single quotes around your parameter values are not treated as text literal demarcation by the SQLite shell. As documented in section 4 of the CLI doc, those quotes serve merely to (uselessly) delimit the value argument to the .parameter meta-command. If you want to specify a text literal to that meta-command, you will need to escape the quotes. And to get the escape character to the CLI from your sh script, you will need to escape that also (since it is within a double-quoted string subject to parameter expansion.)

Your sense that single-quotes will help is good; but you just need to get them past the two shell's interposed between what you write and what the .parameter implementation actually gets.

To see what actually gets to that meta-command,
select * from temp.sqlite_parameters;
may help.

(12) By AlexJ (CompuRoot) on 2022-01-11 03:44:53 in reply to 8 [link] [source]

Thank you for looking at this issue,

According to section 4 you referenced "As documented in section 4 of the CLI doc"

Text bounded by a pair of single-quotes or double-quotes is treated as a single argument, with the quotes stripped.

That's why I assumed that is isn't useless.

If you want to specify a text literal to that meta-command, you will need to escape the quotes

The quotes passed correctly to sqlite3 shell for sure(running bash with option -x can prove it). The issue I believe with parsing in sqlite3 shell. Even if you would run sqlite3 shell alone, interactively, then the issue is still "works":

sqlite> .parameter init
sqlite> .parameter set @a '+1+2+3'
sqlite> SELECT @a;
6

while processing regular SQL statement works as expected:

sqlite> SELECT '2+2';
2+2

quoted value isn't evaluated as it is happens with .parameter set

(14) By Larry Brasfield (larrybr) on 2022-01-11 05:39:27 in reply to 12 [link] [source]

(In regard to "useless" single-quotes and doc excerpt explaining their use:)

That's why I assumed that is isn't useless.

Perhaps "needless" would be a better word. Your argument to .parameter would be the same with or without the enclosing single-quotes. In this case, they served to help disguise the problem.

If you want to specify a text literal to that meta-command, you will need to escape the quotes

The quotes passed correctly to sqlite3 shell for sure(running bash with option -x can prove it). The issue I believe with parsing in sqlite3 shell.

To be clearer: If you want to specify a text literal to that meta-command, you will need to escape the quotes from the SQLite shell's command parser so that they actually reach the .parameter command implementation. They can be prefixed with '\' to accomplish that escaping. And to get a '\' character through your sh script, the '\' will need to be doubled. This need arises precisely to get the desired result through the SQLite shell's parsing. And that parsing is not a bug, certainly not now after many years of stability and presumable reliance by other users.

... while processing regular SQL statement works as expected: ...

Submitted SQL is subject to no parsing by the SQLite shell; it is passed to sqlite3_prepare as written. The parsing described in the doc section I linked is done for meta-commands (aka "dot commands") alone.

If you want your expression-like text to be treated as text when .parameter finally gets it in an argument list, you can write: .parameter set contact \'+66-2-615-3964\' at the sqlite3 input prompt. Without the single-quotes, (protected from the shell's parser), that value looks like and is treated as an arithmetic expression per normal SQL rules. The .parameter implementation, as its first pass (per Richard's explanation of retry logic), places the value argument straight into a SQL VALUES() clause for a parameter table UPSERT. Only if that fails does it try single-quoting the value argument.

(15.1) By AlexJ (CompuRoot) on 2022-01-12 13:35:50 edited from 15.0 in reply to 14 [source]

Thank you Larry for taking time to look into the issue!

If you want to specify a text literal to that meta-command, you will need to escape the quotes from the SQLite shell's command parser so that they actually reach the .parameter command implementation. They can be prefixed with '' to accomplish that escaping.

Could you please test solution you proposed above on your side in sqlite3 shell to be make sure it is not something wrong in my local settings (on linux, freebsd and all zoo of windows):


sqlite> .param init
sqlite> .param set @var \'a b c\'

And to get a '' character through your sh script, the '' will need to be doubled.

BTW, there no need to double escaping (or double escaping) single quotes in sh, dash, bash and so on, if one passing single quotes enclosed in double quoted string. You can use shell's debug option -x to see how shell threats this character.

#!/bin/bash -x

echo "some 'quoted' text"

Single quotes passing as literal characters and don't need to be escaped:

+ echo 'some '\''quoted'\'' text'

since shell already did escaping for us.

And that parsing is not a bug, certainly not now after many years of stability and presumable reliance by other users.

Do you mean that SQLite haven't any bugs ???

Then run please sqlite3 shell. Interactively. And issue following .dot commands:

sqlite> .param init
sqlite> .param set @vvv '+1+2+3'
sqlite> select @vvv;

Submitted SQL is subject to no parsing by the SQLite shell; it is passed to sqlite3_prepare as written.

If it's prepared statement and passed into "sqlite3_prepare as written", then it should pass it with singles quotes and then sqlite engine will treat it in the same way as

SELECT '+1+2+3';

but .dot command .parameter stripping single quotes in a shell code and that became not a prepared statement at all.

Using proposed by Richard workaround
sqlite> .param set @vvv "'+1+2+3'"
sorry, but is not intuitively understandable why to do it, especially if this shell's behavior isn't documented.

And that parsing is not a bug, certainly not now after many years of stability and presumable reliance by other users.

Larry, we are in the same boat and I pretty sure that you know that there no ideally perfect programs. If no one didn't stepped on a bug for a long time, it doesn't means there no bugs, and that's Ok, nobody perfect. Microsoft and linux periodically discovering bugs that are a few decades all even the whole world using it.

I know, that many users claims that they found a bug usually boiling down to either user's error or misbehaving of third party tool that uses sqlite, but issue I talking, is in SQLite3 shell. The whole point of .dot command .param is to emulate prepared statement to be make sure value will be passed to database engine as is, without modifications and what is most important - without trying to evaluate content(!!!).

(18) By Harald Hanche-Olsen (hanche) on 2022-01-12 17:22:42 in reply to 15.1 [link] [source]

Using proposed by Richard workaround
sqlite> .param set @vvv "'+1+2+3'"
sorry, but is not intuitively > understandable why to do it, especially if this shell's behavior isn't documented.

It is, in fact, documented:

Text bounded by a pair of single-quotes or double-quotes is treated as a single argument, with the quotes stripped. Within a double-quoted argument, traditional C-string literal, backslash escape sequence translation is done.

That is pretty clear to me, though admittedly, C-style quoting may not be for the faint of heart. As they say, the difference between a bug and a feature is whether it is documented or not. (I don't totally agree, but there is some truth to it.)

Look, I get it: Escape conventions are indeed very tricky, and we all fall victim to their various traps from time to time. It would be very nice indeed they could be avoided.

To that end, it would be very nice indeed if there were a way to avoid it. One way might be to add a third way of quoting to dot-commands: For example, if white space is followed by a # character, the rest of the intput line would be taken verbatim as the final argument (choice of trigger character subject to debate, of course).

Another way would be to introduce special quoting rules for .param, but that ruins consistency, and is better avoided, I think.

The whole point of .dot command .param is to emulate prepared statement to be make sure value will be passed to database engine as is, without modifications and what is most important - without trying to evaluate content(!!!).

Hmm, I thought it was just a convenient way to store a value that you might want to use repeatedly. Or, to let you easily rerun a complex query with different values, by changing the parameter and rerunning the exact same query. Your proposed rationale does not make a lot of sense to me.

(20) By AlexJ (CompuRoot) on 2022-01-12 18:53:19 in reply to 18 [link] [source]

It is, in fact, documented:

This documentation applied to ALL .dot commands where .parameter just one of them. Particular description of '.parameter' command lead me to believe base on

SQLite allows bound parameters to appear in an SQL statement anywhere that a literal value is allowed. The values for these parameters are set using the sqlite3_bind_...() family of APIs.

so basically it is 'prepared statement', but since sqlite3 shell stripping quotes before passing value to sqlite engine we having described here issue.

One way might be to add a third way of quoting to dot-commands: For example, if white space is followed by a # character, the rest of the intput line would be taken verbatim as the final argument (choice of trigger character subject to debate, of course).

No, I don't think # or other "quoting" rules would work. # or something else can be user supplied data and then again we will step on the same "quoting" issue.

Hmm, I thought it was just a convenient way to store a value that you might want to use repeatedly.

I think prepared statement is much better and secure that simply passing user's data directly using insert

I believe the trap I fall into, is because I believed in the word "parameter" and looking documentation that stated that value of this .dot command passed to sqlite3_prepare() function.

The only solution I see would work is to take all right hand content after first space, just after parameter name and up to end of line and pass it to sqlite3_prepare() as is.

(22) By RandomCoder on 2022-01-13 00:15:47 in reply to 18 [link] [source]

To that end, it would be very nice indeed if there were a way to avoid it. One way might be to add a third way of quoting to dot-commands: For example, if white space is followed by a # character, the rest of the intput line would be taken verbatim as the final argument (choice of trigger character subject to debate, of course).

To my mind, it would be a lot better to have a set_val or some variant that one could call that would skip the attempt to use VALUES(%Q,%s); and just use the VALUES(%Q,%Q) path. It'd still be a bit of a surprise for people that use set without understanding what it's doing, but at least there'd be a way to be explicit about what interactions with the shell here.

(24) By Larry Brasfield (larrybr) on 2022-01-13 01:05:14 in reply to 22 [link] [source]

To my mind, it would be a lot better to have a set_val or some variant that one could call that would skip the attempt to use VALUES(%Q,%s); and just use the VALUES(%Q,%Q) path.

If there was such a variant, its name should be set_text_value because that is all it could ever do. The first code path ( VALUES(%Q,%s) ) allows values of any type to be placed into the temp.sqlite_parameters table.

It'd still be a bit of a surprise for people that use set without understanding what it's doing, but at least there'd be a way to be explicit about what interactions with the shell here.

I have taken an action item to make more explicit how values get into the temp.sqlite_parameters table via .parameter set. We strive to avoid surprising or mystifying those who read the docs.

(19) By Larry Brasfield (larrybr) on 2022-01-12 17:39:44 in reply to 15.1 [link] [source]

Could you please test solution you proposed above on your side in sqlite3 shell to be make sure it is not something wrong in my local settings (on linux, freebsd and all zoo of windows):

I did that before posting.

I had written, "And to get a '\' character through your sh script, the '\' will need to be doubled.", to which this response was made:

BTW, there no need to double escaping (or double escaping) single quotes in sh, dash, bash and so on, if one passing single quotes enclosed in double quoted string. You can use shell's debug option -x to see how shell threats this character. ... Single quotes passing as literal characters and don't need to be escaped ...

My comment was about the need to escape the backslash in the context of your shell script where the usual expansion within doublequotes is done on what you forumulate as the .parameter command.

And that parsing is not a bug, certainly not now after many years of stability and presumable reliance by other users.

Do you mean that SQLite haven't any bugs ???

That's not my assertion. The parsing under discussion is occurring exactly as intended and documented, and as it has been done for so many years that we should expect to break many users' scripts if it is changed now. The opening sentence in this thread, "I believe it is a bug", expresses an opinion with which I disagree.

Then run please sqlite3 shell. Interactively. And issue following .dot commands: sqlite> .param init sqlite> .param set @vvv '+1+2+3' sqlite> select @vvv;

The SQLite shell will strip those single-quotes. I know this from my own experimentation, reading of the code that does it, and the doc I cited to you.

Submitted SQL is subject to no parsing by the SQLite shell; it is passed to sqlite3_prepare as written.

If it's prepared statement and passed into "sqlite3_prepare as written", then it should pass it with singles quotes and then sqlite engine will treat it in the same way as SELECT '+1+2+3'; but .dot command .parameter stripping single quotes in a shell code and that became not a prepared statement at all.

Dot commands are subject to argument parsing as documented and discussed previously. Your alleged bug is about a scenario where that parsing is leading to the result you report but did not expect.

Using proposed by Richard workaround
sqlite> .param set @vvv "'+1+2+3'"
sorry, but is not intuitively understandable why to do it, especially if this shell's behavior isn't documented.

Very little about the quoting rules could be fairly called "intuitive", but it is documented where and as I cited earlier in section 4. I think the time has come for you to read it, carefully.

Richard's solution will work at the SQLite shell command line because the outer double-quotes serve to protect the argument, retaining its single-quotes. I did not propose it because the double-quotes would have to be escaped in your sh program, but so do the protective backslashes. Either way it becomes one of the multi-layered quoting tangles that sh programmers love or dread.

Regarding the discussion of perfect programs and lack thereof: I respectfully disagree but decline to participate in hijacking this thread along such lines.

(21) By AlexJ (CompuRoot) on 2022-01-12 22:50:28 in reply to 19 [link] [source]

My comment was about the need to escape the backslash in the context of your shell script where the usual expansion within double quotes is done on what you forumulate as the .parameter command.

Let leave my script out of discussion, Im pretty sure my code works as expected and passing exact values to sqlite3. I showed test cases in interactive mode of sqlite3 which show the issue.

The parsing under discussion is occurring exactly as intended and documented, and as it has been done for so many years that we should expect to break many users' scripts if it is changed now

Me too, personally I hate to change any behavior of programs that been deployed in production, but I didn't asked to change anything. I just reporting bug/feature issue that isn't documented and can be dangerous (what about if malicious user will supply complicated, long math formula that might trigger internal engine to choke). IMHO, there in documentation no any clue that shell will try to evaluate user supplied value, especially enclosed in quotes. If some behavior (especially, - value evaluation behind scene) isn't documented, then it looks to me as a bug, even if it is very advanced behavior.

Rules for "dot-commands" says:

Text bounded by a pair of single-quotes or double-quotes is treated as a single argument, with the quotes stripped.

and that's exactly what one would expect, - strip a quotes and put enclosed content as is into key/value store.

Read documentation further:

The dot-commands are interpreted by the sqlite3.exe command-line program, not by SQLite itself.

where clearly said: "not by SQLite itself.", but who then doing arithmetic calculation if not SQLite? I took a quick look at source code that parsing .dot commands and didn't found any calculations. Anyway, nowhere in documentation says that shell will evaluate supplied value, that's why such program behavior IMHO can be called as a bug.

Your alleged bug is about a scenario where that parsing is leading to the result you report but did not expect.

Call me nitpicker, but parsing != evaluation, it is two different subsequent steps, first parse, then evaluate, which is comes as surprise.

Very little about the quoting rules could be fairly called "intuitive", but it is documented where and as I cited earlier in section 4.

It is probably my bad English, if I still can't explain that the problem isn't with quoting, but with evaluation. If it is says parameter key value then the value must be a raw value, as is. There no in documentation anything that it says that value can be a math expression and this expression will be automatically evaluated.

I think the time has come for you to read it, carefully.

Larry, I believe I already remembered this section 4 as a poem, but if I missed there explanation regarding value evaluation, I will be more than appreciated if you pointing me there.

Regarding "fixing" a problem. IMHO, first of all documentation must be clearly stated, that a value in .parameter will be analyzed, evaluated and modified if calculated

To avoid breaking user's scripts who found this undocumented behavior as a feature, they can still use set command of .parameter as is now, but there might be implemented another .parameter command, for example "assign", that doing true assignment of raw value to a key (actually exactly what prepared statement must do). IMO the shell can simply threat right hand part of command line as a raw value that beginning right after first space behind key name, up to the end of line and put it as blob to a store. Something like:

echo "${line}" | sed -r 's/^\.para?m?e?t?e?r?\s+set\s+([@\$]*[A-Za-z_]+[0-9]*|\?[0-9]*)\s//g'

Either way it becomes one of the multi-layered quoting tangles that sh programmers love or dread.

I don't agree, the code below can live without ugly escaping and can be cooked on fly if need:

#!/bin/bash

someVar="aaa 'bbb' ccc"

cat << 'EOF' >test.file.txt
.param set @key2 {"key1":"value1", {"key2":"value2"}
.param set ? {"key1":"value1", {"key2":"value2"}
.param set ?1 {"key1":"value1", {"key2":" ' ' ' ' "}

\t \n
${someVar}

EOF


If one would check content of test.file.txt then he/she will find that there no need for escaping. Instead of writing to a file, one can pipe heredoc data to any program as a stream of literal bytes. You can even place inside of heredoc block even raw bytes 0x01, 0x02... and shell will happily write it to a file.

Larry, I really want you to understand that I don't want to offend anyone for sure. If the word "bug" is unwelcome, Im fine with it as long as the issue resolved. As I said, English isn't my native language, so I trying to be more expressive in attempt to describe subject. If I touched some red zones, - it isn't on purpose for sure. Let me know if it is.

I just love SQLite3 a lot and want this tool be better and better, so I hope we are on the same page.

(23.1) By Larry Brasfield (larrybr) on 2022-01-13 00:52:49 edited from 23.0 in reply to 21 [link] [source]

(Edited to escape a few backslashes swallowed by markdown processing.)

My comment was about the need to escape the backslash in the context of your shell script where the usual expansion within double quotes is done on what you forumulate as the .parameter command.

Let leave my script out of discussion, Im pretty sure my code works as expected and passing exact values to sqlite3. I showed test cases in interactive mode of sqlite3 which show the issue.

I'm agreeable to that. However, I will likely continue to remind people whose shell scripts are producing unexpected results that sh, ash, bash, csh, dash, fish, ksh or zsh have a say in what reaches other programs.

... I just reporting bug/feature issue that isn't documented ...

As I have said (and linked twice) and someone else mentioned, the quoting behavior of the shell's command line parser is documented.

... IMHO, there in documentation no any clue that shell will try to evaluate user supplied value ...

We're getting into semantics here. By "the shell", I (and other forum participants) generally mean the CLI program named sqlite3 or sqlite3.exe which contains an instance of the SQLite library, and interprets command line input as either meta-commands or as SQL to be submitted to the library for execution.

The .parameter meta-command is doing some of both. It accepts a set of input arguments as parsed by the shell, and for the "set" subcommand it composes some DDL with this code: zSql = sqlite3_mprintf( "REPLACE INTO temp.sqlite_parameters(key,value)" "VALUES(%Q,%s);", zKey, zValue); and submits it to the library. Note that the 2nd value in the VALUES clause, passed to sqlite3_mprintf() as zValue, is not quoted in any way; it is incorporated into the statement as it was given to this code by the meta-command parser. If somebody wants a blob literal, they can have one put into the parameters table. Or whole SELECT statements involving values taken from other tables could be passed in as the value.

Using a shell build from the cli_extension branch, (with SQLITE_GIMME_SEEARGS #define'd), I created this screen-scrape: larrybr@Bit-Bungler:~/SQLite/SqliteLib/ShellMods$ ./sqlite3 SQLite version 3.38.0 2022-01-08 21:59:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .seeargs set @vvv '+1+2+3' set|@vvv|+1+2+3 sqlite> .seeargs set @vvv \'+1+2+3\' set|@vvv|'+1+2+3' sqlite> .param init sqlite> .param set @noquotes '+1+2+3' sqlite> .param set @quoted \'+1+2+3\' sqlite> .header on sqlite> select * from temp.sqlite_parameters; key|value @noquotes|6 @quoted|+1+2+3 sqlite> , which enables fairly direct observation of what meta-command implementations will see. Note carefully how the @quoted and @noquotes parameter values were affected. The evaluation of that multi-term expression when unquoted happened as the DDL formed by the .parameter set code was evaluated by the library. We could argue for a long time whether this evaluation is done by "the shell" or by "the library", but to no avail. The bottom line, however you characterize this behavior, is that, in order to get text which looks like an arithmetic expression into the parameters table as text, you will have to get some single-quotes around it as it is passed to the above code.

The thrust of my suggestions has been how to get those single-quotes past the meta-command parser and to the .parameter implementation.

That all said, I can see that the doc for the .parameter meta-command should make clear how and when its arguments are evaluated. The doc vaguely mentions "value of the value column" (referring to the parameters table), but says too little about how those values get there. I will improve upon that soon.

Call me nitpicker, but parsing != evaluation

I would perhaps consider calling you a nit-picker if I had made an assertion to the contrary. As I have not, but have focused on parsing (except where I mentioned earlier what ".parameter set ..." does), I'll just say that this discussion has enough red herring content already.

If it is says parameter key value then the value must be a raw value, as is. There no in documentation anything that it says that value can be a math expression and this expression will be automatically evaluated.

... it becomes one of the multi-layered quoting tangles ...

I don't agree, the code below can live without ugly escaping ...

Your shell code's HERE string is not subject to variable expansion as you will see if you examine its effect. Your original post relied on variable expansion to (attempt to) get a value from a sh variable into that parameters table. Given that context for my "it becomes" assertion, your disagreement is truly an instance of nit-picking.

... If the word "bug" is unwelcome, Im fine with it as long as the issue resolved.

There is no bug here. There is a document ambiguity which can be improved so that the next person experiencing your difficulty can be told: "Read the docs, here." The action you need to take should be clear at this point. (Get a pair of enclosing single-quotes to the .parameter set subcommand.) I see no need for any further meta-commands when, with a better understanding of the existing ones, users can accomplish what the suggested new meta-command would do.

(25) By Larry Brasfield (larrybr) on 2022-01-13 06:29:20 in reply to 1 [link] [source]

Consequent to the discussion here, this doc change has been made and will be published soon along with other improvements.

The need for more intuitive ways to set parameter values may drive later feature enhancement, but this doc change should give users the knowledge needed to use ".parameter set ... ..." effectively.

(27) By AlexJ (CompuRoot) on 2022-01-13 17:52:05 in reply to 25 [link] [source]

Thank you for update !

I'm really appreciated for your time and patience with my English, but I think the truth deserved it.

I suggest also to remove misleading sentence: "SQLite allows bound parameters to appear in an SQL statement anywhere that a literal value is allowed. The values for these parameters are set using the sqlite3_bind_...() family of APIs. "

from: https://sqlite.org/cli.html#sql_parameters

because it isn't true for CLI, there no calls to sqlite3_bind_...() functions before running this:

zSql = sqlite3_mprintf(
    "REPLACE INTO temp.sqlite_parameters(key,value)"
    "VALUES(%Q,%s);", zKey, zValue); /* Substitute unsanitazed zValue as */
                                     /* a plain string to %s that might  */
                                     /* be an SQL injection */

As of now, true binding of VALUE to KEY isn't working and lead to dangerous assumption that it is save mechanism that can be used in prepared statements.