[Owasp-leaders] Stop blaming developers on Sql Injection

Marcin Wielgoszewski marcinw86 at gmail.com
Thu Jan 8 11:28:44 EST 2009


Funny you mention that Andrea, because that was the exact scenario
that lead me to discover a SQL injection vulnerability in a particular
web service I wrote about here:  http://www.tssci-security.com/?p=439

On Thu, Jan 8, 2009 at 10:46 AM, Andrea Cogliati
<andrea.cogliati at owasp.org> wrote:
> 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.
>
> Andrea
>
> 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
>> 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
>>
>> _______________________________________________
>> 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