Results 1 to 4 of 4

Thread: xp_crypt 4

  1. #1
    Join Date
    Jul 2007
    Posts
    9

    Red face Unanswered: xp_crypt 4

    i am searching for database encryption tool, i found xp_crypt 4 and saw the method to encrypt the columns using that s/w, but can anyone tell me how can i decrypt those columns when required.

    Divya

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    So you want to use encryption on some fields in your database, that you want to reverse (i.e. so MD5 and SHA1 can't be used as they are one-way).

    Why don't you just write your own algorithm with keys using your favourite scripting language to encrypt/decrypt them. Look up AES/DES for ideas.
    Alternatively you could write functions/procedures that perform this inside MySQL instead. However there are some insecurities that may lie in that approach, in that anyone who can view stored procedures in the MySQL table is going to be able to see what key/salt you are using. Mind you, i guess they could do the same for scripting languages (as they are non-compiled).

  3. #3
    Join Date
    Jul 2007
    Posts
    9

    Lightbulb data encryption

    earlier i was trying that option only and i succesfully encrypted the columns of the database , but i have to write lot of querries while encrypting and decrypting a column , so it is not feasible, i am looking for a solution so that i can encrypt all the columns of my dtaabase at one end, be it through third party tool or through querries.

    can u plz suggest me a way so that i can encrypt all the columns of my database just by writing single querry.

    Thanks

  4. #4
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    If you have PHP with mcrypt installed, a script like this should do the job
    PHP Code:
    <?php
    $key 
    "my-secret-key";

    $conn mysql_connect("localhost""username""password") or die("Couldnt Connect");
    $db mysql_select_db("database"$conn) or die("Couldnt select database");

    $SQL1 "SELECT `id`, `fieldname` FROM `tablename`";

    $res1 mysql_query($SQL1) or die("Error in $SQL1\n\n".mysql_error());
    while (
    $row1 mysql_fetch_assoc($res1))
     {
            
    $encrypted_value addslashes(my_Encrypt($row1['fieldname'], $key));

            
    $SQL2 "UPDATE `tablename` SET `fieldname` = '$encrypted_value' WHERE `id` = '".$row1['id']."'"
            
    $res2 mysql_query($SQL2) or die("Error in $SQL2\n\n".mysql_error());

            
    usleep(100);
     }
    ?>
    <?php
    function my_Encrypt($string$key)
     {
            
    srand((double) microtime() * 1000000); # For sake of MCRYPT_RAND
            
    $key md5($key); # To improve variance

            # Open the cipher
            
    $td mcrypt_module_open('des''','cfb''');

            
    $key substr($key0mcrypt_enc_get_key_size($td));

            
    $iv_size mcrypt_enc_get_iv_size($td);
            
    $iv mcrypt_create_iv($iv_sizeMCRYPT_RAND);
            
            
    mcrypt_generic_init($td$key$iv);
            
    $encrypted mcrypt_generic($td$string);
            
    mcrypt_generic_deinit($td);
            
    mcrypt_module_close($td);
            
    $encrypted $iv.$encrypted;

            return 
    $encrypted;
     }

    function 
    my_Decrypt($string$key)
     {
            
    $key md5($key); # To improve variance
            
            # Open module, and create IV
            
    $td mcrypt_module_open('des''','cfb''');
            
    $key substr($key0mcrypt_enc_get_key_size($td));
            
    $iv_size mcrypt_enc_get_iv_size($td);
            
    $iv substr($string,0,$iv_size);
            
    $string substr($string,$iv_size);
            
            
    # Initialize encryption handle
            
    mcrypt_generic_init($td$key$iv);

            
    # Dencrypt data
            
    $decrypted mdecrypt_generic($td$string);
            
    mcrypt_generic_deinit($td);
            
    mcrypt_module_close($td);

            return 
    $decrypted;
     }
    ?>
    When retrieving,
    PHP Code:
    $row1['fieldname'] = my_Decrypt($row1['fieldname'], $key); 
    I would however, replace
    PHP Code:
    $res2 mysql_query($SQL2) or die("Error in $SQL2\n\n".mysql_error()); 
    with
    PHP Code:
    echo $SQL.";\n"
    and run in command line
    Code:
    php encrypt.php > encrypt.sql
    mysql --user=username --database=mydatabase -p < encrypt.sql
    MySQL 5.1

Posting Permissions

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