Bridging The Security Gap

SQL injection in a parameterized world: because we love our dynamic querie

By Dirk May 28, 2013

So, SQL injection.

It has been around for quite a while now
It has worked its way to being one of the biggest baddies on OWASP’s top 10 before
It is working its way to claiming that spot once again in 2013
And, despite all of the world’s efforts to get rid of it with nice solutions like prepared statements,
it just… won’t…  disappear

And, to be quite honest, I don’t think SQL injection will ever completely disappear, as long as (My/MS/T/PLG) SQL itself only allows parameterization of certain subsets of its statements (WHERE clauses, VALUES clauses, etc.).
The reason for this is simple: developers desperately want, crave, even need dynamic SQL statements – statements whose structure and / or referenced tables/columns can change depending on the current state of the application.

Why? So they can make fancy, user friendly applications that allow the user to choose the way their data is or isn’t filtered.
Because users have come to expect being able to customize every part of everything they interact with.

Image1

(Because users are spoiled)

Not too long ago, I was testing an application for a client, and everywhere in the code they were using prepared statements. It was beautiful! Every little bit of database interaction was parameterized.
However, that was mainly because their code was only concerned with security checks and requests with fixed inputs.

The part of the application where the user interaction became more flexible, was the reporting section. This was all done using the open source Business Intelligence Reporting Tool, lovingly called BIRT, or more specifically the BIRT Viewer.

Now, the BIRT Viewer itself (aside from containing a juicy little bug that is still waiting to be reported) only uses prepared statements.
To create a report in BIRT as a developer, you must define all the things that need to be in the report, define the different variables that will be used in generating the report, and give the parameterized SQL statement that BIRT needs to use to get its information from the database.
All of these definitions are stored in a nice XML structure file on the server and used whenever a user wants to generate a report based on that report-definition.

Sounds great so far, right? Yes it does. And if that’s is where it stopped, things would be fine. Things would be great. Things would probably even be secure.
But things would most certainly not offer the dynamism that users always scream for and things would be abandoned. (Just for fun, try googling ‘”dynamic query” BIRT. Nearly 7000 hits!). So BIRT had to find a way to make their reports and the corresponding queries dynamic / customizable, and so they did…

Image2

Can you see where this going?

Yep, BIRT included support for scripting languages (i.e. javascript) in its report files, allowing developers to customize the content of report definition files, including any and all SQL statements it defines, at runtime, perhaps even based on user input.

So now the people who create the report definitions suddenly have to think about nasty things like input validation and sanitation. And perhaps they have never heard  of these things, because they are designers instead of programmers. Or maybe they are programmers, but because they are working on report definitions, they don’t feel like they’re ‘coding’ and forget to apply their best practices.
Or maybe they have actually tried to apply the best practices, but find themselves unable to apply them as they are now working within the confines of javascript, which doesn’t know anything about SQL and so does not provide any support for prepared statements or magic functions like super_sql_ultimate_escape_string(),  that might help with encoding.

So they either end up reinventing the wheel of input sanitation (probably thinking that a square should be good enough); copying it from some blog post that somebody on birt-exchange found on google (which for some reason is shaped like a triangle); or they just forget about wheels all together and decide to wing it.

And sure enough, when going through the report definitions there were the familiar string substitution techniques that told me that, yes, even in a development environment where prepared statements are the norm, SQL injection is going to find a way.

 

The offending (anonymized) snippet from one of the report definitions:

<method name=”beforeOpen”><![CDATA[
if (params["optional_filter"] ) {
this.queryText = this.queryText.replace(“/*PLACEHOLDER_FILTER*/”, ” AND (optional_filter= ‘” + params["optional_filter"] +”‘)”);
}; 

}]]>
</method>
<xml-property name=”queryText”><![CDATA[
SELECT some_column, some_other_column FROM data_table WHERE (main_argument = ?) /*PLACEHOLDER_FILTER*/
</xml-property>

 

And that’s it, game over.

Because it doesn’t matter how well you have parameterized the majority of your queries; if there is still even one query that remains injectable, you’re gonna have a bad time.

Image3 Happy Hacking!

The post SQL injection in a parameterized world: because we love our dynamic querie appeared first on ITQ.