Variable substitution (TCL & SQLite)

[Zbigniew Baniewski]

I'm choosing desired column names dynamically, then store all the names
in one variable, something like this...

set columns "column1, column2, column3"

The names are chosen in much more complicated way, but the above is just
a variable contents example. I'm trying then to fetch the data like this:

set data [dbcomm eval {SELECT $columns FROM some_table}]

...but it doesn't work. It returns that column names, not the data from
the table. When I replace $columns with just the column names separated by
colons - I mean: directly with $columns contents - there's no problem
anymore. Not sure: the variable substitution won't work the way presented
above? What should I change?

Currently I made a temporary fix, fetching just all (*), then selecting the
data I need - but I don't like it: I'm fetching more, than I needed, and
there's an additional "cleaning" loop, which is slowing down the entire
procedure.

[D. Richard Hipp]

Post by Zbigniew Baniewski
I'm choosing desired column names dynamically, then store all the names
in one variable, something like this...
set columns "column1, column2, column3"
The names are chosen in much more complicated way, but the above is just
set data [dbcomm eval {SELECT $columns FROM some_table}]
...but it doesn't work.

The rules of TCL parsing are that text within {...} gets passed into
its command exactly as written with the outermost {...} removed.
So the command that is running is:

command-name: dbcomm
1st-argument: eval
2nd-argument: SELECT $columns FROM some_table

In other words, the $columns was *not* expanded by TCL. It got
passed down into SQLite. SQLite sees the $columns and thinks
you are dealing with an application variable. Just like a "?" or a
":abc" or "@xyz". Sqlite3_prepare() runs and treats the $columns
token as it would any other SQL variable.

After the statement is prepared. TCL asks the statement: "What
application variables do you have, and what are their names?"
The statement tells TCL that it has a variable named "$columns".
TCL says "I have a variable by that name", and so then TCL
then calls sqlite3_bind_text() to stick the value of the $columns
TCL variable into the SQLite variable. TCL then calls sqlite3_step()
to run the statement.

So, even though $columns looks something like a TCL variable,
it is really an SQLite variable. You can change the value of an
SQLite variable by binding all you want and it is not going to cause
the statement to be reparsed. This is a feature, not a bug - it
prevents
SQL injection attacks.

Notice that the $columns token is an SQLite variable because the
{...} prevented TCL from expanding the text within the {...} and thus
caused the original $columns text, not the expansion of the value
of $columns, to be passed down into SQLite. This is very important.
This is the essence of TCL. This is the part of TCL that people who
have difficulty with TCL don't understand. The rules of TCL are very,
very simple, but they are also different from the rules of Algol-derived
languages like C++ or Python and that difference confuses many
people. TCL is much closer to Lisp. Make sure you understand this
before going on.

Now, suppose you use "..." instead of {...} in the original statement:

dbcomm eval "SELECT $columns FROM some_table"

The rules of TCL are that text within "..." is treated as a single
token, but unlike {...} the text within "..." undergoes variable
expansion and [...] substatement evaluation before being passed
into the command. So the command that gets run is this:

command-name: dbcomm
1st-argument: eval
2nd-argument: SELECT column1, column2, column3 FROM some_table

The second argument gets passed to sqlite3_prepare(). This causes
the statement to be prepared as you want it to be. There are no SQLite
variables in this case. The $columns has been interpreted and expanded
by TCL before the statement is ever sent into SQLite.

You should be very careful using "..." instead of {...} in this context.
If a user can control the content of $columns, then the user might
be able to do something equivalent to:

set columns {null; DELETE FROM critical_table; SELECT null}

The result would be a classic SQL injection attach. The use of {...}
is preferred for this reason. But sometimes, when you want the
text of your SQL statement to be under program control, you want
to use "..." instead. Just be very sure you know exactly what you
are doing whenever you use "..."

D. Richard Hipp
drh-***@public.gmane.org




-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Zbigniew Baniewski]

Post by D. Richard Hipp
The rules of TCL parsing are that text within {...} gets passed into
its command exactly as written with the outermost {...} removed. [..]
In other words, the $columns was *not* expanded by TCL. It got
passed down into SQLite.
SQLite sees the $columns and thinks
you are dealing with an application variable. Just like a "?" or a
token as it would any other SQL variable.

So, TCL sees a variable "columns", whose contents ($columns) - is
"column1, column2, column3".

SQLite sees statement { SELECT $columns FROM some_table }, where $columns is
just "a token", which _can be_ an application variable, if TCL confirms
this.

Post by D. Richard Hipp
After the statement is prepared. TCL asks the statement: "What
application variables do you have, and what are their names?"
The statement tells TCL that it has a variable named "$columns".
TCL says "I have a variable by that name", and so then TCL
then calls sqlite3_bind_text() to stick the value of the $columns
TCL variable into the SQLite variable. TCL then calls sqlite3_step()
to run the statement.

...and now the contents of $columns (SQL variable) in the statement above,
has been replaced with the contents of $columns (TCL variable) - because the
variable names are "compatible". So - that was my assumption - we've got now:

{ SELECT column1, column2, column3 FROM some_table }

...which seems to be quite legal SQL statement.


It seems, there's something I'm still missing(?).

[Michael Schlenker]

Post by Zbigniew Baniewski

Post by D. Richard Hipp
The rules of TCL parsing are that text within {...} gets passed into
its command exactly as written with the outermost {...} removed. [..]
In other words, the $columns was *not* expanded by TCL. It got
passed down into SQLite.
SQLite sees the $columns and thinks
you are dealing with an application variable. Just like a "?" or a
token as it would any other SQL variable.

So, TCL sees a variable "columns", whose contents ($columns) - is
"column1, column2, column3".
SQLite sees statement { SELECT $columns FROM some_table }, where $columns is
just "a token", which _can be_ an application variable, if TCL confirms
this.

Not really true.
If the part is wrapped in {} then for Tcl the $column is just an ordinary
string with no other meaning than foobar, and NO substitution takes place
before the string is passed to SQLite.
SQLite then interprets the string again, like some Tcl commands do
themselfes and defines the semantics for its argument as:

$name is an application variable if it appears in a place where an
application variable is valid.

This is then prepared as a statement and then values are bound by asking the
surrounding Tcl stackframe for the values of the Variables used.

Your usage fails, because the select list is no valid place to use
application variables, so SQLite does expand it there.

http://sqlite.org/c3ref/bind_blob.html has the details for the C side, only
literals can be replaced with application variables, and column names in the
select list are not really literals.

Michael

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Zbigniew Baniewski]

Post by Michael Schlenker
Not really true.
If the part is wrapped in {} then for Tcl the $column is just an ordinary
string with no other meaning than foobar, and NO substitution takes place
before the string is passed to SQLite.

Yes, as I wrote already: I agree.

Post by Michael Schlenker
SQLite then interprets the string again, like some Tcl commands do
$name is an application variable if it appears in a place where an
application variable is valid.
[..]
Your usage fails, because the select list is no valid place to use
application variables, so SQLite does expand it there.

How is the definition of the "valid place"?

[Michael Schlenker]

Post by Zbigniew Baniewski

Post by Michael Schlenker
$name is an application variable if it appears in a place where an
application variable is valid.
[..]
Your usage fails, because the select list is no valid place to use
application variables, so SQLite does expand it there.

How is the definition of the "valid place"?

See the link i provided in my last message, it explains it. Probably drh can
provide more details, or just look at the BNF used by the lemon parser, it
should contain all the gory details.

Michael


-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Simon Davies]

Post by Zbigniew Baniewski
...and now the contents of $columns (SQL variable) in the statement above,
has been replaced with the contents of $columns (TCL variable) - because the
{ SELECT column1, column2, column3 FROM some_table }
...which seems to be quite legal SQL statement.
It seems, there's something I'm still missing(?).

Hi Zbigniew,

Parameter binding is not the same as substitution into a raw sql string.

SQLite prepares an SQL string akin to "SELECT ? FROM some_table;",
then satisfies the place holder by having the text "column1, column2,
column3" ($columns ) bound to the prepared statement. Thus the results
are from executing the SQL
SELECT 'column1, column2, column3' FROM some_table;
which I believe tallies with the results you see.

Rgds,
Simon

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Zbigniew Baniewski]

Thus the results are from executing the SQL
SELECT 'column1, column2, column3' FROM some_table;
which I believe tallies with the results you see.

Thanks: it's probably the best picture, what is exactly going on there.