[Owasp-leaders] Stop blaming developers on Sql Injection

Andrea Cogliati andrea.cogliati at owasp.org
Thu Jan 8 10:46:25 EST 2009

It's also worth mentioning that even if you properly use  
PreparedStatement (or the equivalent for other platforms) to call  
Stored Procedures that use string concatenation on server side, the  
query might still be injectable. It's a rare scenario, but I've found  
it in large corporations where the developers and the DB guys don't  
trust each other and don't talk.


On Jan 8, 2009, at 10:01 AM, Jeff Williams wrote:

> 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
> 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
> _______________________________________________
> 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