MySQL fill column with random hashes

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL fill column with random hashes

Post 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
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL fill column with random hashes

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: MySQL fill column with random hashes

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL fill column with random hashes

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: MySQL fill column with random hashes

Post 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.
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL fill column with random hashes

Post 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
Post Reply