Database Security

I saw the following on the webappsec list at Security Focus:

| I've heard this preached before.
|
| Using JDBC properly can help protect against SQL Injection.
|
| What protections does JDBC provide?
|
| Does java encode the input to not be malicious?
|
| I'm curious where in the java source/libraries does jdbc help
| to mitigate malicious input when using jdbc.
|
This preach is applicable for any programming language. It
all depends on how well you have done input & output
validation. As in what input you expect & what input is
malicious for your app. If all goes well you can make SQL
injection very difficult or even impossible . The reason I
say difficult, because it all depends on how well the SQL
injection is crafted. As far as I recollect I don't think
JDBC or for that case even java gives you predefined class
for doing that. But there is quite a possibility that some
one on the internet must have surely written these classes.
--
Taufiq
http://www.niiconsulting.com/products/iso_toolkit.html

I don't disagree with Taufiq's assessment. However, I do disagree with his acceptance of the status quo. I wrote a rant on this blog responding to a complaint that security professionals are not taken seriously. In it, I pointed out that the security industry should promote improving the security climate, not just react to it with solutions 'for a price'. The example I gave was *DBC libraries.

The JDBC package, java.sql, does not supply any security parsing. This is not the real workhorse, but it should at least provide a method for this. Each database supplies a jar that java.sql classes call to access the specific database. This is where security parsing must be handled.

The thing is that parsing input is tricky. The first step is to validate that the input is correct for the column data type. This is reasonably straightforward for simple types like integer and varchar. But the way different databases support binary data and very large fields is not consistent. There is also support for non-standard data, such as PostgreSQL's support for the inet data type.

The JDBC Connection interface includes the getMetaData method, which returns the information supplied by the specific database library, some of which is unique to that database. There are not only differences between databases, it is even possible that there are differences between versions of the same database. This could be an issue for an application because:

Some DatabaseMetaData methods return lists of information in the form of ResultSet objects. Regular ResultSet methods, such as getString and getInt, can be used to retrieve the data from these ResultSet objects.

All unique information must be verified for every version of the database supported. And if you are supporting multiple databases, it is that much more difficult.

The next step is to escape all characters that have special meaning, such as single quote and backslash. But again, each database has its own special characters that must be accounted for, such as ampersand in Oracle, and the E'...' escape pattern in PostgreSQL.

Update: Eric Kerin points out in his comments that the PreparedStatement interface does this, and after some testing I have found that this is the case. My excuse is that there is nothing in the javadoc for the SQL package or the PreparedStatement interface that explains this. Instead the documentation promotes it for optimizing frequently used statements. See my reply below for further responses to comments.

Also, there is a good article on this issue at the Open Web Application Security Project, which I found by googling for java and "sql injection".


The current situation places the responsibility for security on the thousands of application programmers, who must now dig into the internals of the database(s!) on the backend of their applications. If instead, the database development teams provided a parser for each field of data, it would be possible to determine if the input would result in a message, like this one that I was able to create from testing various input sequences:

WARNING: nonstandard use of \' in a string literal

ERROR: relation "foobar" does not exist

STATEMENT: select foo from foobar

I'm still working on parsing that construct and reworking it in a way that does not reject the data out of hand, because it might be a legitimate description of an Event, or possibly a Trigger definition. I am fortunate, because the input is not read from the network. You might not be so lucky.

And before I leave this topic/rant, I must point out that application programmers need to work closely with their DBAs to be sure that permissions are set on tables to allow only as much access as absolutely necessary and no more. If you don't have a DBA and/or maintain the database yourself, you need to become very familiar with the levels of GRANTing access and the use of roles to at least limit the damage when SQL injection attacks succeed. In my own experience, as well as reports from others, the attacks on applications, and databases especially, is continuing to increase.

If anyone is interested in the database security in the Realeyes UI, check out the Database and ValidatorDBForm modules, and then see how they are used in any of the Window*Base.java modules at the UI subversion repository. The ValidatorDBForm class includes the InterfaceAdminExtendedProcessing interface to do extra contextual error checking, which really is the job of the application. There are some pretty good examples of its use in the WindowsRules(Triggers/Actions/Events).java modules.

I'm pretty sure I'm talking for all application developers when I say (as a security guy), "Hey database developers, a little help!"

Later . . . Jim