How to pass PreparedStatement setArray for character array

Question

I have some JDBC code as follows:

String selectSQL = "SELECT * FROM DBUSER WHERE USER_ID = ? and PASSWORD = ?";

Integer userId = 1000;
char[] passwordString = new char[] { 't', 'e', 's', 't' };

PreparedStatement preparedStatement = dbConnection.prepareStatement(selectSQL);
preparedStatement.setInt(1, 1001);
preparedStatement.setArray(2,... ??? // how to do this part?

// execute select SQL statement
ResultSet rs = preparedStatement.executeQuery();

How do I call preparedStatement.setArray to set the second parameter in the query? I don't want to use a string parameter here to protect the password.

Note I am using Hypersonic DB but plan to move to MySql if this is useful.

Answer 1

PreparedStatement#setArray received a java.sql.Array First you should use the JDBC Conncetion's createArrayOf method to create the array, only then you can pass it to setArray.

As the method only accept Object[] you should create an array of Character instead of char.

For example:

Character[] passwordString = new Character[] { 't', 'e', 's', 't' };
Array sqlArray = con.createArrayOf("CHAR", passwordString);
preparedStatement.setArray(2, sqlArray);

Answer 2

If you want to pass an array in prepared statement just call

preparedStatemtn.setArray(index,array);

But what you must assure first is that in your DB the column is also a ARRAY. For more detail please see Aviram Sagal answer.

---

But your base idead it to protect the password.

This solution will protect only from reading the passoword from Java string pool. This is very ratre type of attact anyway. Ans passoword is transmited as plain text.

My sugestion is that instead of complicating the db schema, you should use some benefits of cryptography. Instaed of passing the password value, you should pass password digest.

A simplyfied example of digest function.

public static String getDigest(byte[] password) {

  return new String(Hex.encodeHex(new MessageDigest.getInstance("SHA").digest(password)));

}

Then you store in db the digest with is safe and you use simple string in queries.

Answer 3

Have a look at this code

final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table " + 
        "where search_column IN (SELECT * FROM unnest(?))"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));
final ResultSet rs = statement.executeQuery();
try {
    while(rs.next()) {
        // do some...
    }
} finally {
    rs.close();
}

also please take a look at this article for reference http://people.apache.org/~djd/derby/publishedapi/java/sql/PreparedStatement.html