I need a way to encypt and hash 14-digit member IDs stored in a mysql database automatically, preferably directly through php, but I guess I can also call a shell script which executes the script with the supplied variables.
The member ID as well as the hash is going to be printed on a physical ID card for verification.

The script should select and encrypt all the IDs which are not already encrypted in from the field 'id' in table 'memberdata' in database 'members' with user 'user' and password 'password' on 'localhost', then write the hashes to the 'idhash' field for each respective record.

The IDs are encrypted using aes256 encryption with a supplied key and then made an md5 hash, with the following command:

openssl des -e -aes256 -k thekey -in memberids | openssl md5

$mydb=array("$dbhost"=>"localhost","$dbuser"=>"username","$dbpass"=>"password","$dbname"=>"database");

function connect_db($mydb)
{
mysql_connect($dbhost,$dbuser,$dbpass)
mysql_select_db($dbname);
return true;
}

connect_db($mydb);

$sql="SELECT * FROM memberdata";
$res=mysql_query($sql);
while($data=mysql_fetch_array($res))
{
//is id greater than 14?, (md5 hash is 32 characters)
$id=$data['id'];
if(!strlen($id) > 14)
{
$id=md5($id);
}
echo"<br> id is :: $id";//prints out every row of the database
//as a list of md5 hashes.
}

This should get you started

//assuming a valid connection to the database exists

//find all those members that don't have a hash
$result = mysql_query("SELECT id,idhash FROM memberdata WHERE idhash = ''");
if (!$result) { die(mysql_error()); }

while($row = mysql_fetch_assoc($result))
{
//Grab the encrypted key
$idhash = system("openssl des -e -aes256 -k $key -in $row['id'] | openssl md5");

//Set the idhash value
if (!mysql_query("UPDATE memberdata SET idhash = '".$idhash."' WHERE id = '".$row['id']."'")) { die(mysql_error()); }
}



Of course, you should modify it for your own needs :)

Thank you very much. I finished fyrestrtr's code like this (user/password removed):

<?php

mysql_connect("localhost", "user", "password") or die(mysql_error());
mysql_select_db("members") or die(mysql_error());

//assuming a valid connection to the database exists

//find all those members that don't have a hash
$result = mysql_query("SELECT id,idhash FROM memberdata WHERE idhash = ''");
if (!$result) { die(mysql_error()); }

while($row = mysql_fetch_assoc($result))
{
____//Grab the encrypted key
____$idhash = system("openssl des -e -aes256 -k testpass -in $row['id'] | openssl md5");
_____
_____//Set the idhash value
_____if (!mysql_query("UPDATE memberdata SET idhash = '".$idhash."' WHERE id = '".$row['id']."'")) { die(mysql_error()); }
}

?>

However, I'm getting a parse error on the openssl line. Ideas?

You're probably getting the parse error because openssl expects its input to be supplied by the file listed in the -in parameter, or by stdin. You'll need to change your code to write the ID to a temporary file first, or to pipe it into the command.

system() isn't the right function to use, assuming that you don't want openssl's output to be displayed to the user. Use popen() or proc_open() instead. These will require additional changes to the code. See http://us4.php.net/manual/en/function.popen.php and http://us4.php.net/manual/en/function.proc-open.php.