Tcl - sqlite help needed with my proc....

Author

sqlite help needed with my proc....

Mel

2007-06-24, 8:11 am

What is wrong with this proc and how can i fix it.

Please help
----------------------------------------------------------------------------------

file delete -force test.db

sqlite dbh test.db
dbh eval "create table departments (id int, name char(50))"

########################################
###
#####
########################################
###
proc query {qstr} {
sqlite dbh test.db

if {[catch {set rval [dbh eval $qstr]} e] != 0} {
puts $e
exit
}

puts >>>$rval|$qstr<<<

return $rval
}

query "delete from departments"

set str "Al's automotive"
query "insert into departments values (1000,$str)"

i get :
[color=darkred]
near "'s automotive)": syntax error

billposer@alum.mit.edu

2007-06-24, 8:11 am

On Jun 23, 2:05 pm, Mel <MelHer...@gmail.com> wrote:
> What is wrong with this proc and how can i fix it.
>
> Please help
> ----------------------------------------------------------------------------------
>
> file delete -force test.db
>
> sqlite dbh test.db
> dbh eval "create table departments (id int, name char(50))"
>
> ########################################
###
> #####
> ########################################
###
> proc query {qstr} {
> sqlite dbh test.db
>
> if {[catch {set rval [dbh eval $qstr]} e] != 0} {
> puts $e
> exit
> }
>
> puts >>>$rval|$qstr<<<
>
> return $rval
>
> }
>
> query "delete from departments"
>
> set str "Al's automotive"
> query "insert into departments values (1000,$str)"
>
> i get :
>
> near "'s automotive)": syntax error

sql wants quotes around str to tell it that it is a string.
It works if you write:

query "insert into departments values (1000,\"$str\")"

Nick Hounsome

2007-06-24, 8:11 am

On Jun 24, 3:02 am, billpo...@alum.mit.edu wrote:
> On Jun 23, 2:05 pm, Mel <MelHer...@gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> sql wants quotes around str to tell it that it is a string.
> It works if you write:
>
> query "insert into departments values (1000,\"$str\")"

The immediate cause of your problem is that single quotes are a valid
quoting mechanism for SQL (not just sqlite) so you have an unbalanced
quote. The suggestion to double quote works because double quotes
protect single (and vise versa) but it is not a general solution (e.g.
try it with set str un"balanced'quotes\\n )

This mechanism is never going to work for general strings because you
cannot guarantee the quoting will come out right in the arg string.

One way that will work is to use sqlite's own expansion but then you
need to ensure that the variable is visible in the correct scope:

# ensure that $::str is visible to sqlite
global str

set str "Al's automotive"
# Note curly brackets - db eval expands $::str and (logically) does
the necessary quting
query {insert into departments values (1000,$::str)}

Another way if you didn't want to use globals would be to pass the
variable names in to query and upvar them to themselves.
Then you would call

query {insert into departments values (1000,$str)} str

and query would be

#args is a list of variable names used in the query
proc query {qstr args} {
foreach var $args { upvar 1 $var $var }
...

All very clever but I suspect that the fundamental design is wrong
anyway and that you should reconsider it carefully before using any
technical fixes

Nick

Darren New

2007-06-25, 4:23 am

Mel wrote:
> >>>|delete from departments<<<
> near "'s automotive)": syntax error

Along with all the other advice, be aware that this sort of thing can
lead to what's called an "SQL injection attack". You prompt the user for
a business name to look up, and they type in
Al's automotive
and get that error back. So they type in
Al';delete from departments;
and you're hosed.

--
Darren New / San Diego, CA, USA (PST)
I bet exercise equipment would be a lot more
expensive if we had evolved from starfish.

Mel

2007-06-25, 10:10 pm

On Jun 24, 11:09 am, Darren New <d...@san.rr.com> wrote:
> Melwrote:
>
> Along with all the other advice, be aware that this sort of thing can
> lead to what's called an "SQL injection attack". You prompt the user for
> a business name to look up, and they type in
> Al's automotive
> and get that error back. So they type in
> Al';delete from departments;
> and you're hosed.
>
> --
> Darren New / San Diego, CA, USA (PST)
> I bet exercise equipment would be a lot more
> expensive if we had evolved from starfish.

so how should i avoid getting hosed ? Can you please put me on the
right path ?

thanks for your "very reasonable and sound" advise

Darren New

2007-06-25, 10:10 pm

Mel wrote:
> so how should i avoid getting hosed ? Can you please put me on the
> right path ?

All user input that eventually winds up in your SQL statements needs to
have the proper escaping done, with quotes being doubled or whatever
your SQL interpreter requires. (Usually, there's a routine in the API to
do the escaping for you, somehow.)

A similar problem happens with HTML, where you use a field to prompt
someone for their name, and they type in (say) an <img> tag that points
to their server, so when you display their name to someone else, the bad
guy gets hits on his server. That's "cross-site scripting". You fix it
the same way - escape user input so it's not interpreted as HTML.
Replace < with $lt; for example.

You'll have to get into the details yourself, but the rest of this
thread has pointed you to the places to look.

Hope that helps.

--
Darren New / San Diego, CA, USA (PST)
I bet exercise equipment would be a lot more
expensive if we had evolved from starfish.

Sponsored Links