Tuesday, March 17, 2009

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.

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


Eric Kerin said...

In the specific case of JDBC, please research the java.sql.PreparedStatement class.
It allows you to define place holders for where data should go, and the JDBC libraries ALL handle making sure the data passed to the setString, setInt,etc methods are only interpreted as data (If they do not do this, the specific JDBC driver is very broken) This method makes SQL injection impossible, with no extra escaping or validation needed by the application programmer.

Every major language/environment that does database access has a similar method I've personally done it in JAVA, .NET, PHP and Perl, but this design pattern has existed for many years, and is prevalent in any platform that does database access.

Eric Kerin said...

Sorry for the double post...

If you use the parameterized queries, and they eliminate the possibility of SQL injection, what is the point of doing "security" parsing at all?

Also, there are benefits to doing queries in this method as well, in many database platforms, it will speed up your application, because the SQL statement being executed is probably run many times over, and therefore can benefit from the query cache implemented by many RDBMs.

Ok, Rant over...

Ken said...

Just because you're using parameterized queries doesn't mean you shouldn't do security parsing of the input.

Parameterized queries indeed take SQL injection out of play, but unvalidated input can still carry other poisonous data, such as cross-site scripting and many many others.

It's still (and always, IMHO) a good idea to do positive input validation, even if you're doing parameterized queries.

Note, you should still do parameterized queries, of course. Just don't expect this to relieve you of the burden of input validation!


Ken van Wyk

Rogan Dawes said...


Of course you should be validating the input for other types of attacks. But this is not the job of the java.sql package.

java.sql provides the PreparedStatement (of which the OP was clearly not aware), which is *the* correct solution for getting untrusted data into and out of a database (along with CallableStatement for stored procedures), and obviates the need to explain whether to use '' or \', etc to escape quotes, as all that is taken care of by the DB-specific java.sql.Driver implementation.

Checking for things like XSS and other malicious input is either app specific or at least app-TYPE specific, and does not belong in the java.sql package.

Jim Sansing said...


I stand corrected (see the update in the original post). I do not believe that the way it works is 'elegant'. It simply treats the SQL injection attempt as data, so my data now has 'select foo from foobar' in the middle of it. But that is far better than nothing.

Regarding your 2nd comment, I count parsing for context as security, and I do not expect the database interface to have any understanding of this. For example, a ports field should be tested for the range 0 - 65,535 so that when the data is used, it does not cause errors which could result in down time or worse. I believe this is similar to the points that Ken and Rogan made.

Later . . . Jim

Smith said...

Jim Sansing,
Thanks for sharing the informative article,it is very helpful,Database security is one of the important aspect of the computer security as databases hold vital and crucial data of the world such as banking detail, company’s data, and so on. Most of the websites and applications which we use are vulnerable to malwares, infectious code, etc. Buffer overflow and SQL injection are the most concerned things in security as most of the old applications and databases are vulnerable where there are millions of them could be affected.

for more information check this link:http://www.eccouncil.org/certification/certified_ethical_hacker.aspx