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

Only later the values for startWeek and endWeek are determined. When
they are determined, is there a possibility to have the right values
substituted, or should I work with format?

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

Cecil Westerhof <Cecil@decebal.nl> wrote:
> I have the following code:
> set selectWeek {
> SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
> , SUM(dayViews) AS WeekTotals
> FROM dayViews
> WHERE WeekNo >= ${startWeek}
> AND WeekNo <= ${endWeek}
> GROUP BY WeekNo
> ORDER BY WeekNo
> }
>
> Only later the values for startWeek and endWeek are determined. When
> they are determined, is there a possibility to have the right values
> substituted, or should I work with format?

Read up on the [subst] command. It is intended for this type of
substituting within strings.

On Monday, October 23, 2023 at 11:14:08 PM UTC+2, Cecil Westerhof wrote:
> I have the following code:
> set selectWeek {
> SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
> , SUM(dayViews) AS WeekTotals
> FROM dayViews
> WHERE WeekNo >= ${startWeek}
> AND WeekNo <= ${endWeek}
> GROUP BY WeekNo
> ORDER BY WeekNo
> }
>
> Only later the values for startWeek and endWeek are determined. When
> they are determined, is there a possibility to have the right values
> substituted, or should I work with format?

There might be another option depending on the package you are using. I know from tdom[1] and Pgtcl[2] that support variable injection.

[1] http://tdom.org/index.html/file?name=doc/domNode.html&ci=tip "At every place where the XPath syntax allows a node test there could be a Tcl variable reference (in any form), just the leading $ replaced with %."
[2] https://flightaware.github.io/Pgtcl/html/pgtcl-pgexecprepared.html "args consists of zero or more optional values that can be inserted, unquoted, into the SQL statement using $-style substitution."

Actually, take care about the input - you could introduce a dormant bug or a security hole using Tcl's subst or format!

HTH
Martin

On 23/10/2023 22:12, Cecil Westerhof wrote:
> I have the following code:
> set selectWeek {
> SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
> , SUM(dayViews) AS WeekTotals
> FROM dayViews
> WHERE WeekNo >= ${startWeek}
> AND WeekNo <= ${endWeek}
> GROUP BY WeekNo
> ORDER BY WeekNo
> }
>
> Only later the values for startWeek and endWeek are determined. When
> they are determined, is there a possibility to have the right values
> substituted, or should I work with format?
>
How are you executing the SQL?

If you're using tdbc, then you can, and should, change your WHERE clause to

WHERE WeekNo >= :startWeek
AND WeekNo <= :endWeek

tdbc will substitute the values of the startWeek and endWeek variables
when it executes the query if they are in scope.If they aren't in scope,
then you can specify the values by passing a dictionary when you
evaluate the SQL text.

IMHO, If you aren't using tdbc, then you should be.

Alan

Alan Grunwald <nospam.nurdglaw@gmail.com> writes:

> On 23/10/2023 22:12, Cecil Westerhof wrote:
>> I have the following code:
>> set selectWeek {
>> SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
>> , SUM(dayViews) AS WeekTotals
>> FROM dayViews
>> WHERE WeekNo >= ${startWeek}
>> AND WeekNo <= ${endWeek}
>> GROUP BY WeekNo
>> ORDER BY WeekNo
>> }
>> Only later the values for startWeek and endWeek are determined. When
>> they are determined, is there a possibility to have the right values
>> substituted, or should I work with format?
>>
> How are you executing the SQL?

'package require sqlite3' and 'db eval …'

> If you're using tdbc, then you can, and should, change your WHERE clause to
>
> WHERE WeekNo >= :startWeek
> AND WeekNo <= :endWeek

That was it. I knew it could be done, but did not remember how.

Should use the tclsh/sqlite combo more often.

> tdbc will substitute the values of the startWeek and endWeek variables
> when it executes the query if they are in scope.If they aren't in scope,
> then you can specify the values by passing a dictionary when you
> evaluate the SQL text.
>
> IMHO, If you aren't using tdbc, then you should be.

I am not using tdbc. What are the advantages of it?

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

Cecil Westerhof <Cecil@decebal.nl> wrote:
> Alan Grunwald <nospam.nurdglaw@gmail.com> writes:
>> How are you executing the SQL?
>
> 'package require sqlite3' and 'db eval …'
>
>
>> If you're using tdbc, then you can, and should, change your WHERE
>> clause to
>>
>> WHERE WeekNo >= :startWeek
>> AND WeekNo <= :endWeek
>
> That was it. I knew it could be done, but did not remember how.

Sqlite's Tcl API supports three different ways to "substitute" (which
one to use depends upon exactly what it is that is in the variable).

The docs at: https://sqlite.org/tclsqlite.html explain all three in the
description of the "eval" method.

Rich <rich@example.invalid> writes:

> Cecil Westerhof <Cecil@decebal.nl> wrote:
>> Alan Grunwald <nospam.nurdglaw@gmail.com> writes:
>>> How are you executing the SQL?
>>
>> 'package require sqlite3' and 'db eval …'
>>
>>
>>> If you're using tdbc, then you can, and should, change your WHERE
>>> clause to
>>>
>>> WHERE WeekNo >= :startWeek
>>> AND WeekNo <= :endWeek
>>
>> That was it. I knew it could be done, but did not remember how.
>
> Sqlite's Tcl API supports three different ways to "substitute" (which
> one to use depends upon exactly what it is that is in the variable).
>
> The docs at: https://sqlite.org/tclsqlite.html explain all three in the
> description of the "eval" method.

I will look into it. But I am quite sure that in the past I also use
':'. But it is never wrong to learn a little bit more.

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

Cecil Westerhof schrieb am Montag, 23. Oktober 2023 um 23:14:08 UTC+2:
> I have the following code:
> set selectWeek {
> SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
> , SUM(dayViews) AS WeekTotals
> FROM dayViews
> WHERE WeekNo >= ${startWeek}
> AND WeekNo <= ${endWeek}
> GROUP BY WeekNo
> ORDER BY WeekNo
> }
>
> Only later the values for startWeek and endWeek are determined. When
> they are determined, is there a possibility to have the right values
> substituted, or should I work with format?
>
> --
> Cecil Westerhof
> Senior Software Engineer
> LinkedIn: http://www.linkedin.com/in/cecilwesterhof

Maybe to stupid, but why you don’t use quotes for your sql statement?

Am 24.10.2023 um 22:53 schrieb Mole Cool:
> Cecil Westerhof schrieb am Montag, 23. Oktober 2023 um 23:14:08 UTC+2:
>> I have the following code:
>> set selectWeek {
>> SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
>> , SUM(dayViews) AS WeekTotals
>> FROM dayViews
>> WHERE WeekNo >= ${startWeek}
>> AND WeekNo <= ${endWeek}
>> GROUP BY WeekNo
>> ORDER BY WeekNo
>> }
>>
>> Only later the values for startWeek and endWeek are determined. When
>> they are determined, is there a possibility to have the right values
>> substituted, or should I work with format?
>>
>> --
>> Cecil Westerhof
>> Senior Software Engineer
>> LinkedIn: http://www.linkedin.com/in/cecilwesterhof
>
>
> Maybe to stupid, but why you don’t use quotes for your sql statement?

If your variable data contains quotes, it must be handled.
That is, how SQL Injection attacks work.

"select * from table where CustID = '$CustId'"

Then, CustId comes from a web server and contains "q'; delete table;
select '"
and you get executed:
"select * from table where CustID = 'q'; delete table; select ''"

To avoid this, use the ":" syntax. It cares about correct quoting.

Take care,
Harald

Mole Cool <molecool1058@googlemail.com> writes:

> Cecil Westerhof schrieb am Montag, 23. Oktober 2023 um 23:14:08 UTC+2:
>> I have the following code:
>> set selectWeek {
>> SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
>> , SUM(dayViews) AS WeekTotals
>> FROM dayViews
>> WHERE WeekNo >= ${startWeek}
>> AND WeekNo <= ${endWeek}
>> GROUP BY WeekNo
>> ORDER BY WeekNo
>> }
>>
>> Only later the values for startWeek and endWeek are determined. When
>> they are determined, is there a possibility to have the right values
>> substituted, or should I work with format?
>>
>> --
>> Cecil Westerhof
>> Senior Software Engineer
>> LinkedIn: http://www.linkedin.com/in/cecilwesterhof
>
>
> Maybe to stupid, but why you don’t use quotes for your sql statement?

That is not going to work, because the variables do not exist yet, so
I will get:
WHERE WeekNo >=
AND WeekNo <=

That is not going to do what I want.

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

Cecil Westerhof schrieb am Mittwoch, 25. Oktober 2023 um 12:14:08 UTC+2:
> Mole Cool <moleco...@googlemail.com> writes:
>
> > Cecil Westerhof schrieb am Montag, 23. Oktober 2023 um 23:14:08 UTC+2:
> >> I have the following code:
> >> set selectWeek {
> >> SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
> >> , SUM(dayViews) AS WeekTotals
> >> FROM dayViews
> >> WHERE WeekNo >= ${startWeek}
> >> AND WeekNo <= ${endWeek}
> >> GROUP BY WeekNo
> >> ORDER BY WeekNo
> >> }
> >>
> >> Only later the values for startWeek and endWeek are determined. When
> >> they are determined, is there a possibility to have the right values
> >> substituted, or should I work with format?
> >>
> >> --
> >> Cecil Westerhof
> >> Senior Software Engineer
> >> LinkedIn: http://www.linkedin.com/in/cecilwesterhof
> >
> >
> > Maybe to stupid, but why you don’t use quotes for your sql statement?
> That is not going to work, because the variables do not exist yet, so
> I will get:
> WHERE WeekNo >=
> AND WeekNo <=
>
> That is not going to do what I want.
> --
> Cecil Westerhof
> Senior Software Engineer
> LinkedIn: http://www.linkedin.com/in/cecilwesterhof

You should put the query in braces like {select ... where WeekNo >= $weekno}

HTH
rene

* Rich <rich@example.invalid>
| Cecil Westerhof <Cecil@decebal.nl> wrote:
| > Alan Grunwald <nospam.nurdglaw@gmail.com> writes:
| >> How are you executing the SQL?
| >
| > 'package require sqlite3' and 'db eval …'
| >
| >> If you're using tdbc, then you can, and should, change your WHERE
| >> clause to
| >>
| >> WHERE WeekNo >= :startWeek
| >> AND WeekNo <= :endWeek
| >
| > That was it. I knew it could be done, but did not remember how.
>
| Sqlite's Tcl API supports three different ways to "substitute" (which
| one to use depends upon exactly what it is that is in the variable).
>
| The docs at: https://sqlite.org/tclsqlite.html explain all three in the
| description of the "eval" method.

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?

R'

package req sqlite3
sqlite3 db :memory:

proc sqlTolist {db sqltext} {
db eval $sqltext value {
set row [list]
foreach col $value(*) {
lappend row $value($col)
}
lappend rows $row
}
set rows [linsert $rows 0 $value(*)]
return $rows
}

proc sqlWbindTolist {db sqltext startWeek endWeek} {
db eval $sqltext value {
set row [list]
foreach col $value(*) {
lappend row $value($col)
}
lappend rows $row
}
set rows [linsert $rows 0 $value(*)]
return $rows
}

set sqltext {
CREATE TABLE dayViews (
dayDate TEXT NOT NULL DEFAULT (date('now', '-1 day')),
dayViews INTEGER NOT NULL,
PRIMARY KEY(dayDate)
)
;}
db eval $sqltext

#Testdata
proc testdata {{days 30}} {
set sDate [clock add [clock scan now] -$days day]
for {set i 0 } { $i < [expr {2 * $days }]} { incr i } {
lappend r [list [clock format [clock add $sDate $i day ] -format "%Y-%m-%d" ] 10]
}
return $r
} set sqltext {INSERT INTO dayViews VALUES(:dayDate,:dayViews)}
foreach a [testdata] {
set dayDate [lindex $a 0]
set dayViews [lindex $a 1]
db eval $sqltext
}

set sqltext {
SELECT MIN(WeekNo), MAX(WeekNo) FROM (
SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
, COUNT(dayViews) AS Days
FROM dayViews
GROUP BY WeekNo
)
WHERE Days = 7
;}
puts "1. $sqltext\n [sqlTolist db $sqltext]"

set sqltext {
SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
, SUM(dayViews) AS WeekTotals
FROM dayViews
WHERE WeekNo >= :startWeek
AND WeekNo <= :endWeek
GROUP BY WeekNo
ORDER BY WeekNo
;}
puts "2. $sqltext\n [sqlWbindTolist db $sqltext 40 43]"

set sqltext {
SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
, COUNT(dayViews) AS Days
FROM dayViews
GROUP BY WeekNo
;}
puts "3. $sqltext\n [sqlTolist db $sqltext]"

set sqltext {
select *
FROM dayViews
;}
puts "4. $sqltext\n [sqlTolist db $sqltext]"

On 24/10/2023 15:16, Cecil Westerhof wrote:
> Alan Grunwald <nospam.nurdglaw@gmail.com> writes:
>

Big snip

>> IMHO, If you aren't using tdbc, then you should be.
>
> I am not using tdbc. What are the advantages of it?
>

I wasn't aware of how to handle variable substitution using the sqlite
package; I guess that when I was using (before I discovered tdbc) I
didn't understand the security risks of constructing SQL text that
includes user-supplied values - referred to later in this thread.

To my mind the main point of using tdbc is that it's fairly agnostic
about the underlying database, so that if one day I need to switch my
code from running over sqlite to running over, say, MySQL I only (!?)
need to worry about inconsistencies in the SQL dialect; everything else
"just works" after I change the TCL command used to create the database
connection.

Alan

Ralf Fassel <ralfixx@gmx.de> writes:

> * Rich <rich@example.invalid>
> | Cecil Westerhof <Cecil@decebal.nl> wrote:
> | > Alan Grunwald <nospam.nurdglaw@gmail.com> writes:
> | >> How are you executing the SQL?
> | >
> | > 'package require sqlite3' and 'db eval …'
> | >
> | >> If you're using tdbc, then you can, and should, change your WHERE
> | >> clause to
> | >>
> | >> WHERE WeekNo >= :startWeek
> | >> AND WeekNo <= :endWeek
> | >
> | > That was it. I knew it could be done, but did not remember how.
>>
> | Sqlite's Tcl API supports three different ways to "substitute" (which
> | one to use depends upon exactly what it is that is in the variable).
>>
> | The docs at: https://sqlite.org/tclsqlite.html explain all three in the
> | description of the "eval" method.
>
> 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 I do not use a 'local' list, but a 'global' string variable:
db1 eval ${commandStr}

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

greg schrieb am Mittwoch, 25. Oktober 2023 um 17:43:29 UTC+2:
> package req sqlite3
> sqlite3 db :memory:
>
> proc sqlTolist {db sqltext} {
> db eval $sqltext value {
> set row [list]
> foreach col $value(*) {
> lappend row $value($col)
> }
> lappend rows $row
> }
> set rows [linsert $rows 0 $value(*)]
> return $rows
> }
>
> proc sqlWbindTolist {db sqltext startWeek endWeek} {
> db eval $sqltext value {
> set row [list]
> foreach col $value(*) {
> lappend row $value($col)
> }
> lappend rows $row
> }
> set rows [linsert $rows 0 $value(*)]
> return $rows
> }
>

#bug in procs $db

proc sqlTolist {db sqltext} {
$db eval $sqltext value {
set row [list]
foreach col $value(*) {
lappend row $value($col)
}
lappend rows $row
}
set rows [linsert $rows 0 $value(*)]
return $rows
}

proc sqlWbindTolist {db sqltext startWeek endWeek} {
$db eval $sqltext value {
set row [list]
foreach col $value(*) {
lappend row $value($col)
}
lappend rows $row
}
set rows [linsert $rows 0 $value(*)]
return $rows
}

# this proc replaced proc sqlWbindTolist
proc sqlTolist {db sqltext args} {
if {[llength $args] != "0" && ([llength $args] %2) == 0} {
dict for {k v } $args {set $k $v}
} elseif {([llength $args] %2) != 0} {
puts "Bind Error args: $args"
return error
}
$db eval $sqltext value {
set row [list]
foreach col $value(*) {
lappend row $value($col)
}
lappend rows $row
}
set rows [linsert $rows 0 $value(*)]
return $rows
}

#But tdbc is much better

rene <r.zaumseil@gmail.com> writes:

> Cecil Westerhof schrieb am Mittwoch, 25. Oktober 2023 um 12:14:08 UTC+2:
>> Mole Cool <moleco...@googlemail.com> writes:
>>
>> > Cecil Westerhof schrieb am Montag, 23. Oktober 2023 um 23:14:08 UTC+2:
>> >> I have the following code:
>> >> set selectWeek {
>> >> SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
>> >> , SUM(dayViews) AS WeekTotals
>> >> FROM dayViews
>> >> WHERE WeekNo >= ${startWeek}
>> >> AND WeekNo <= ${endWeek}
>> >> GROUP BY WeekNo
>> >> ORDER BY WeekNo
>> >> }
>> >>
>> >> Only later the values for startWeek and endWeek are determined. When
>> >> they are determined, is there a possibility to have the right values
>> >> substituted, or should I work with format?
>> >>
>> >> --
>> >> Cecil Westerhof
>> >> Senior Software Engineer
>> >> LinkedIn: http://www.linkedin.com/in/cecilwesterhof
>> >
>> >
>> > Maybe to stupid, but why you don’t use quotes for your sql statement?
>> That is not going to work, because the variables do not exist yet, so
>> I will get:
>> WHERE WeekNo >=
>> AND WeekNo <=
>>
>> That is not going to do what I want.
>> --
>> Cecil Westerhof
>> Senior Software Engineer
>> LinkedIn: http://www.linkedin.com/in/cecilwesterhof
>
> You should put the query in braces like {select ... where WeekNo >= $weekno}

I already tried that (before asking here), but that does not work.

But it is solved by using :startWeek and :endWeek.

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

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.

Schelte.

Alan Grunwald <nospam.nurdglaw@gmail.com> writes:

> On 24/10/2023 15:16, Cecil Westerhof wrote:
>> Alan Grunwald <nospam.nurdglaw@gmail.com> writes:
>>
>
> Big snip
>
>>> IMHO, If you aren't using tdbc, then you should be.
>> I am not using tdbc. What are the advantages of it?
>>
>
> I wasn't aware of how to handle variable substitution using the sqlite
> package; I guess that when I was using (before I discovered tdbc) I
> didn't understand the security risks of constructing SQL text that
> includes user-supplied values - referred to later in this thread.
>
> To my mind the main point of using tdbc is that it's fairly agnostic
> about the underlying database, so that if one day I need to switch my
> code from running over sqlite to running over, say, MySQL I only (!?)
> need to worry about inconsistencies in the SQL dialect; everything else
> "just works" after I change the TCL command used to create the database
> connection.

It is almost impossible that I will something else as SQLite.
(In this case anyway.)

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

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.

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

On 26/10/2023 00:38, Cecil Westerhof wrote:
> 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.
>
It is wrong if you want sqlite to replace the variables. It only handles
$variable, :variable, and @variable. Not ${variable}.

Schelte.

Schelte <nospam@wanadoo.nl> writes:

> On 26/10/2023 00:38, Cecil Westerhof wrote:
>> 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.
>>
> It is wrong if you want sqlite to replace the variables. It only handles
> $variable, :variable, and @variable. Not ${variable}.

That works if you use it as a 'local' list, but that is not what I am
doing. I have the query in a variable and use:
db eval ${sqlCommand}

Then $variable does not work, but :variable does.
And at the same time it is secure.

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

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. Schelte is right that your habit to write
variable references as ${startWeek} is your problem here. See this code:

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
}]
catch {db eval {
SELECT value FROM something
WHERE key = ${thiskey}
}} errMsg
puts $errMsg

#difference variable and array
# dbcmd eval ?-withoutnulls? sql ?array-name? ?script?

set a 0
set {a} 2

set value(a) 3
set value({a}) 4

puts "[tcl::unsupported::representation $a]"
puts "[tcl::unsupported::representation ${a}]"
puts "[tcl::unsupported::representation $value(a)]"
puts "[tcl::unsupported::representation $value({a})]"

puts $a
puts ${a}

parray value

if {0} {
value is a pure string with a refcount of 2, object pointer at 0x55fe45c14160, string representation "2"
value is a pure string with a refcount of 2, object pointer at 0x55fe45c14160, string representation "2"
value is a pure string with a refcount of 2, object pointer at 0x55fe45c14190, string representation "3"
value is a pure string with a refcount of 2, object pointer at 0x55fe45c14100, string representation "4"
2 2
value(a) = 3
value({a}) = 4

}

On 26/10/2023 02:15, Cecil Westerhof wrote:
> That works if you use it as a 'local' list, but that is not what I am
> doing. I have the query in a variable and use:
> db eval ${sqlCommand}
>
> Then $variable does not work, but :variable does.
> And at the same time it is secure.
>
As Rolf mentioned, :variable and $variable are equivalent in sqlite. So
everywhere where substitution of :variable works, $variable will also work:

% package require sqlite3
3.39.3
% sqlite3 db :memory:
% set sqlCommand1 {select :variable}
select :variable
% set sqlCommand2 {select $variable}
select $variable
% set sqlCommand3 {select ${variable}}
select ${variable}
% set variable bla
bla
% db eval ${sqlCommand1}
bla
% db eval ${sqlCommand2}
bla
% db eval ${sqlCommand3}
unrecognized token: "$"

Schelte