[Esapi-user] SQL Codecs

Joel Fessler jfessler at denimgroup.com
Mon Aug 2 18:03:39 EDT 2010


Ok, thanks for the info.

Joel Fessler

From: Wong, Bernie [mailto:Bernie_Wong at stercomm.com]
Sent: Monday, August 02, 2010 4:56 PM
To: Jim Manico; August Detlefsen; Joel Fessler
Cc: ESAPI-Developers; esapi-user at lists.owasp.org
Subject: RE: [Esapi-user] SQL Codecs

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> [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<mailto: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<mailto: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<mailto:Esapi-user at lists.owasp.org>
https://lists.owasp.org/mailman/listinfo/esapi-user



_______________________________________________

Esapi-user mailing list

Esapi-user at lists.owasp.org<mailto: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<mailto: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/88124ac1/attachment-0001.html 


More information about the Esapi-user mailing list