Hi, Thanks for your mail! I found and fixed the problem. The reason is: You have used setCharacterStream, which internally creates a CLOB. There is a bug in the conversion from CLOB to BINARY (instead of decoding the hex text, it converts each character as a byte). The workaround is to use PreparedStatement.setString
By the way you don't need to cast to org.h2. classes. just use java.sql.PreparedStatement.setCharacterStream(1, new StringReader(mKey), -1) - this only works for H2 however. Regards, Thomas On Tue, Mar 10, 2009 at 8:56 PM, earthe <[email protected]> wrote: > > I was having trouble getting the encrypt/decrypt functions to work > properly, until I realized that the problem was with the > PreparedStatement call I was using to send the data to DECRYPT. I'm > sure I'm just doing something wrong, but I'm not sure what the 'right > way' is.. I can get the calls to work in the h2 console fine, but > when I run them through my java code, but if I use > PreparedStatement.setCharacterStream(int, new StringReader > (mEncryptedValue)) when doing the decryption, it fails. > > What am I doing wrong with the prepared statement that the encrypted > value is getting garbled on the way in to h2? Code below. > > Thanks for any pointers, > > earth > > Here's my code. Replace "cCrypDataDatabase.getDBConnection()" with a > valid h2 connection object. The output I get from this is: > > original value: this that > decrypted with ? + setCharacterStream:&>?Hq > ???n K? ? > decrypted with '+mEncryptedValue':this that > > ----------------- > > public void proofOfFailure() { > try { > String mKey = "aaa"; > String mValue = "this that"; > String mCommand = "CALL ENCRYPT('AES', rawtohex(?), > STRINGTOUTF8 > (?))"; > org.h2.jdbc.JdbcPreparedStatement mPreparedStatement1 = > (org.h2.jdbc.JdbcPreparedStatement) > cCrypDataDatabase.getDBConnection().prepareStatement(mCommand); > mPreparedStatement1.setCharacterStream(1, new > StringReader(mKey)); > mPreparedStatement1.setCharacterStream(2, new > StringReader > (mValue)); > ResultSet mResultSet1 = > mPreparedStatement1.executeQuery(); > mResultSet1.first(); > String mEncryptedValue = mResultSet1.getString(1); > mPreparedStatement1.close(); > > String mCommandDecrypt = "CALL trim(char(0) from > UTF8TOSTRING > (DECRYPT('AES',rawtohex(?), ?)))"; //fails > //System.out.println("Decrypt SQL:"+mCommandDecrypt); > org.h2.jdbc.JdbcPreparedStatement mPreparedStatement2 = > (org.h2.jdbc.JdbcPreparedStatement) > cCrypDataDatabase.getDBConnection().prepareStatement(mCommandDecrypt); > > mPreparedStatement2.setCharacterStream(1, new > StringReader(mKey)); > mPreparedStatement2.setCharacterStream(2, new > StringReader > (mEncryptedValue)); > ResultSet mResultSet2 = > mPreparedStatement2.executeQuery(); > mResultSet2.first(); > String mDecryptedValue1 = mResultSet2.getString(1); > mPreparedStatement2.close(); > > String mCommandDecrypt2 = "CALL trim(char(0) from > UTF8TOSTRING > (DECRYPT('AES',rawtohex(?), '"+mEncryptedValue+"')))"; //works > org.h2.jdbc.JdbcPreparedStatement mPreparedStatement3 = > (org.h2.jdbc.JdbcPreparedStatement) > cCrypDataDatabase.getDBConnection().prepareStatement > (mCommandDecrypt2); > > mPreparedStatement3.setCharacterStream(1, new > StringReader(mKey)); > ResultSet mResultSet3 = > mPreparedStatement3.executeQuery(); > mResultSet3.first(); > String mDecryptedValue2 = mResultSet3.getString(1); > > mPreparedStatement3.close(); > System.out.println("original value: " + mValue); > System.out.println("decrypted with ? + > setCharacterStream:" + > mDecryptedValue1); > System.out.println("decrypted with > '+mEncryptedValue':" + > mDecryptedValue2); > > } catch (SQLException e) { > e.printStackTrace(); > } > } > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
