[Esapi-user] SQL Codecs

Jim Manico jim.manico at owasp.org
Mon Aug 2 21:20:06 EDT 2010


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/20100802/05859d2d/attachment.html 


More information about the Esapi-user mailing list