[Esapi-user] SQL Codecs

Wong, Bernie Bernie_Wong at stercomm.com
Mon Aug 2 17:55:53 EDT 2010


Joel

I believe Oracle may be vulnerable in the following situation assuming
your Oracle codec only escapes the single quote ('). 

 

Oracle by default does not set an escape character by default. However,
given the following dynamic SQL  

    select * from users where name = '$name' and password = '$password'
and organization like '$org%' escape '\' 

 

If I enter value of $name as 

    \' or 1=1 -

 

I am pretty sure the resulting SQL after encoding/escaping would be
evaluated as 

    select * from users where name = '\'' or 1=1 -' and password =
'$password' and organization like '$org%' escape '\'

 

which will make Oracle look for records where name = ' or 1=1

 

Unfortunately, I don't have an Oracle DB handy right now to test that
assertion. I am pretty confident the above would work. 

 

If the assertion above is correct, you can't get around the problem by
encoding the \ as \\ because developers can arbitrarily choose another
escape character. Hence, I could simply use # or ^ as my escape
character. 

 

I am not sure about DB2 or MS SQL. 

 

By the way, I see that MySQL already escapes the backslash. I don't know
if MySQL allows one to choose the escape character.  

 

Bernie

 

From: esapi-user-bounces at lists.owasp.org
[mailto:esapi-user-bounces at lists.owasp.org] On Behalf Of Jim Manico
Sent: Monday, August 02, 2010 5:02 PM
To: August Detlefsen
Cc: ESAPI-Developers; esapi-user at lists.owasp.org
Subject: Re: [Esapi-user] SQL Codecs

 

ANYTIME user data drives the table name or column name of a query, you
have a security anti-pattern. You are revealing the inner workings of
your database schema.

Also, you can NOT Parameterize/Bind column names and/or table names in
Java - you must do whitelist validation against a legal list of table
names as August suggests.

But, this is a path fraught with error. Keep metadata of this nature on
the server! 

- Jim




I've seen cases where other parts of a SQL query were generated
dynamically: table and column names, and especially the ORDER BY clause.
Though, these types of options should be selected from a whitelist and
the SQL hidden from the end-user. 

-August 


On 8/2/10 12:59 PM, Jeff Williams wrote: 

Currently, our encoding model for SQL only supports this quoted
where-clause context.  Do we need a more sophisticated model to support
user-data in *other* contexts within a SQL query? 

  

--Jeff 

  

From: esapi-user-bounces at lists.owasp.org
[mailto:esapi-user-bounces at lists.owasp.org] On Behalf Of Jim Manico
Sent: Monday, August 02, 2010 2:14 PM
To: Joel Fessler
Cc: esapi-user at lists.owasp.org
Subject: Re: [Esapi-user] SQL Codecs 

  

Yes, this is all (I think) you need to encode if user data is placed in
a quoted where-clause context. There are a limited number of 'slots' in
a dynamic SQL statement that user data can go safely. Check out Dave
Wichers SQL cheatsheet for more information:
http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet 


- Jim 


On Aug 2, 2010, at 5:20 AM, Joel Fessler <jfessler at denimgroup.com>
wrote: 

	I am working on implementing codecs to encode for MsSQL, DB2 and
Sybase. The only character that I have found that I need to encode for
is a single quote. Does anyone know of anything else that I need to
encode for or where I can find this out? 

	  

	Joel Fessler 

	  

	_______________________________________________
	Esapi-user mailing list
	Esapi-user at lists.owasp.org
	https://lists.owasp.org/mailman/listinfo/esapi-user 

  
_______________________________________________
Esapi-user mailing list
Esapi-user at lists.owasp.org
https://lists.owasp.org/mailman/listinfo/esapi-user
  

 

-- 
August Detlefsen
CEO/Web Application Architect
CodeMagi, Inc. 
http://www.codemagi.com
 
 
_______________________________________________
Esapi-user mailing list
Esapi-user at lists.owasp.org
https://lists.owasp.org/mailman/listinfo/esapi-user

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://lists.owasp.org/pipermail/esapi-user/attachments/20100802/b88901ee/attachment.html 


More information about the Esapi-user mailing list