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

["Sverre H. Huseby" <shh () thathost com>]

[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.

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
    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/