xp_crypt 4

divya4k2007 · #1 (**permalink**) 08-02-07, 09:34

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

aschk · #2 (**permalink**) 08-03-07, 03:44

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).

divya4k2007 · #3 (**permalink**) 08-03-07, 04:41

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

anjanesh · #4 (**permalink**) 08-14-07, 12:16

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($key, 0, mcrypt_enc_get_key_size($td));



        $iv_size = mcrypt_enc_get_iv_size($td);

        $iv = mcrypt_create_iv($iv_size, MCRYPT_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($key, 0, mcrypt_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

Internet Services

Web Development

Digital Marketing

Consumer Tech