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