Thread: 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
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
On Sat, Aug 6, 2011 at 1:49 PM, Craig Ringer wrote:
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.
On 6/08/2011 5:28 PM, Anton Moiseev wrote:I hope that's not an example from your code... because storing passwords in clear text is almost always an *INCREDIBLY* bad idea.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");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.password won't be found.
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