store HashBytes(''MD5'', passwords.password) as varchar(32)

• Robert Fish-288318

  SSC Veteran

  Points: 235

  More actions

  July 26, 2006 at 2:55 am

  #169422

  Hi gang,

  I am trying to get some sql scripts running which will synchronize an existing sql db table with a new phpBB2 users table.

  existing db users table has fields for username and password (plaintext)

  phpBB2 creates a password field in their users table as varchar(32) which requires the md5 hash of the plaintext password.

  phpBB2 then runs their php code on the passwords to get it into the DB... but I need to bypass the php code, as the user is logging on to a site already, in ColdFusion, and I would like the one login to control all daughter apps.

  I cannot get the hashbytes function to populate that varchar(32) field with anything but binary garbage.

  for example... for plain text password = 'p2mx7q'  the md5 hash I can see in the query results pane by executing:

  SELECT

  TOP 1 password,HashBytes('MD5', passwords.password) AS goodmd5 FROM passwords

  it comes out as: '0x1B1AE0C05DD956ECC0F6719B463E754F' which seems ok to me.

  The problem is that when I try the actual UPDATE it enters 'àÀ]ÙVìÀöq›F>uO' into the varchar(32) field.

  I have tried varchar(50) thinking that the 0x at the front of the binary string was a problem, and have tried all kinds of CAST and CONVERT functions on the function output... but it always inserts garbage.

  QUESTION: Is there any way for me to run this function, and have the md5 hash stored as a normal string??

  Thanks in advance for any help you can provide!

  Rob