Обсуждение: citext data type does not work with JDBC PreparedStatement?

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';

it returns password ok.

But if I do the query from java in this way:

        final String query = "SELECT password FROM users WHERE name = ?";
        final PreparedStatement stmt = dbConnection.prepareStatement(query);
        stmt.setString(1, "leon");
        final ResultSet resultSet = stmt.executeQuery();
        if(resultSet.next()) {
            System.out.println("password is:" + resultSet.getString(1));
        } else {
            System.out.println("password not found");           
        }


password won't be found. If I change parameter substitution like that:

        final String query = "SELECT password FROM users WHERE name = 'leon'";
        final PreparedStatement stmt = dbConnection.prepareStatement(query);
        final ResultSet resultSet = stmt.executeQuery();
        if(resultSet.next()) {
            System.out.println("password is:" + resultSet.getString(1));
        } else {
            System.out.println("password not found");           
        }

the password would be again returned.

Any ideas why this can happen and how to fix this?

thank's
Anton

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.

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

--
Craig Ringer

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