[Owasp-leaders] Stop blaming developers on Sql Injection

Calderon, Juan Carlos (GE, Corporate, consultant) juan.calderon at ge.com
Thu Jan 8 13:13:03 EST 2009


I have so much ideas and thoughts to share, that I don't know where to start

We still agree that better technology is the solution and that secure by default should be the status quo.

I think we are mixing scenarios I agree with you. It is faster and easier to build an application secure from the ground specially with helpers like ESAPI and it is also true that is very costly to make an insecure application secure. So we have existent applications and new applications.

For fixing application you already mention escaping is not precisely the best option, yet is optimal on this situation given the current status quo. Would calling a escaping function (blacklisting approach) is better than changing one line for another and fix the issue completely? (application side only)

Don't get me wrong I fully support ESAPI and OWASP work I am proud to be part of it. That is why am I working on making ESAPI available to an awfully vulnerable and old technology such as Classic ASP. My proposal is to try to make what we know if correct part of the core of the technologies and fix the technologies to avoid people make mistakes as "even the best hunter miss a prey".

Then I will continue my work and try also to achieve this objective of making a injection fix for classic ASP a one line change. Wish me luck :)

Regards,
Juan Carlos Calderon

PS To the list, I felt I was treated a little like a rookie with some comments, no hard feeling, just a clarification :), I just want to let you know I am aware and familiar with preparedstatement in java, command objects for classic ASP and .NET, parameterized queries in latest versions of PHP, Sql injection in PL/MS SQL code, etc. as I have 8+ years on app sec :) it is just that I obvious them as part of the conversation.

-----Original Message-----
From: Jeff Williams [mailto:jeff.williams at owasp.org] 
Sent: Jueves, 08 de Enero de 2009 09:52 a.m.
To: Calderon, Juan Carlos (GE, Corporate, consultant); owasp-leaders at lists.owasp.org; 'Erlend Oftedal'
Subject: RE: [Owasp-leaders] Stop blaming developers on Sql Injection

I disagree with the premise that writing secure code is necessarily more time consuming or costly. Particularly when you arm developers with powerful security libraries like ESAPI, the savings across the entire SDLC (including training, requirements, design, testing, and deployment) are enormous.
That's not even considering the reduction in risk.

So in your example I would fire both developers and find someone who agrees to write secure code by default (i.e. their standard price is for code that doesn't contain the OWASP Top Ten). I suggest looking at the OWASP Secure Software Contract Annex for guidance on including security in agreements with developers.

Also, a legacy application with lots of dynamic SQL might benefit from using escaping rather than parameterized queries. The only change to the code is to wrap any user data with a call to escapeForOracle(). ESAPI supports these wrappers for Oracle and MySQL currently, and it's fairly easy to add new Codecs to support other databases.
http://code.google.com/p/owasp-esapi-java/source/browse#svn/trunk/src/main/j
ava/org/owasp/esapi/codecs.  Contact me anyone who wants to volunteer!

--Jeff

-----Original Message-----
From: Calderon, Juan Carlos (GE, Corporate, consultant) [mailto:juan.calderon at ge.com]
Sent: Thursday, January 08, 2009 10:26 AM
To: jeff.williams at owasp.org; owasp-leaders at lists.owasp.org; Erlend Oftedal
Subject: RE: [Owasp-leaders] Stop blaming developers on Sql Injection

Yes I am aware of it :)
 
I was thinking on what Tim mentioned in OWASP Linked-in discussion and I totally agree (here is a snip)
--------------------------------------------------------
They (the users/buyers) generally don't care if a product is insecure. As long as it's relatively cheap and does what is says on the tin, then job done.
The issue is not so much with the development community - they will happily code up an application in whatever framework the customer wants. The issue is with the end user for commissioning or buying insecure software in the first place. 
....
 A developer who insists on additional time to write secure code will typically be dropped in favor of a developer who will do the job for half the price. 
---------------------------------------------------------

Changing people is hard, but changing technology is not that hard.
Currently, changing from a on-the-fly to a Prepared statement takes you from
1 line of code to at about 5 per instance. 2 months ago we delivered a report for an application with 1800+ sql injection instances. The effort of changing all of those is huge. 

But if we make can make it easy (like the c-style example I sent) and keep it in a single line of code, it would be more feasible that developers change their coding practices without loosing time in extra lines or create new code using this best practice, also will be relatively easier for automated tools to make the conversion. Do you agree?

Regards,
Juan Carlos Calderon

-----Original Message-----
From: Jeff Williams [mailto:jeff.williams at owasp.org]
Sent: Jueves, 08 de Enero de 2009 09:01 a.m.
To: Calderon, Juan Carlos (GE, Corporate, consultant); owasp-leaders at lists.owasp.org; 'Erlend Oftedal'
Subject: RE: [Owasp-leaders] Stop blaming developers on Sql Injection

JDBC has the PreparedStatement which provides a parameterized API for databases. Highly recommended for virtually all queries. And you have to use the ? placeholders.  If you use PreparedStatement but just concatenate user data into the query it is still injectable.

--Jeff

-----Original Message-----
From: Calderon, Juan Carlos (GE, Corporate, consultant) [mailto:juan.calderon at ge.com]
Sent: Thursday, January 08, 2009 9:48 AM
To: jeff.williams at owasp.org; owasp-leaders at lists.owasp.org; Erlend Oftedal
Subject: RE: [Owasp-leaders] Stop blaming developers on Sql Injection

OK So given the limitation of all the possible options and thinking on a "quick" solution. I guess the conclusion is we should push to enforce a parameterized way that is as easy to use (or as much as possible) than the current way of mixing code and data (AKA on-the-fly Sql).

I will try to implement something in Classic ASP ESAPI. Also I will try to see if JDBC API can be influenced somehow to work this way, if you guys know a good contact that could facilitate the work, please contact me directly. 

I know there are lots of other technologies available, but we have to start with something. It's is good to see that discussion lead to action :)

Lets make our (technology) world better (secure). Anyone else to join the campaign?

Regards,
Juan Carlos Calderon

-----Original Message-----
From: owasp-leaders-bounces at lists.owasp.org
[mailto:owasp-leaders-bounces at lists.owasp.org] On Behalf Of Jeff Williams
Sent: Martes, 06 de Enero de 2009 03:50 p.m.
To: Owasp leaders; Erlend Oftedal
Subject: Re: [Owasp-leaders] Stop blaming developers on Sql Injection

Hi Juan,

The Servlet spec doesn't deal with databases, so it doesn't have anything to say about SQL injection. We're working with them to get output encoding, session cookie protection, CSRF protection, and some other stuff in there.

The problem is mixing code and data together (see http://www.theregister.co.uk/2008/08/01/rich_data_vulnerabilities/).
Parameterized interfaces are one approach.  Output escaping/encoding is a more fragile approach.  And input validation is also helpful but *cannot* stop injection on its own.

The "richer" data gets, the worse this problem will get. When doing threat modeling these days, I consider that *every* piece of data might contain viable code (perhaps encoded). There is no such thing as "data" anymore.
That's why you have to use parameterized interfaces and/or escaping/encoding everywhere to sandbox these little programs.

What can we do?  Encourage EVERY product/technology/spec to provide a parameterized interface (hopefully exclusively).  If they can't do that then at least provide an escape syntax that is simple and easy to use. I find \xHH or \uHHHH (simple Javascript escape) to be easy to parse and unlikely to cause problems.  Note however, that you can't allow the \' and \" type escapes as they can cause parsing problems in hierarchical documents like HTML.

We can also push for better canonicalization technology so that validation can actually work. Web encodings are insane. Using only valid encoding formats for each character, there are about a quadrillion possible encodings of the word "<script>". Really.  If you allow for double encodings (multiple, mixed, or nested) the number is basically infinite.  Double encoding is evil and it must be stopped before it's too late.

--Jeff

-----Original Message-----
From: owasp-leaders-bounces at lists.owasp.org
[mailto:owasp-leaders-bounces at lists.owasp.org] On Behalf Of Calderon, Juan Carlos (GE, Corporate,consultant)
Sent: Tuesday, January 06, 2009 4:20 PM
To: Erlend Oftedal; Owasp leaders
Subject: Re: [Owasp-leaders] Stop blaming developers on Sql Injection

Erlend 

I agree 100% with you, I have used LINQ for some apps and found it very intuitive. This is agreat example of a technology solution. 

So why not making a Object SQL (OSQL) language specification that could be implemented in any technology? Something like this C-style code:

 Result r = db.query("SELECT * FROM user WHERE username=?50s AND password=?50s", username, password);  //(username would be a 50 characters
string)

I don't know if this is the real answer at it just figure it out while replying this email. Or the SQL input validation I mentioned before. 

My point is that we have to push for technology to upgrade itself and make it as easy as it is now and secure by default at the same time. So we reach a new status quo and developer do not have to think or evaluate between making it easy and fast or hard and secure. It would be easy and at the same time inherintely secure. Otherwise we have the battle lost against close deadlines.

So if we want Sql Injection to be gradually out of top 10s of the world a long term solution is a technology solution in my point of view.

Jeff, what was proposed for mitigate Sql Injection for Servlets 3.0 specification?

Regards,
Juan Carlos Calderon

-----Original Message-----
From: Erlend Oftedal [mailto:Erlend.Oftedal at BEKK.no]
Sent: Jueves, 04 de Diciembre de 2008 10:47 a.m.
To: Calderon, Juan Carlos (GE, Corporate, consultant); Owasp leaders
Subject: RE: Stop blaming developers on Sql Injection

Hello Juan

I work as a developer myself. I don't think we can change the DBRMs themselves or the query language for that matter. The query languages themselves are structured, generic and flexible which is probably the reason why they succeeded in the first place.
What we can do, is change the frameworks that we use. Framework developers could make it harder to do the wrong thing, than to do the right. Right now it's the other way around. Doing a query wrong might take one line of code - something like:
        Result r = db.query("SELECT * FROM user WHERE username='" + username
+ "' AND password='" + password "'"); Doing the same using a 
+ parameterized
query often takes four or more lines (define query, define each parameter, execute query) and is generally a bit harder to understand for inexperienced developers.
I think the best approach is to remove the actual problem, which in my opinion is that we are mixing control and data when we are writing SQL. The frameworks should seek to allow us to write our code without having to think about the SQL, and then the SQL can be generated for us.
An interesting example of such a framework is LINQ (Language Integrated
Query) which can be used in the newer versions of the .NET framework. When using LINQ, you can specify the query directly in the programming language, and then parameterized queries are created by the framework:
        User user = from u in user where u.name = username and u.password = password select u; The from...in...where...select can be used with any query language (SQL, LDAP etc.), as long as there is a so called LINQ provider for it (it's possible to implement you own LINQ providers for unsupported query languages).
I like this approach, because it allows you to think about the data as objects directly in the language, instead of stepping into SQL context. This page has a lot of LINQ samples on how to do ordering, grouping, aggregations
etc.: http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx


Best regards
Erlend Oftedal

-----Opprinnelig melding-----
Fra: owasp-leaders-bounces at lists.owasp.org
[mailto:owasp-leaders-bounces at lists.owasp.org] På vegne av Calderon, Juan Carlos (GE, Corporate, consultant)
Sendt: 4. desember 2008 16:06
Til: Owasp leaders
Emne: [Owasp-leaders] Stop blaming developers on Sql Injection

> Hello Leaders
>
> I liked a lot the mentality of Jeff Williams related to our Industry 
> is
not doing enough about security.
>
> We have seen Sql injection for many years now on every top X list of 
> every
single list about common security problems. We have see massive sql injections and robots on the wild and now many people, even not technical is aware of "the problem" but the increasing trend has not changed.
>
> So what it takes to stop this? to train every single old school, 
> expert
and newly graduated and amateur developer? fight back every single misrecomendation on the blogs of the world? I think no, a technology oriented solution is required.
>
> So my proposal today is to change how DBRM process Query languages and 
> do not allow string literals that do not represent an object in the 
> database in the Query string but as attached values. So
>
> Select * from table where id = 'something' when lastdate = 
> #01/02/2007#
>
> Would be an invalid query as 'something' and #01/02/2007# would not be 
> allowed by DBRM processor. Yet
>
> Select * from table where id = anothercolum and field2 = @@identity
>
> is valid, as we are comparing columns vs. columns and vs. internal 
> variables (there are some room for security issues on this area of 
> variables, but I am assuming that would me much less than the current
> threat)
>
> So, we would be forced to use parameterized queries to feed literal 
> values in a DB information request for information or action. With the 
> additional benefit of performance (less probability of another 
> security problem - DoS)
>
> I know the change acceptance is a big deal as well, many existent
applications would break or they will be forced to run old vulnerable version of RBDM until they are migrated, but once this becomes the new status quo we can think on how to use technology to avoid XSS and other security "plagues" in the same technology oriented way.
>
> You might realized this already but this could be applicable to any 
> interpreter in a harder or easier way including LDAP, system 
> functions, XPath,  etc etc
So if the industry is not doing anything should we add something to enforce this on ESAPI?
> Regards,
> Juan Carlos
_______________________________________________
OWASP-Leaders mailing list
OWASP-Leaders at lists.owasp.org
https://lists.owasp.org/mailman/listinfo/owasp-leaders
_______________________________________________
OWASP-Leaders mailing list
OWASP-Leaders at lists.owasp.org
https://lists.owasp.org/mailman/listinfo/owasp-leaders

_______________________________________________
OWASP-Leaders mailing list
OWASP-Leaders at lists.owasp.org
https://lists.owasp.org/mailman/listinfo/owasp-leaders




More information about the OWASP-Leaders mailing list