RE: Lazy sanitizing of data for SQL queries

["Brass, Phil (ISS Atlanta)" <PBrass () iss net>]

Yeah, the problem is querying it.  Every system that wants to query the
data has to base64 encode query parameters.  Furthermore, the encoding
will cause problems with sorting in the database - while it may be the
case that base64 sorts "asciibetically" the same as the unencoded text
(I'm not sure about this), it will not be suitable for case-insensitive
sorting, soundex, etc.

Also, integer fields would have to be stored in the database as encoded
text.  Again, it may be possible to encode the integer values such that
they sort correctly, but then every query tool is going to need to know
how to do this.

While there are really no good "lazy" solutions to SQL injection,
probably the best "lazy" way I know of is to convert your ad-hoc
concatenated queries to prepared statement queries, and this will only
work under the following conditions:
1.  the database object, database driver, and database server implement
prepared statements in such a way that prepared statement parameters are
never seen by the SQL interpreter
2.  Your current queries can be converted to prepared statements
(different platforms have different requirements on where those question
marks can go)

This kind of thing still doesn't protect you from attempts to access
unauthorized data.  For example, if you've got a database of everybody's
orders, and the user should only be allowed to access their order, you
need to make sure the user can't submit, for example, a "view order"
request with somebody else's order number, thus gaining unauthorized
access.

Phil




> -----Original Message-----
> From: HarryM [mailto:harrym () the-group org] 
> Sent: Friday, January 24, 2003 2:10 PM
> To: webappsec () securityfocus com
> Subject: Lazy sanitizing of data for SQL queries
>
>
> I was just thinking about this - Perhaps a good way of lazily 
> sanitising data to be inserted into an SQL query would be to 
> Base64 encode it? You could then decode it before displaying 
> it to the user. Obviously this doesn't prevent XSS attacks, 
> and it would slightly increase the size of the data being 
> stored, but aside from that... Can anyone see a problem with 
> this idea?
>
> HarryM