Page 1 of 1

MySQL fill column with random hashes

Posted: Thu Dec 03, 2009 3:33 am
by batfastad
Hi everyone

I have a table of 30,000 records and I'd like to generate a random md5 (or whatever) hash for each one.
I could use PHP to go through and generate all these then update the database.
Just wondering if there's a way to do it within a MySQL query which would probably be a bit quicker.

I understand md5 and sha-1 are no longer considered secure for cryptographic purposes but I think they'll be good enough for our simple needs.
I'd also like to store this as a hex value rather than binary.
I realise I could use the MySQL MD5() function, but what's the best way to get a strong random in MySQL to pass to the MD5() function?
Can MySQL be persuaded to use /dev/urandom to get a strong random?

Cheers, B

Re: MySQL fill column with random hashes

Posted: Mon Dec 14, 2009 1:58 pm
by batfastad
Hi everyone
Anyone got any shortcuts for this?
I'm probably going to go ahead and generate the hashes and shove them in the DB manually through PHP at the moment

Cheers, B

Re: MySQL fill column with random hashes

Posted: Mon Dec 14, 2009 2:21 pm
by John Cartwright
You will either need to generate the SQL by hand our through PHP. There is no magic way to tell mysql to fill it's database with specific data, that's what queries are for.

For x=0; x<1000; x++ {
SQL = insert into table set hashcolumn = md5(x)
}

I'm on my iPhone so forgive the pseudo

Re: MySQL fill column with random hashes

Posted: Mon Dec 14, 2009 2:59 pm
by Eran
[sql]UPDATE table1 SET hash_col = MD5(RAND())[/sql]
Alternatively, you can concatenate some of the other columns (preferably unique key columns) as the input for the MD5.

Re: MySQL fill column with random hashes

Posted: Tue Dec 15, 2009 6:33 am
by John Cartwright
pytrin wrote:[sql]UPDATE table1 SET hash_col = MD5(RAND())[/sql]
Alternatively, you can concatenate some of the other columns (preferably unique key columns) as the input for the MD5.
I stand corrected.

Re: MySQL fill column with random hashes

Posted: Tue Jan 05, 2010 7:29 am
by batfastad
Ok I'm just getting round to doing this and I have some questions.

I have a PHP script which gets a whole bunch of random data from /dev/urandom.
It outputs hex strings of 64 characters in length, for each row of my database.
The reason I'm outputting in hex rather than the raw binary is because I want to copy & paste this query to execute it.

How do I use MySQL to convert this 64 char hex string into binary for storing in a BLOB field?

Would I just use the UNHEX() function like this?

Code: Select all

UPDATE `companies` SET `HASH`=UNHEX('3802f199dcb8b2b32afc2ef9732628d5ea00682eccb8fd0a79fbdc62e9a1b184') WHERE `company_id`=123456;
Where the hash field is my binary BLOB

I just wanted to make sure that I'm not corrupting my hash value at any point in the process. I realise conversion from hex to binary should be lossless but I didn't know if there are any bugs or gotchas I should be aware of

Cheers, B