Re: JDBC PreparedStatements, Java Data Objects/O-R mapping, and SQL Injection

[Jeff Williams @ Aspect]

| Now just to be clear, I think using PreparedStatements is a good
| idea. But since the spec doesn't provide any meaningful protection
| against SQL injection, I think anyone using JDBC ought to do their
| own protection.

I can't see how they would do that. If they start doubling quotes, and the JDBC driver behaves as I would expect, the database suddenly contains strings with double quotes in them.

I haven't read the latest JDBC spec [1], but the 1.4.1 doc for PreparedStatement.setString(int, String) states that:

    Sets the designated parameter to the given Java String value. The     driver converts this to an SQL VARCHAR or LONGVARCHAR value     (depending on the argument's size relative to the driver's limits     on VARCHAR values) when it sends it to the database.

If quotes may be used to do SQL Injection using this method, I think the implementation is _not_ according to the documentation. In that case, as I see it, the driver is not converting the string to a VARCHAR or LONGVARCHAR value: Only parts of the string will be a VARCHAR value, the rest will be SQL terms. Hence, the string is not converted to a VARCHAR (or LONGVARCHAR).

I wouldn't be amazed if I found that a new JDBC driver failed to handle metacharacters, but I don't think the application programmer should care much, other than checking that quotes and any other metacharacter are handled correctly before using the driver. If troublesome characters are not handled correctly, then shame on the driver programmers, IMNSHO.

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related securityfocus.com Links bugtraq certification focus-bsd focus-ids focus-ih focus-linux focus-ms focus-sun focus-unix-other focus-virus forensics incidents libnet linux-secnews ms-secnews pen-test secpapers secprog sectools secureshell security-basics securityjobs sf-news vuln-dev webappsec Request more information about Pantek

I just took a look at the mentioned setString method of the latest (stable) JDBC driver for both MySQL and PostgreSQL. The MySQL version contains this:

    for (i = 0; i < x.length(); ++i)
    {

        char c = x.charAt(i);

        if (c == '\\' || c == '\'' || c == '"')
        {
            B.append((char) '\\');
        }
        B.append(c);

The PostgreSQL version contains this:

    for (i = 0 ; i < x.length() ; ++i)
    {

        char c = x.charAt(i);
        if (c == '\\' || c == '\'')
            sbuf.append((char)'\\');
        sbuf.append(c);

Makes you wonder if they were written by the same person. :) At least, it looks like both handle the metacharacters as I would expect after reading about the VARCHAR conversion in the docs.

Sverre.

[1] I read the JDBC 1.0 spec when I implemented my own JDBC driver for

Do you need more help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related securityfocus.com Links bugtraq certification focus-bsd focus-ids focus-ih focus-linux focus-ms focus-sun focus-unix-other focus-virus forensics incidents libnet linux-secnews ms-secnews pen-test secpapers secprog sectools secureshell security-basics securityjobs sf-news vuln-dev webappsec Request more information about Pantek

    PostgreSQL some years back. The spec left far too much to be     decided by the implementor.

-- 
shh@thathost.com		Computer Geek?  Try my Nerd Quiz
http://shh.thathost.com/	
http://nerdquiz.thathost.com/