Re: [sqlite] Protect against SQL injection inside of the database?

[Gerry Snyder]

On Thu, Jul 16, 2009 at 4:20 AM, Michael Schlenker  wrote:

>
>
> This is perfectly safe:
> set result [db1 eval {select * from X where label = $myStringValue and id >
> $compId}]
>
> But you MUST use {} to quote your query and not "", so sqlite gets to do
> the
> substitution (or better said convert things to prepared statements and bind
> values correctly) and not Tcl.


No reason to avoid Tcl. You can also avoid the possibility of Tcl
substitution by using :myStringValue instead of $myStringValue.

Gerry
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Protect against SQL injection inside of the database?

[Adam DeVita]

http://unixwiz.net/techtips/sql-injection.html  is a nice introduction to
sql injection attacks.  (Learning by example) It also explains why binding
is far superior to trying to invent a set of rules and cleaning the input.

.

On Thu, Jul 16, 2009 at 9:01 AM, Michael Schlenker  wrote:

> Fredrik Karlsson schrieb:
> > On Thu, Jul 16, 2009 at 1:20 PM, Michael Schlenker
> wrote:
> >> Your working far too hard. The sqlite Tcl binding already does all thats
> needed.
> >>
> >> This is perfectly safe:
> >> set result [db1 eval {select * from X where label = $myStringValue and
> id >
> >> $compId}]
> >>
> >> But you MUST use {} to quote your query and not "", so sqlite gets to do
> the
> >> substitution (or better said convert things to prepared statements and
> bind
> >> values correctly) and not Tcl.
> >>
> >> Michael
> >
> > Hi Michael,
> >
> > Ok, I can see how this would be the easiest solution, but what I am
> > doing is basically a query builder (maping of comands in a specialized
> > language to pattern subselects in SQL queries). Since the statements
> > can be nested in many different ways, I cannot expect to be able to
> > construct the query and keeping track of variable names to be used in
> > the final substitution, so that I can make use of the built in binding
> > feature of sqlite It is much to much hard work.
> >
>
> I don't think so.
>
> Just use an array to store your values and prefix the names with the
> identifier of your subpattern. Now when you emit your subpattern via
> [format] or some other method just add the appropriate prefixed bind
> variables. Should not be too hard.
>
> > Instead, I think I need to make each part of the query return a
> > complete (not to be evaluated further outside of sqlite) SQL query
> > subselect statement, which is why I think I need to make sure that the
> > values I insert is safe inside an SQL statement myself.
> > Or, do you know of a Tcl command to make strings "SQL safe"? (Sorry
> > for making this into a Tcl question now..)
>
> Its the wrong way. See the mess you get with mysql_real_escape() in PHP and
> you know its wrong.
>
> Michael
>
> --
> Michael Schlenker
> Software Engineer
>
> CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
> Wiener Straße 1-3   Fax:+49 (421) 20153-41
> 28359 Bremen
> http://www.contact.de/  E-Mail: m...@contact.de
>
> Sitz der Gesellschaft: Bremen
> Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
> Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Protect against SQL injection inside of the database?

[Michael Schlenker]

Fredrik Karlsson schrieb:
> On Thu, Jul 16, 2009 at 1:20 PM, Michael Schlenker wrote:
>> Your working far too hard. The sqlite Tcl binding already does all thats 
>> needed.
>>
>> This is perfectly safe:
>> set result [db1 eval {select * from X where label = $myStringValue and id >
>> $compId}]
>>
>> But you MUST use {} to quote your query and not "", so sqlite gets to do the
>> substitution (or better said convert things to prepared statements and bind
>> values correctly) and not Tcl.
>>
>> Michael
> 
> Hi Michael,
> 
> Ok, I can see how this would be the easiest solution, but what I am
> doing is basically a query builder (maping of comands in a specialized
> language to pattern subselects in SQL queries). Since the statements
> can be nested in many different ways, I cannot expect to be able to
> construct the query and keeping track of variable names to be used in
> the final substitution, so that I can make use of the built in binding
> feature of sqlite It is much to much hard work.
> 

I don't think so.

Just use an array to store your values and prefix the names with the
identifier of your subpattern. Now when you emit your subpattern via
[format] or some other method just add the appropriate prefixed bind
variables. Should not be too hard.

> Instead, I think I need to make each part of the query return a
> complete (not to be evaluated further outside of sqlite) SQL query
> subselect statement, which is why I think I need to make sure that the
> values I insert is safe inside an SQL statement myself.
> Or, do you know of a Tcl command to make strings "SQL safe"? (Sorry
> for making this into a Tcl question now..)

Its the wrong way. See the mess you get with mysql_real_escape() in PHP and
you know its wrong.

Michael

-- 
Michael Schlenker
Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: m...@contact.de

Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Protect against SQL injection inside of the database?

[Fredrik Karlsson]

On Thu, Jul 16, 2009 at 1:20 PM, Michael Schlenker wrote:
> Your working far too hard. The sqlite Tcl binding already does all thats 
> needed.
>
> This is perfectly safe:
> set result [db1 eval {select * from X where label = $myStringValue and id >
> $compId}]
>
> But you MUST use {} to quote your query and not "", so sqlite gets to do the
> substitution (or better said convert things to prepared statements and bind
> values correctly) and not Tcl.
>
> Michael

Hi Michael,

Ok, I can see how this would be the easiest solution, but what I am
doing is basically a query builder (maping of comands in a specialized
language to pattern subselects in SQL queries). Since the statements
can be nested in many different ways, I cannot expect to be able to
construct the query and keeping track of variable names to be used in
the final substitution, so that I can make use of the built in binding
feature of sqlite It is much to much hard work.

Instead, I think I need to make each part of the query return a
complete (not to be evaluated further outside of sqlite) SQL query
subselect statement, which is why I think I need to make sure that the
values I insert is safe inside an SQL statement myself.
Or, do you know of a Tcl command to make strings "SQL safe"? (Sorry
for making this into a Tcl question now..)

/Fredrik


-- 
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Protect against SQL injection inside of the database?

[Michael Schlenker]

Fredrik Karlsson schrieb:
> Dear list,
> 
> Sorry for jumping onto the list mainly to ask a question, but it is an
> imporant one, and I have failed to find the answer on Google.
> I am developing a prototype of an application in Tcl using sqlite as
> the  backend database. Now, I know that I will be dealing with quite
> naïve users, who will not think that "!' and simialar characters are
> evil and potentially dangerous in a SQL database context. So, now I
> need to make sure that I am taking all the precautions I can to
> protect the database from evil / naïve users, and since parts of the
> application may be ported to C for speed later, I would prefer as much
> of it to happen in the SQL queries themselves, in order to make sure
> that the behaviour stays constant when porting.
> 
> My currrent strategy is to use a combination of quote() and trim() (as
> blank space at the ends of a string is not important in my
> application). So, for each string value I get from the user, I do
> something similar to
> 
> set out [format {select * from X where label == quote(trim("%s")) and
> id > %d } $myStringValue $compId ]
> 
> (Please ignore the Tcl part if you are not familiar with it.. format
> is basically (almost) sprintf in a new name )

Your working far too hard. The sqlite Tcl binding already does all thats needed.

This is perfectly safe:
set result [db1 eval {select * from X where label = $myStringValue and id >
$compId}]

But you MUST use {} to quote your query and not "", so sqlite gets to do the
substitution (or better said convert things to prepared statements and bind
values correctly) and not Tcl.

Michael

-- 
Michael Schlenker
Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: m...@contact.de

Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Protect against SQL injection inside of the database?

[Pavel Ivanov]

Does Tcl supports binding of parameters to prepared statement?
If yes then do just that and you will not need to do any "quotes" and
think of any "ifs".

Pavel

On Thu, Jul 16, 2009 at 3:49 AM, Fredrik Karlsson wrote:
> Dear list,
>
> Sorry for jumping onto the list mainly to ask a question, but it is an
> imporant one, and I have failed to find the answer on Google.
> I am developing a prototype of an application in Tcl using sqlite as
> the  backend database. Now, I know that I will be dealing with quite
> naïve users, who will not think that "!' and simialar characters are
> evil and potentially dangerous in a SQL database context. So, now I
> need to make sure that I am taking all the precautions I can to
> protect the database from evil / naïve users, and since parts of the
> application may be ported to C for speed later, I would prefer as much
> of it to happen in the SQL queries themselves, in order to make sure
> that the behaviour stays constant when porting.
>
> My currrent strategy is to use a combination of quote() and trim() (as
> blank space at the ends of a string is not important in my
> application). So, for each string value I get from the user, I do
> something similar to
>
> set out [format {select * from X where label == quote(trim("%s")) and
> id > %d } $myStringValue $compId ]
>
> (Please ignore the Tcl part if you are not familiar with it.. format
> is basically (almost) sprintf in a new name )
>
> So, my questions are now:
>
> 1) Can I feel safe that the string value is now "safe" (to some
> degree) regarding SQL injection?
> 2) Have I done something that will prevent me from matching values I
> really want to match by altering the original string value?
> 3) Is the integer value reasonably secure, or shouls something be done
> for that too (and then, what?)
>
> Sorry for these questions, but I would rather dot all the i:s before
> moving on in the application development. I have seen before how
> creative naïve users can be when it comes to making applications crash
> due to unforseen actions. :-)
>
> Of course, any input in this would be greatly appreciated.
>
> /Fredrik
>
> --
> "Life is like a trumpet - if you don't put anything into it, you don't
> get anything out of it."
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Protect against SQL injection inside of the database?

[Fredrik Karlsson]

Dear list,

Sorry for jumping onto the list mainly to ask a question, but it is an
imporant one, and I have failed to find the answer on Google.
I am developing a prototype of an application in Tcl using sqlite as
the  backend database. Now, I know that I will be dealing with quite
naïve users, who will not think that "!' and simialar characters are
evil and potentially dangerous in a SQL database context. So, now I
need to make sure that I am taking all the precautions I can to
protect the database from evil / naïve users, and since parts of the
application may be ported to C for speed later, I would prefer as much
of it to happen in the SQL queries themselves, in order to make sure
that the behaviour stays constant when porting.

My currrent strategy is to use a combination of quote() and trim() (as
blank space at the ends of a string is not important in my
application). So, for each string value I get from the user, I do
something similar to

set out [format {select * from X where label == quote(trim("%s")) and
id > %d } $myStringValue $compId ]

(Please ignore the Tcl part if you are not familiar with it.. format
is basically (almost) sprintf in a new name )

So, my questions are now:

1) Can I feel safe that the string value is now "safe" (to some
degree) regarding SQL injection?
2) Have I done something that will prevent me from matching values I
really want to match by altering the original string value?
3) Is the integer value reasonably secure, or shouls something be done
for that too (and then, what?)

Sorry for these questions, but I would rather dot all the i:s before
moving on in the application development. I have seen before how
creative naïve users can be when it comes to making applications crash
due to unforseen actions. :-)

Of course, any input in this would be greatly appreciated.

/Fredrik

-- 
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users