Обсуждение: Unable to store SHA hash (Non-HTML--Sorry)

I apologize if this message appears in duplicate. The original was posted
before the I received notice that an additional confirmation message would
be needed to get onto the list.

I recently migrated an application from Oracle to Postgresql 7.1. The
migration was fairly painless with one exception:

User's passwords are hashed using SHA, then stored in the database. Ie.
    // Get the hash of the password
    MessageDigest md=null;
    try {
      md = MessageDigest.getInstance("sha");
    }
    catch (NoSuchAlgorithmException e) {
      System.out.println("Error: sha encryption unavailable.");
    }
    String hashedPass = new
String(md.digest(request.getParameter("pass").getBytes()));

This string contains several characters that are outside the normal ASCII
range. The string could be stored and retrieved using Oracle and MySQL, but
in Postgres any unusual characters become '?'. This corrupts the hash and
prevents users from logging on.
So far, the following have been tried:
- Password stored using PreparedStatement setString() call. Retrieved using
ResultSet.getString(). Verified hash corruption in the database.
- Password field datatype changed from varchar to bytea. Oddly enough,
PreparedStatement.setBytes() can not be used against this datatype. Resorted
to using .setString(). Hash was still corrupted at the database level.

Any insight into how to accomplish this task would be greatly appreciated.

Jerry



_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

"Jerry Reid" <jereid@hotmail.com> writes:
> This string contains several characters that are outside the normal ASCII
> range. The string could be stored and retrieved using Oracle and MySQL, but
> in Postgres any unusual characters become '?'.

Postgres will happily store anything except a null ('\0') in a text
field.  I suspect that either

(a) you have set up the database with some multibyte encoding method
activated, and your funny characters are confusing the multibyte
conversions; or

(b) the mistranslation is happening on the JDBC side.

You might try enabling query logging to see exactly what command is
arriving at the backend when you try to insert this data.  That should
determine whether the client or server side is at fault.

            regards, tom lane

Jerry,

There are a couple of things going on here.  First you really can't use
a postgres char/varchar/text datatype to store/retrieve binary
information with jdbc.  (See a message I just posted to the jdbc mail
list explaining character set conversions in the 7.1 jdbc drivers).

So the proper datatype for binary information in Postgres is the 'bytea'
type.  Unfortunately there isn't any support in the jdbc drivers for
this datatype (anyone want to contribute a fix???), so you really can't
use that in your situation either.

In my code where I need to store short pieces of binary data (like
digests) I encode them in hex and store the hex.  Yes this results in a
2 times increase in storage, and some overhead in encoding/decodeing,
but it isn't too bad.  Sure you could use other encodings for the binary
data, but I find hex to be the easiest for small amounts of data.

thanks,
--Barry

Jerry Reid wrote:

> I apologize if this message appears in duplicate. The original was
> posted before the I received notice that an additional confirmation
> message would be needed to get onto the list.
>
> I recently migrated an application from Oracle to Postgresql 7.1. The
> migration was fairly painless with one exception:
>
> User's passwords are hashed using SHA, then stored in the database. Ie.
>    // Get the hash of the password
>    MessageDigest md=null;
>    try {
>      md = MessageDigest.getInstance("sha");
>    }
>    catch (NoSuchAlgorithmException e) {
>      System.out.println("Error: sha encryption unavailable.");
>    }
>    String hashedPass = new
> String(md.digest(request.getParameter("pass").getBytes()));
>
> This string contains several characters that are outside the normal ASCII
> range. The string could be stored and retrieved using Oracle and
> MySQL, but
> in Postgres any unusual characters become '?'. This corrupts the hash and
> prevents users from logging on.
> So far, the following have been tried:
> - Password stored using PreparedStatement setString() call. Retrieved
> using
> ResultSet.getString(). Verified hash corruption in the database.
> - Password field datatype changed from varchar to bytea. Oddly enough,
> PreparedStatement.setBytes() can not be used against this datatype.
> Resorted
> to using .setString(). Hash was still corrupted at the database level.
>
> Any insight into how to accomplish this task would be greatly
> appreciated.
>
> Jerry
>
>
>
> _________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>

> In my code where I need to store short pieces of binary data (like
> digests) I encode them in hex and store the hex.  Yes this results in a
> 2 times increase in storage, and some overhead in encoding/decodeing,
> but it isn't too bad.  Sure you could use other encodings for the binary
> data, but I find hex to be the easiest for small amounts of data.

We use base64 which converts every 3 characters into 4 (as I recall) for
such short things.  For longer, type oid in sql works with
setBytes()/getBytes() in JDBC2, but what the overhead is for a small amount
of binary data I don't know.

David