[Esapi-user] SQL Codecs

Tue Aug 3 15:06:49 EDT 2010

Bernie,

Any thoughts on the solution to this problem? Is the escape character over-ride API enough?

- Jim

On Aug 3, 2010, at 2:43 AM, "Wong, Bernie" <Bernie_Wong at stercomm.com> wrote:

> Jim, thanks.
> 
>  
> 
> By the way, looks like both DB2 and SQL Server allow developers to set their escape characters – see http://stackoverflow.com/questions/700648/escape-percentage-sign-db2-sql and
> 
> http://sqlserver2000.databases.aspfaq.com/how-do-i-search-for-special-characters-e-g-in-sql-server.html for examples.
> 
>  
> 
> Bernie
> 
>  
> 
>  
> 
> From: Jim Manico [mailto:jim.manico at owasp.org] 
> Sent: Monday, August 02, 2010 9:20 PM
> To: Wong, Bernie; 'August Detlefsen'; jfessler at denimgroup.com
> Cc: 'ESAPI-Developers'; esapi-user at lists.owasp.org
> Subject: RE: [Esapi-user] SQL Codecs
> 
>  
> 
> Well said Bernie.  The Oracle escape character, can indeed, be over ridden unique to each SQL statement.
> 
>  
> 
> Perhaps we should expand upon the Encoder to include escape character override?
> 
>  
> 
> Right now we provide only (in ESAPI.encoder())
> 
>  
> 
> String encodeForSQL(Codec codec, String input);
> 
>  
> 
> And we could also provide:
> 
>  
> 
> String encodeForSQL(Codec codec, String input, char escapeCharacter);
> 
>  
> 
> But I don’t like having to construct a Codec each time when we could cache it internal to ESAPI.
> 
>  
> 
> Perhaps we could get vendor specific; I think the clarity is worth it – since it lets us provide vendor specific JavaDoc’s about each API’s use, where the generic API does not let us do that neatly.
> 
>  
> 
> So I’d prefer to see…
> 
>  
> 
> String encodeForOracle(String sqlStatement);
> 
> //
> 
> // Support queries like    
> 
> // select * from users where name = '\’’ or 1=1 –' and password = '$password' and organization like '$org%' escape '\'
> 
> //
> 
> String encodeForOracle(String sqlStatement, char escapeCharacter);
> 
>  
> 
> //Assumes that you cannot over-ride the MySQL escape character
> 
> String encodeForMySQL(String sqlStatement);
> 
>  
> 
> Etc…
> 
>  
> 
> Each vendor seems to require unique function signatures, anyhow.
> 
>  
> 
> - Jim
> 
>  
> 
> From: Wong, Bernie [mailto:Bernie_Wong at stercomm.com] 
> Sent: Monday, August 02, 2010 11:56 AM
> To: Jim Manico; August Detlefsen; jfessler at denimgroup.com
> 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] 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/20100803/73644725/attachment.html 