Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    Trondheim, Norway
    Posts
    3

    php/shell script encrypting member IDs in a mysql database

    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

  2. #2
    PHP Code:
    $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. 

    Last edited by trukfixer; 04-23-2004 at 09:17 AM.

  3. #3
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,104
    This should get you started
    PHP Code:
    //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

  4. #4
    Join Date
    Mar 2004
    Location
    Trondheim, Norway
    Posts
    3
    Thank you very much. I finished fyrestrtr's code like this (user/password removed):

    PHP Code:
    <?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?

  5. #5
    Join Date
    Jun 2003
    Posts
    673
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •