Rolf Ade <rolf@pointsman.de> writes:

> Cecil Westerhof <Cecil@decebal.nl> writes:
>> Schelte <nospam@wanadoo.nl> writes:
>>
>>> On 25/10/2023 16:54, Ralf Fassel wrote:
>>>> These docs state that db1 eval {INSERT INTO t1 VALUES(5,:bigstring)}
>>>> and db1 eval {INSERT INTO t1 VALUES(5,$bigstring)} are equivalent, so
>>>> I wonder why the original approach did not work?
>>>
>>> Because the OP insists on writing his variables as ${startWeek} etc. Had
>>> he used $startWeek, it would have worked.
>>
>> You do not know what you are talking about, that is completely b***t.
>> You may not like how I write my variables, but that does not mean it
>> is wrong.
>
> No reason for strong words.

You are completely right: I should not have let my annoyment let get
the better of me. 😢

When I rewrite the set to:
set selectWeek {
SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
, COUNT(*) AS Days
, SUM(dayViews) AS WeekTotals
FROM dayViews
WHERE WeekNo >= $startWeek
AND WeekNo <= $endWeek
GROUP BY WeekNo
ORDER BY WeekNo
}

Then the following works without a hitch:
db eval ${selectWeek} {

I prefer :startWeek instead of $startWeek, but that is my quirk.

It is no excuse for my reaction, but I was annoyed by for example that
I was told that I should have used:
set selectWeek "
instead of:
set selectWeek {

Which is really wrong.
Once I wrote several posts in which I shared code and someone changed
all my posts from ${variable} to $variable (without changing anything
else). Which I really did not appreciate.

But two wrongs do not make a right.
Next time I need to count to ten, or twenty-five, or …

--
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

Cecil Westerhof <Cecil@decebal.nl> wrote:
>
> Because I do not use a 'local' list, but a 'global' string variable:
> db1 eval ${commandStr}

{} around a variable name does not mean a global variable. Using {}
around the name is just escaping the characters of the name such that
any Tcl metacharacters in the name are not interpreted by the Tcl
interpreter.

Variable
TCL
https://www.tcl.tk/man/tcl/TclCmd/Tcl.html#M12

$name variable substitution yes
${name) variable substitution yes
:name variable substitution no
no difference by $name and ${name}
and
:name is not recognized as a variable

SQLITE
https://www.sqlite.org/draft/tokenreq.html
https://www.sqlite.org/tclsqlite.html

$name variable substitution yes
${name) variable substitution no
:name variable substitution yes
@name variable substitution (yes)
and
difference by $name and ${name}
${name} is not recognized as a variable
:name is recognized as variable

####

Processing
TCL parsed statement to tclstmt => SQLITE parsed tclstmt to sqlitestmt

""
statement in "stmt" then in tclstmt with variablevalue
${name) is by TCL interpreted as variablevalue

{}
statement in {stmt} then in tclstmt with variablename
${name} is not recognized as a variable by SQLITE

#####
Solution:
statement in "stmt" and variable in ''

package require sqlite3
sqlite3 db :memory:
db eval {
CREATE TABLE something(key text, value text);
INSERT INTO something(key, value)
VALUES('foo', 'foovalue');
INSERT INTO something(key, value)
VALUES('bar', 'barvalue')
} set thiskey "bar"

puts [db eval "
SELECT value FROM something
WHERE key = '${thiskey}'
"]

* Cecil Westerhof <Cecil@decebal.nl>
| I prefer :startWeek instead of $startWeek, but that is my quirk.

Not your quirk, IMHO, since in *this* context, :startWeek is actually
to be preferred, since it avoids the evaluation of the variable by TCL
for sure (if for example later edits change the surrounding {} to "").

R'

I don’t get your point, if it gets substituted then there is no different if you use $CustId or :CustId because your data contains an sql statement.

Quoting a bit of the prior article to add context helps everyone
understand what you are referencing. I added back some of Harold's
text below.

Mole Cool <molecool1058@googlemail.com> wrote:
>Harald Oehlmann <wortkarg3@yahoo.com> wrote:
>> If your variable data contains quotes, it must be handled.
>> That is, how SQL Injection attacks work.
>> ...
>> To avoid this, use the ":" syntax. It cares about correct quoting.
>
> I don’t get your point, if it gets substituted then there is no
> different if you use $CustId or :CustId because your data contains an
> sql statement.

The point is that it gets substituted in a different way.

If one does string substitution, i.e.:

set var "purple"
set sql "select item from things where color = '$var';"

Then one has set themselves up for possible SQL Injection attacks. If
the contents of "var" are controllable by someone other than you, the
programmer, then for the above code they could do:

set var "purple'; drop table things;"

And then the substitution would do:

set sql "select item from things where color = '$var';"

producing

select item from things where color = 'purple'; drop table things;

And when the above is sent to your database server it will dutifully
run those two statements, and your "things" table would be gone.

Alternately, using the : syntax, the substitution is not actually
performed as a string substitution. What does to the DB server (or
sqlite driver) is a special sql statement that includes "placeholders"
and separate arguments for each placeholder. So using the : syntax,
the server gets this statement:

select item from things where color = :var

and separately a data item that say ":var" contains "purple'; drop table
things;"

And it executes the statement without string substituting it, so it in
effect runs:

select item from things where color = 'purple''; drop table things;';

And as you likely don't have a color named "purple'; drop table things;"
zero rows return instead of your things table being deleted.

The whole text has a couple of errors and misleading statements!

For example:
select item from things where color = 'purple''; drop table things;';

The statement above will search for "purple'; drop table things;" and will NOT drop the table!

.... where color = :var -- where are the single quotes surrounding :var
Correct subs with ':var' ... where color = 'purple'; drop table things;'

You will get an unrecognized token: !

Quoting part of the prior reply helps everyone. Please do so instead
of removing all the quoted material.

Mole Cool <molecool1058@googlemail.com> wrote:
>
> The whole text has a couple of errors and misleading statements!

Only due to your missunderstandings. Otherwise it is correct.

> For example:
> select item from things where color = 'purple''; drop table things;';
>
> The statement above will search for "purple'; drop table things;" and
> will NOT drop the table!

Correct, which is *exactly* what I said in the previous article:

> select item from things where color = 'purple''; drop table things;';
>
> And as you likely don't have a color named "purple'; drop table things;"
> zero rows return instead of your things table being deleted.

Note the next sentence which you omitted from your reply, and which
ends with "instead of your things table being deleted" -- the meaning
of that ending is exactly the same as: "will NOT drop the table".

> ... where color = :var -- where are the single quotes surrounding :var

They are not needed when using the special substitution rules, because
the substitution does not occur by string interpolation

The :var method is sqlite's variant of the simiar method used by the Postgresql
Tcl api:

https://wiki.tcl-lang.org/page/Quick%2Dstart+guide+to+use+of+PostgreSQL+with+Tcl

Recent version of Pgtcl running with fairly recent version of
PostgreSQL can do variable substitutions, which are pretty cool, and
require less quoting and stuff. Observe...

set statement {insert into peopletable values ($1, $2, $3, $4, $5);}

set result [pg_exec $conn $statement $name $address $city $state $zip]

Note how the SQL statement has no single quotes, and not how the
variables are passed separated to pg_exec as additional arguments.
Sqlite just shortcuts things a little by making their : variant reach
into the Tcl interpreter and retreive the variable instead of making
the programmer write the variables out as extra arguments.

> Correct subs with ':var' ... where color = 'purple'; drop table things;'
> You will get an unrecognized token: !

If you put quotes around :var then you don't invoke Sqlite's special
"reach into the Tcl interpreter and retreive the contents of this
variable" mode, and you query for the literal string ":var", which
would search for the literal string ":var". To invoke the special,
sql-injection safe mode, you don't surround the :var or @var strings
with quotes.