On Jan 17, 2008, at 10:23 PM, Zbigniew Baniewski wrote:
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.
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
[EMAIL PROTECTED]
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
