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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Cecil Westerhof <[email protected]> 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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Alan Grunwald <[email protected]> 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Cecil Westerhof <[email protected]> wrote:

Alan Grunwald <[email protected]> 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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Rich <[email protected]d> writes:

Cecil Westerhof <[email protected]> wrote:

Alan Grunwald <[email protected]> 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Mole Cool <[email protected]> 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Cecil Westerhof schrieb am Mittwoch, 25. Oktober 2023 um 12:14:08 UTC+2:

Mole Cool <[email protected]> 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

* Rich <[email protected]d>
| Cecil Westerhof <[email protected]> wrote:
| > Alan Grunwald <[email protected]> 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'

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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]"

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 24/10/2023 15:16, Cecil Westerhof wrote:

Alan Grunwald <[email protected]> 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Ralf Fassel <[email protected]> writes:

* Rich <[email protected]d>
| Cecil Westerhof <[email protected]> wrote:
| > Alan Grunwald <[email protected]> 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

rene <[email protected]> writes:

Cecil Westerhof schrieb am Mittwoch, 25. Oktober 2023 um 12:14:08 UTC+2:

Mole Cool <[email protected]> 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Alan Grunwald <[email protected]> writes:

On 24/10/2023 15:16, Cecil Westerhof wrote:

Alan Grunwald <[email protected]> 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 26/10/2023 00:38, Cecil Westerhof wrote:

Schelte <[email protected]> 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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Schelte <[email protected]> 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Schelte <[email protected]> writes:

On 26/10/2023 00:38, Cecil Westerhof wrote:

Schelte <[email protected]> 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Cecil Westerhof <[email protected]> writes:

Schelte <[email protected]> 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

#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

}

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Rolf Ade <[email protected]> writes:

Cecil Westerhof <[email protected]> writes:

Schelte <[email protected]> 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

The Tcl sqlite3 package (from tclsqlite3.c) takes everything after the $ including the enclosing braces, and passes it to Tcl_GetVar2Ex as the variable name.

The Tcl interpreter uses Tcl_ParseVarName which checks if the character after the $ is a "{", and if it is takes everything between the matching braces as the variable name.

Dave B

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

[email protected] writes:

The Tcl sqlite3 package (from tclsqlite3.c) takes everything after the $ including the enclosing braces, and passes it to Tcl_GetVar2Ex as the variable name.

The Tcl interpreter uses Tcl_ParseVarName which checks if the character
after the $ is a "{", and if it is takes everything between the matching braces as the variable name.

This is concerning the thread I started?

I would find it logical if it would work this way. If at one place
$variable is the same as ${variable}, I would expect it everywhere to
be the case.
But sadly it is not.
Tho show it I use this code:
puts "Before first select"
db eval { SELECT 37 } {}
set testVal 37
puts "Before second select"
db eval { SELECT $testVal } {}
puts "Before third select"
db eval { SELECT ${testVal} } {}
puts "After selects"

When I run it I get:
Before first select
Before second select
Before third select
unrecognized token: "$"
while executing
"db eval { SELECT ${testVal} } {}"
(file "/home/cecil/bin/ytWeek.tcl" line 52)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 10/26/2023 9:31 AM, Cecil Westerhof wrote:

[email protected] writes:

The Tcl sqlite3 package (from tclsqlite3.c) takes everything after the $
including the enclosing braces, and passes it to Tcl_GetVar2Ex as the
variable name.

The Tcl interpreter uses Tcl_ParseVarName which checks if the character
after the $ is a "{", and if it is takes everything between the matching
braces as the variable name.

This is concerning the thread I started?

I would find it logical if it would work this way. If at one place
$variable is the same as ${variable}, I would expect it everywhere to
be the case.
But sadly it is not.
Tho show it I use this code:
puts "Before first select"
db eval { SELECT 37 } {}
set testVal 37
puts "Before second select"
db eval { SELECT $testVal } {}
puts "Before third select"
db eval { SELECT ${testVal} } {}
puts "After selects"

When I run it I get:
Before first select
Before second select
Before third select
unrecognized token: "$"
while executing
"db eval { SELECT ${testVal} } {}"
(file "/home/cecil/bin/ytWeek.tcl" line 52)

If you enclose your sqlite3 statement inside of braces, then tcl doesn't parse anything inside the braces, except to keep a level count of braces so it can find the final closing brace (with \{ or \} not counted).

That's it, everything else is passed on to sqlite3 as is. See rule 6 of the 12. So, it's up to sqlite3 to deal with the $ however it chooses to. And it does not (appear) to support the ${var} format that tcl does. So, it's not tcl's fault, since sqlite3
has it's own language. It supports $, :, and @ however.

Since sqlite3 statements are generally encoded as tcl words that begin and end with {}'s, tcl $ substitution does not occur. To get ${var} handled the sqlite3 statement would need to be in double quotes (assuming it has some spaces) and then tcl would be
doing the $ substitution before handing it off to sqlite3.


note for the curious:

Actually, it's Tcl_ParseVarName that handles $var, ${var}, $() or $ (as just text). The code has a comment block which explains it. It's kinda complex, since var can have namespace characters using :: and empty array names have explicit code to handle
those cases.

However, the manual entry for Tcl_ParseVarName (still) doesn't mention all the possibilities, and only gives 2 examples, one of a scalar variable on one as an array. I don't know if that's a manual bug, or just a desire to not overburden the reader. The $
{var} form is mentioned (only) in the tcl rule 8 of the 12 afaik.

The tcl function Tcl_GetVar2Ex is called with a pointer to the variable or array name already separated from any $.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

et99 schrieb am Donnerstag, 26. Oktober 2023 um 19:30:18 UTC+2:

On 10/26/2023 9:31 AM, Cecil Westerhof wrote:

clt.to:

The Tcl sqlite3 package (from tclsqlite3.c) takes everything after the $ >> including the enclosing braces, and passes it to Tcl_GetVar2Ex as the
variable name.

The Tcl interpreter uses Tcl_ParseVarName which checks if the character >> after the $ is a "{", and if it is takes everything between the matching >> braces as the variable name.

This is concerning the thread I started?

I would find it logical if it would work this way. If at one place $variable is the same as ${variable}, I would expect it everywhere to
be the case.
But sadly it is not.
Tho show it I use this code:
puts "Before first select"
db eval { SELECT 37 } {}
set testVal 37
puts "Before second select"
db eval { SELECT $testVal } {}
puts "Before third select"
db eval { SELECT ${testVal} } {}
puts "After selects"

When I run it I get:
Before first select
Before second select
Before third select
unrecognized token: "$"
while executing
"db eval { SELECT ${testVal} } {}"
(file "/home/cecil/bin/ytWeek.tcl" line 52)

If you enclose your sqlite3 statement inside of braces, then tcl doesn't parse anything inside the braces, except to keep a level count of braces so it can find the final closing brace (with \{ or \} not counted).

That's it, everything else is passed on to sqlite3 as is. See rule 6 of the 12. So, it's up to sqlite3 to deal with the $ however it chooses to. And it does not (appear) to support the ${var} format that tcl does. So, it's not tcl's fault, since

sqlite3 has it's own language. It supports $, :, and @ however.


from
https://www.sqlite.org/draft/tokenreq.html

Variables are used as placeholders in SQL statements for constant values that are to be bound at start-time.
H40310: SQLite shall recognize as a VARIABLE token the a question-mark (u003f) followed by zero or more NUMERIC characters.
A "parameter name" is defined to be a sequence of one or more characters that consists of ALPHANUMERIC characters and/or dollar-signs (u0025) intermixed with pairs of colons (u003a) and optionally followed by any sequence of non-zero, non-WHITESPACE
characters enclosed in parentheses (u0028 and u0029).

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Cecil Westerhof <[email protected]> writes:

[email protected] writes:

The Tcl sqlite3 package (from tclsqlite3.c) takes everything after the $
including the enclosing braces, and passes it to Tcl_GetVar2Ex as the
variable name.

The Tcl interpreter uses Tcl_ParseVarName which checks if the character
after the $ is a "{", and if it is takes everything between the matching
braces as the variable name.

This is concerning the thread I started?

Yes. It explains why sqlite3 does not work in this respect as you
expect.

I would find it logical if it would work this way. If at one place
$variable is the same as ${variable}, I would expect it everywhere to
be the case.
But sadly it is not.

But it is.

In code evaluated by the Tcl interpreter (citing you) "$variable is the
same as ${variable}"

But the argument given to [db eval arg] isn't evaluated by the Tcl
interpreter. It is processed by the database command created by the
extension command sqlite3.

A database command created by sqlite3 expects in this case a SQL expression in sqlite3 SQL dialect. Of course this outstandig excellent piece of
software provides a way to "inject" string literals and in a secure way.
But this commands expect the syntax $variable (or :variable) and see ${variable} as invalid syntax. That's OK. Every command is free to
interpret its arguments.

The "injection attack" - think not only about bad guys but also
about bad luck, it is a data driven bug - is a problem for every
embedded language. As Dave wrote, the Tcl API provides functions to help extension writers so solve this (Tcl_ParseVarName, Tcl_ParseVar). I give
an example with tDOM below (the embedded query language here is XPath)
were in the embedded language $variable is the same as ${variable}.

That all said this remains to be a quirk more to your habit to prefer ${variable} over $variable.

rolf

package require tdom

set xml {
<doc>
<elem key="foo">foovalue</elem>
<elem key="bar">barvalue</elem>
</doc>
}

dom parse $xml doc
set thiskey bar
puts [$doc selectNodes {string(/doc/elem[@key=$thiskey])}]
puts [$doc selectNodes {string(/doc/elem[@key=${thiskey}])}]

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 10/26/2023 10:39 AM, greg wrote:

et99 schrieb am Donnerstag, 26. Oktober 2023 um 19:30:18 UTC+2:

On 10/26/2023 9:31 AM, Cecil Westerhof wrote:

clt.to:

The Tcl sqlite3 package (from tclsqlite3.c) takes everything after the $ >>>> including the enclosing braces, and passes it to Tcl_GetVar2Ex as the
variable name.

The Tcl interpreter uses Tcl_ParseVarName which checks if the character >>>> after the $ is a "{", and if it is takes everything between the matching >>>> braces as the variable name.

This is concerning the thread I started?

I would find it logical if it would work this way. If at one place
$variable is the same as ${variable}, I would expect it everywhere to
be the case.
But sadly it is not.
Tho show it I use this code:
puts "Before first select"
db eval { SELECT 37 } {}
set testVal 37
puts "Before second select"
db eval { SELECT $testVal } {}
puts "Before third select"
db eval { SELECT ${testVal} } {}
puts "After selects"

When I run it I get:
Before first select
Before second select
Before third select
unrecognized token: "$"
while executing
"db eval { SELECT ${testVal} } {}"
(file "/home/cecil/bin/ytWeek.tcl" line 52)

If you enclose your sqlite3 statement inside of braces, then tcl doesn't parse anything inside the braces, except to keep a level count of braces so it can find the final closing brace (with \{ or \} not counted).

That's it, everything else is passed on to sqlite3 as is. See rule 6 of the 12. So, it's up to sqlite3 to deal with the $ however it chooses to. And it does not (appear) to support the ${var} format that tcl does. So, it's not tcl's fault, since

sqlite3 has it's own language. It supports $, :, and @ however.

from
https://www.sqlite.org/draft/tokenreq.html

Variables are used as placeholders in SQL statements for constant values that are to be bound at start-time.
H40310: SQLite shall recognize as a VARIABLE token the a question-mark (u003f) followed by zero or more NUMERIC characters.
A "parameter name" is defined to be a sequence of one or more characters that consists of ALPHANUMERIC characters and/or dollar-signs (u0025) intermixed with pairs of colons (u003a) and optionally followed by any sequence of non-zero, non-WHITESPACE

characters enclosed in parentheses (u0028 and u0029).



Note that sqlite3 is not just a tcl extension. When one reads the extraordinary testing procedures used, it is clear that the developers are looking at use far beyond just tcl.

So sqlite3 doesn't support every language's idiosyncratic syntax such as tcl's ${var} form.

And this form was most likely intended (only) to extend the $ operator to variable name construction not normally found in most languages, such as punctuation characters and the invisible name ${}. It was to have $var as a shortcut for [set var].

And I am quite dismayed that there is actually special purpose code to handle invisible array variable names. And partly because of this, the jimtcl extension of $(expression) for [expr {expression}] will most likely be rejected for inclusion in tcl 9.0.

And $(expression) solves the long standing problem of bracing expressions, not to mention its more readable and writable form.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Cecil Westerhof <[email protected]> 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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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}'
"]

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Rolf Ade <[email protected]> writes:

Cecil Westerhof <[email protected]> writes:

[email protected] writes:

The Tcl sqlite3 package (from tclsqlite3.c) takes everything after the $ >>> including the enclosing braces, and passes it to Tcl_GetVar2Ex as the
variable name.

The Tcl interpreter uses Tcl_ParseVarName which checks if the character
after the $ is a "{", and if it is takes everything between the matching >>> braces as the variable name.

English is not my first language. But the above gave me the idea that
also for SQLite $var and ${var} should work.

But I am going to use :var, so it is no biggy.

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

* Cecil Westerhof <[email protected]>
| 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'

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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 <[email protected]> wrote:

Harald Oehlmann <[email protected]> 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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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: !

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

Mole Cool <[email protected]> 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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)