Re: citext data type does not work with JDBC PreparedStatement?

[Anton Moiseev <benderamp@xxxxxxxxx>]

On Sat, Aug 6, 2011 at 1:49 PM, Craig Ringer wrote:

On 6/08/2011 5:28 PM, Anton Moiseev wrote:

Hi,

I wanted to have case-insensitive user names in my db and found that
citext postgresql data type
(http://www.postgresql.org/docs/8.4/interactive/citext.html) is exactly
what I need.

So I have added to my db and it seemed to work fine when query db from
command line interface, but when I run it from java prepared statement,
things do not work as expected.

For example, I have user name 'Leon' stored in the db and want to get
password for him.

If I execute query in sql console:
SELECT password FROM users WHERE name = 'leon';

I hope that's not an example from your code... because storing passwords in clear text is almost always an *INCREDIBLY* bad idea.

If at all possible, hash the password using a salted hash function, and compare the hashes when checking passwords.

This is stripped test case, password column would return hashed password, and actually the authentication code is located in JDBCRealm in apache tomcat which also uses PreparedStatement mostly in the same way as above.
 

        final String query = "SELECT password FROM users WHERE name = ?";
        final PreparedStatement stmt =
dbConnection.prepareStatement(query);
        stmt.setString(1, "leon");

password won't be found.

What's the query sent to the backend? Enable query and parameter logging in postgresql.conf, re-run your test, and see what the server receives.

Yes, the problem was in how jdbc sends the final query to server. I have tried to look at the postgres JDBC source code and found the following place in AbstractJdbc2Statement:

    public void setString(int parameterIndex, String x) throws SQLException
    {
        checkClosed();
        setString(parameterIndex, x, (connection.getStringVarcharFlag() ? Oid.VARCHAR : Oid.UNSPECIFIED));

    }

So it seems that it might do some kind of casting strings to varchar if connection.getStringVarcharFlag() is true (and citext docs say that casting column to text during comparison would result case-sensitive operation).

So, in AbstractJdbc2Connection I have found that "stringtype" connection property value affects this flag - "unspecified" would result bindStringAsVarchar = false;

So I have tried to create connection in this way:

final Properties props = new Properties();
        props.put("user", "db_user");
        props.put("password", "db_pass");
        props.put("stringtype", "unspecified");

Class.forName("org.postgresql.Driver");
Connection dbConnection = DriverManager.getConnection("jdbc:postgresql://db_host/db_name", props);

and case-insensitive comparison started to work even with jdbc statement setString(xxx).

And also just for the record - tomcat JDBCRealm would work in case-insensitive way the the following connectionURL param provided in context.xml:

connectionURL="jdbc:postgresql://db_host/db_name?user=db_user&password=db_password&stringtype=unspecified"

thank's