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

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<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&amp;password=db_password&amp;stringtype=unspecified"

thank's