Prev: Possible bug in Tcl or Windows or Tcl on Windows
Next: tcl/tk wish for seismic data display
From: r sampson on 6 Sep 2009 12:01
Gerald is correct.

After substitution is completed, the expression that goes to the
database looks like.

INSERT INTO article (id, detail) VALUES ($id, 'Hello 'World'')

The single-quotes are evaluated by the DB so that "Hello " becomes the
value in quotes and "World''" is left over -- invalid SQL.

To correct it, make the initial assignment

set detail "Hello ''World''"






Mel wrote:
> Thanks for you reply. I do however think it is a substitution problem;
> consider the following:
>
> set id 1000
> set detail "Hello 'World'"
>
> set x{
> Jack of all Trades $id, '$deatil " { ['
>
> }
>
> set test [subst $x] ; <<< still fails
>
> There must be a way of not interpreting the string. I hope you
> agree ;-)
>
>
> On Sep 5, 11:44 am, "Gerald W. Lester" <Gerald.Les...(a)cox.net> wrote:
>> Mel wrote:
>>> The following code fails because of the singlequotearound "World". I
>>> fyou look at the query itself, detail is surrounded by singlequote
>>> and therefore it fails on inclusion of any singlequote. How can one
>>> avoid this situation ?
>>> thanks in advance.
>>> set id 1000
>>> set detail "Hello 'World'"
>>> set query {
>>> INSERT INTO article (id, detail) VALUES ($id, '$deatil')
>>> }
>>> set test [subst $query]
>>> dbh eval $test
>> What you are talking about is properly called an SQL Insertion Attack (seehttp://en.wikipedia.org/wiki/SQL_injection).
>>
>> Note -- this has *NOTHING* to do with Tcl.
>>
>> The answer is to use bind/bound parameters. The exact syntax depends on the
>> database extension (or the tcldb that will be in Core in 8.6) you are
>> using which you did not identify.
>>
>> The other option is to do a [stringmap] on detail to substitute two single
>> quotes where ever there is one.
>>
>> --
>> +------------------------------------------------------------------------+
>> | Gerald W. Lester |
>> |"The man who fights for his ideals is the man who is alive." - Cervantes|
>> +-----------------------------------------------------------------------
From: bs on 6 Sep 2009 12:09
On Sep 5, 6:34 am, Mel <melher...(a)gmail.com> wrote:
> The following code fails because of the single quote around "World". I
> fyou look at the query itself, detail is surrounded by single quote
> and therefore it fails on inclusion of any single quote. How can one
> avoid this situation ?
> thanks in advance.
>
> set id     1000
> set detail "Hello 'World'"
>
> set query {
>     INSERT INTO article (id, detail) VALUES ($id, '$deatil')
>
> }
>
> set test [subst $query]
>
> dbh eval $test

Are you using SQLite? If you are, I believe SQLite will do the
substitution (and quoting) for you. So, IIRC, you can just do:

set id 1000
set detail "Hello 'World'"

set query {
INSERT INTO article (id, detail) VALUES ($id, $deatil)

}


dbh eval $query

The {} prevent Tcl from substituting, but when the SQLite parser sees
the $, it does a lookup to see if it's a variable, and if so
substitutes/quotes.

It's been awhile since I used SQLite, but I believe that's how it
works...

HTH,
--brett
First  |  Prev  | 
Pages: 1 2
Prev: Possible bug in Tcl or Windows or Tcl on Windows
Next: tcl/tk wish for seismic data display