SQLite Forum

.param set accepts "illegal" names
Login

.param set accepts "illegal" names

(1) By AlexJ (CompuRoot) on 2022-01-18 18:55:29 [link] [source]

According to documentation, SQLite3 CLI parameter as well related documentation to parameter binding stated that first character for parameter must be "one of the characters "$", ":", "@" or "?" ", but SQLite3 CLI program accepts any alphanumeric characters as a first character in parameter's name while it correctly doesn't allows to use such wrongly assigned parameters.

Test case:

sqlite> .param set ~abc 'abc'
sqlite> select ~abc;
Error: in prepare, no such column: abc (1)
sqlite> .param set xyz 'xyz'
sqlite> select xyz;
Error: in prepare, no such column: xyz (1)

(2) By Larry Brasfield (larrybr) on 2022-01-18 19:42:21 in reply to 1 [link] [source]

I'm not sure how "wrongly" those parameters were set. Consider: .param init .param set dogs 3 .param set cats 4 .param set @pets "(select sum(value) from temp.sqlite_parameters where key in ('dogs', 'cats'))" select @pets as PetCensus; , which prints "7".

You might argue that you should be blocked from creating key/value pairs that cannot be used directly in a query, as the doc you cite already clearly says. My attitude is that it's harmless at worst, except possibly for leading some who ignore the docs to take a peek at them, and potentially somewhat useful.

Arguments for a more paternalistic approach (in the code) to this can be considered, but at present I am not seeing any compelling ones. My own inclination is to make the temp.sqlite_parameters table more generally useful, not less so.

(3) By Harald Hanche-Olsen (hanche) on 2022-01-18 19:53:28 in reply to 2 [link] [source]

Indeed, I discovered this recently, but as I could not for the life of me think up any possible harm coming from it, I decided it not worth reporting.

Someone might wish to (ab)use this for a temporary key-value store, but my own preference would be to make my own temp table with a shorter name for the purpose.

(4) By Larry Brasfield (larrybr) on 2022-01-18 21:10:35 in reply to 3 [link] [source]

When I use sqlite3 from shell scripts, or sometimes when typing at it, I find the absence of two "normal shell" features most frustrating. One is a mechanism for setting and using variables. The other is some kind of branching based upon evaluation of conditions that may involve variables.

The possibility of the temp.sqlite_parameters table (or an equivalent if something separate is advisable) becoming part of such features keeps nagging at me. For that reason, I prefer to leave it open-ended, as it is documented and implemented today.

(5) By AlexJ (CompuRoot) on 2022-01-18 21:58:00 in reply to 4 [link] [source]

a mechanism for setting and using variables.

Variables IMHO can be emulated like:

WITH ccc AS (SELECT "cats" AS MyVariable) SELECT value FROM temp.sqlite_parameters, ccc WHERE key = MyVariable;

... as it is documented and implemented today.

Unfortunately documentation doesn't match implementation.

The first sentence from SQL parameters documentation stated:

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..

This documentation exclusively related to SQLite CLI program aka sqlite3 which is misleading.

(7) By AlexJ (CompuRoot) on 2022-01-18 22:05:13 in reply to 3 [link] [source]

but my own preference would be to make my own temp table with a shorter name for the purpose.

Yes, functionality provided by .parameter command can be done in plain SQL.

create temp table kval (key,value);
insert into kval values('cats',3);
insert into kval values('dogs',4);
select sum(value) from temp.kval where key in ('dogs', 'cats');

(6) By AlexJ (CompuRoot) on 2022-01-18 21:59:48 in reply to 2 [link] [source]

You might argue that you should be blocked from creating key/value pairs that cannot be used directly in a query

That's the reason of my report.

(11) By Larry Brasfield (larrybr) on 2022-01-19 02:13:50 in reply to 6 [link] [source]

You might argue that you should be blocked from creating key/value pairs that cannot be used directly in a query

That's the reason of my report.

I understand your belief on this "should", but you merely stated it without arguing it in post #1 (which would mean providing reasons that others should adopt the same belief.)

What is the harm? Would you agree that no documented behavior is violated by the toleration ".parameter set ..." has for keys that cannot be parameters names? What positive good is achieved by changing the behavior to better match your belief?

(13) By AlexJ (CompuRoot) on 2022-01-19 07:10:35 in reply to 11 [link] [source]

What positive good is achieved by changing the behavior to better match your belief?

The Documentation must match implementation.

The .dot command .parameter using the same syntax that used for parameter binding. It even using the same template syntax, but under the hood, it doesn't do any real binding. As I already pointed, SQL parameters in CLI implementation wrongly referencing to unrelated mechanisms of SQL parameter binding.

If there no binding, what the reason to place at first place, as the first sentence in documentation links related to Binding Values To Prepared Statements

This 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."

should be removed from documentation related to CLI implementation of .parameter command because substitution in printf that forms SQL query is not what described in that first sentence of documentation.

(14) By Harald Hanche-Olsen (hanche) on 2022-01-19 13:50:18 in reply to 13 [source]

I fail to see the part of the documentation where it says that the .parameter command uses parameter binding.

What I do see, though, is the claim that when the shell is going to run a query, it will extract available data from the sqlite_parameters table and bind the values to the corresponding parameters in the query.

From a cursory glance at src/shell.c.in, it looks like the function named bind_prepared_stmt performs this task.

Am I wrong about that?

Now, to be sure, due to the implementation of command line parsing in the shell and the .parameter command, using that command is not a way to secure against SQL injection and the like. Possibly, the documentation should contain a warning against that misconception.

(17) By AlexJ (CompuRoot) on 2022-01-19 17:20:49 in reply to 14 [link] [source]

From a cursory glance at src/shell.c.in, it looks like the function named bind_prepared_stmt performs this task.

Am I wrong about that?

Bellow is the code responsible for processing .parameter set. Take a look at line #10 & #20. These aren't parametrized queries and there no any calls to sqlite3_bind_...() functions.

More than that, in line #10 zValue will be evaluated by SQLite engine, so if a value for example would be a "+1-626-123-4567" (a phone number) then resulting value in temp.sqlite_parameters will be -5315 instead of the phone number.

01:    if( nArg==4 && strcmp(azArg[1],"set")==0 ){
02:      int rx;
03:      char *zSql;
04:      sqlite3_stmt *pStmt;
05:      const char *zKey = azArg[2];
06:      const char *zValue = azArg[3];
07:      bind_table_init(p);
08:      zSql = sqlite3_mprintf(
09:                  "REPLACE INTO temp.sqlite_parameters(key,value)"
10:                  "VALUES(%Q,%s);", zKey, zValue);
11:      if( zSql==0 ) shell_out_of_memory();
12:      pStmt = 0;
13:      rx = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
14:      sqlite3_free(zSql);
15:      if( rx!=SQLITE_OK ){
16:        sqlite3_finalize(pStmt);
17:        pStmt = 0;
18:        zSql = sqlite3_mprintf(
19:                   "REPLACE INTO temp.sqlite_parameters(key,value)"
20:                   "VALUES(%Q,%Q);", zKey, zValue);
21:        if( zSql==0 ) shell_out_of_memory();
22:        rx = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
23:        sqlite3_free(zSql);
24:        if( rx!=SQLITE_OK ){
25:          utf8_printf(p->out, "Error: %s\n", sqlite3_errmsg(p->db));
26:          sqlite3_finalize(pStmt);
27:          pStmt = 0;
28:          rc = 1;
29:        }
30:      }
31:      sqlite3_step(pStmt);
32:      sqlite3_finalize(pStmt);
33:    }

Possibly, the documentation should contain a warning against that misconception.

That's what I trying to explain without success. Larry already added a little clarification in upcoming release, but still, the first sentence in documentation related to CLI implementation of SQL praremeter still use reference to prepared statements while in fact there no parameter binding in the code that processing .parameter set command.

(20) By Larry Brasfield (larrybr) on 2022-01-19 22:16:49 in reply to 14 [link] [source]

Am I wrong about that?

No, and your code-reading skills are fine.

Now, to be sure, due to the implementation of command line parsing in the shell and the .parameter command, using that command is not a way to secure against SQL injection and the like. Possibly, the documentation should contain a warning against that misconception.

This recent revision, (soon to be pushed to sqlite.org), indicates as much without any paternalistic, "and this enables SQL injection." That is blindingly obvious, to the point of baby-talk redundancy, from the revised text.

I will consider mentioning there that those who might allow sight-unseen values to reach the ".parameter set" meta-command should probably be aware of the -safe invocation option. I am also considering giving .parameters a "text" subcommand which would provide the quotes to guard against SQL injection. (Normally, mere quoting is not the best or easiest way to do that, but the way it is done in the library's printf implementation is pretty fool-proof. Hence I am disinclined to go to the trouble of created a bindable parameter and binding it for the .parameter command.)

(16) By Larry Brasfield (larrybr) on 2022-01-19 15:04:10 in reply to 13 [link] [source]

The Documentation must match implementation.

You have a different view of this than the project team members do. What we strive to accomplish with the documentation is that: it be useful to SQLite users, allowing them to use the API and tools; it state what can be relied upon; and that it not misstate what can be relied upon. Subsidiary to those objectives are clarity, organization and discoverability. It is most definitely not an objective (among the members) that every possible outcome of using SQLite be predictable by recourse to the documentation. Some behaviors are left unspecified, some are explicitly said to yield undefined results, and some are left open so that future enhancements may be made without contradicting a previous version of the documentation.

It is my position that the documentation on the .parameter meta-command, as recently modified, accurately states what ".parameter set ..." does. Further, I contend that this documentation is enough to allow users to employ that meta-command and does not misstate anything about it. If you contend otherwise, please support your contention with an argument which includes evidence and reasoning rather than bare assertions or platitudes.

The .dot command .parameter using the same syntax that used for parameter binding.

Each of the sentences I get when trying to convert that into a declarative sentence is false. The .parameter doc does not claim that it uses the same syntax as is used for parameter binding (or for bindable parameters either.)

It even using the same template syntax, but under the hood, it doesn't do any real binding.

No, it does not "use the same template syntax". It supports such use, and that is all that is claimed. While it is true that the .parameter command, by itself, does not do any "real binding", its purpose is to support a concurrently added feature, described alongside the .parameter doc, whereby when the shell is asked to run some SQL, it attempts to match parameter names found in the statement to names found in the temp.sqlite_parameters table, and when a match occurs, binds the associated value to the named parameter. That under-the-hood binding is entirely real, at least inasmuch as software phenomena can be real.

This sentence: "..." should be removed from documentation related to CLI implementation of .parameter command because substitution in printf that forms SQL query is not what described in that first sentence of documentation.

That sentence is critical to understanding the present purpose of having a temp.sqlite_parameters table and a .parameter meta-command to populate it. Hence, it is entirely appropriate in that context. Further, there is nothing in that documentation (as modified for clarity) that is misleading. And, in my opinion, none of it is superfluous or could be removed without undermining the purposes of the documentation.

The printf substitution that ".parameter set ..." does is an independent fact from what parameter binding does (when SQL is submitted for execution.) However, given the close relationship between ".parameter set ..." does (populating temp.sqlite_parameters) and what the shell's binding process does (reading temp.sqlite_parameters), putting these facts in close proximity and describing the relationship between those actions is entirely consistent with the above-stated documentation purposes.

(18) By AlexJ (CompuRoot) on 2022-01-19 17:34:06 in reply to 16 [link] [source]

If you contend otherwise, please support your contention with an argument which includes evidence and reasoning rather than bare assertions or platitudes.

I believe I already explained and proved that .parameter set doesn't use parameter binding and anyone who know just little about C, can see it in source code, so that's my point, if there no "real parameters binding", then there no reason to reference to such binding in documentation.

(19) By Larry Brasfield (larrybr) on 2022-01-19 18:28:05 in reply to 18 [link] [source]

I believe I already explained and proved that .parameter set doesn't use parameter binding and anyone who know just little about C, can see it in source code, so that's my point, if there no "real parameters binding", then there no reason to reference to such binding in documentation.

I know a little, perhaps more, about C, and hence have not ever argued that ".parameter set" does any binding. However, parameter binding is the reason for the temp.sqlite_parameters table to exist. ".parameter set ..." is useful for loading or modifying that table (as its doc clearly explains.) And, in case anybody wonders (as they should), "Why would I care what is in that table?", or "Of what use is the .parameter meta-command?", the answer is right there too, stating that SQL submitted to the shell is subjected to a parameter binding process where named parameters whose names are in that table are bound to the value associated with the name in that table.

This is not rocket science, and I cannot fathom why it has become a point of noise and confusion. As far as I can tell, the document was clear a week ago, and with the text added to ward off a natural but false assumption, has become clearer since.

You have not made the case that the documentation is wrong, unclear, or lacking any essential element(s) regarding the ".parameter set" feature. You have thrown out a flurry of assertions to that effect, which have tended to show what confusion arose in your own thinking. (And that has done some good, resulting in the above-linked clarification.) But saying you have already explained and proved something which nobody contends and is not claimed in the documentation does nothing to dissuade me that "the documentation on the .parameter meta-command, as recently modified, accurately states what ".parameter set ..." does." To accomplish that would take: (1) A specific citation to some particular text in the documents; (2) A statement as to what is false about that text; and (3) why and upon what evidence you believe it to be false.

At this point, I intend to ignore any further vague assertions on that subject.

there no reason to reference to such binding in documentation.

In a different post in this thread, I have explained in detail why that reference to the binding process is present within the ".parameter" doc. See the last two paragraphs, where the reason is elaborated. If you continue to believe "there is no reason" after that, and wish to convince me of it, you will need to explain how else anybody reading that document would guess why the ".parameter" meta-command exists without reading a lot of (C) code.

I can understand that your reading of the document may have led you to false belief about the effect of ".parameter set", because of that brief discussion of binding. However, at this point, I am not prepared to attribute that confusion to document deficiency without a clear argument making that case.

(8) By anonymous on 2022-01-19 01:31:57 in reply to 1 [link] [source]

Exactly what names are valid is a bit slippery.

sqlite> .mode table
sqlite> .headers on
sqlite> .parameter set ?1 "'one'"
sqlite> .parameter set ?01 "'zero one'"
sqlite> select ?1,?01;
+-----+-----+
| ?1  | ?01 |
+-----+-----+
| one | one |
+-----+-----+
sqlite> select ?01,?1;
+----------+----------+
|   ?01    |    ?1    |
+----------+----------+
| zero one | zero one |
+----------+----------+

(9) By Larry Brasfield (larrybr) on 2022-01-19 01:48:33 in reply to 8 [link] [source]

That's clever, but orthogonal to the tolerance of ".param set ...".

Per the parameter syntax (or spelling) rules both ?01 and ?1 refer to the same parameter. (The 1st in this case.) The fact that these number pseudo-names show up differently in sqlite3_bind_parameter_name(...) returns depending on which appeared first is a curiosity, perhaps even a quirk, of the SQLite library. But both names are valid.

That the shell's binding logic is (blessedly) ignorant of this sort of confusion is a fact independent of its toleration of keys that cannot name to-be-bound parameters.

(12) By AlexJ (CompuRoot) on 2022-01-19 06:47:56 in reply to 9 [link] [source]

Per the parameter syntax (or spelling) rules both ?01 and ?1 refer to the same parameter.

The reference link you provided is related to Binding Values To Prepared Statements, but as I already pointed multiple times, - .parameter .dot command IS NOT related to binding at all and this mismatch between documentation and real implementation leads to confusion and wrong assumptions.

You using common, well established definition of parameter related to binding in SQL with particular SQLite3 CLI implementation, which ARE NOT real bind parameters because there no calls to any sqlite3_bind...() functions. You even using the same syntax related to binded parameters in .parameter templates, such as

?
?NNN
:VVV
@VVV
$VVV  

but real implementation DO NOT using related to this definitions functions: sqlite3_bind...()

This:

  "REPLACE INTO temp.sqlite_parameters(key,value)"
  "VALUES(%Q,%s);", zKey, zValue);

is NOT binding in my humble opinion.

.parameter .dot command feature can not be referenced to unrelated documentation that's describes "Binding Values To Prepared Statements".

(15) By Larry Brasfield (larrybr) on 2022-01-19 14:18:19 in reply to 12 [link] [source]

Per the parameter syntax (or spelling) rules both ?01 and ?1 refer to the same parameter.

The reference link you provided is related to Binding Values To Prepared Statements, but as I already pointed multiple times, - .parameter .dot command IS NOT related to binding at all and this mismatch between documentation and real implementation leads to confusion and wrong assumptions.

You are replying to a post in which that link and the text resting upon it was entirely responsive to the immediate subject. Neither the link nor my reply was intended to address your "binding" issue raised and reiterated in a separate thread. In a series of posts on subject X, it makes no sense to complain that subject Y is not addressed there.

.parameter .dot command feature can not be referenced to unrelated documentation that's describes "Binding Values To Prepared Statements".

Nonsense. The thrust of my post #9 is to show that the treatment of ?1 and ?01 parameters mentioned in post #8, to which I was responding, is independent of (or "orthogonal to") how ".parameter set ..." behaves. The behavior shown in post #8 is highly related to the document I cited, and the facts related there support my contention in post #9. When I cite a fact X in support of "Y is not an instance of Z", where X helps explain what Y is, to complain that X is unrelated to Z is facetious.

(10) By Keith Medcalf (kmedcalf) on 2022-01-19 02:03:50 in reply to 8 [link] [source]

This result is entirely expected (or should be).

select ?1, ?01;

Allocates a parameter array with length one, the parameter being named "?1". "?01" is a reference to parameter 1. (The 0 is silent).

select ?01, ?1;

Allocates a parameter array with length one, the parameter being named "?01". "?1" is reference to parameter 1 which was named "?01".

The "?" is intended for positional (numbered) parameters. Other characters "@", "$", and ":" are introducers for "named" parameters.

Note that mixing of positional and named parameters works as expected:

.param init
.param set :ijit "'ijit'"
.param set ?1 "'?1'"
select :ijit, ?1;

This allocates one parameter (number 1) with name ":ijit". The parameter "?1" is a numbered parameter that refers to the first (number 1) parameter.