Storing encrypted data in MySQL

prouski · Aug 23, 2002

I'm running on a shared server, no access to php config files, mcrypt is not compiled in, but have SSL support.

I need 3 users to be able to login in to a site and add/delete/view data to MySQL, but all data needs to be stored encrypted. Managing the user login/encryption side of things is no problem, but I'm lost as to how to do 2-way encryption across multiple users with no mcrypt support.

Does anyone have any clever suggestions?

Thanks.

tomkleijkers · Aug 23, 2002

It's allways a problem when you need 2-way encryption. But why do you need it? One-way encryption is much saver! You can use base_64 encoding, but it's easy to decode it (isn't it allways?).

prouski · Aug 23, 2002

Thanks for this. I guess I phrased the question badly.

I need to store encrypted data (reasonably securely) and be able to decrypt it for viewing in authenticated users' browsers.

Having looked at base64 it's more of an encoding rather than encryption solution, so it would be a bit lame of me to rely solely on this, as I'll be storing users' home details, phone numebrs, etc.

Still looking for the solution......

tomkleijkers · Aug 23, 2002

It has no use encrypting your data in the database. If you want some people to see encrypted data and others the real values, just encrypt it when it's been viewed by an unautorised user.

So:
If the data is viewed by an autorised user: just display
Otherwise: Encrypt using md5 (or something).

There's no need to encrypt data in a db for security, because if someone can hack into your db, he also can hack into your files and view the encryption.

Regards,

Tom

jayant · Aug 23, 2002

I don't remember about its source but you can have the code:

$ralphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890";
$alphabet = $ralphabet . $ralphabet;

function encrypt ($strtoencrypt,$password) {
$strtoencrypt = str_replace("\t","[tab]",$strtoencrypt);
$strtoencrypt = str_replace("\n","[new]",$strtoencrypt);
$strtoencrypt = str_replace("\r","[ret]",$strtoencrypt);
global $ralphabet;
global $alphabet;
 for( $i=0; $i<strlen($password); $i++ )
 {
   $cur_pswd_ltr = substr($password,$i,1);
   $pos_alpha_ary[] = substr(strstr($alphabet,$cur_pswd_ltr),0,strlen($ralphabet));
  }
$i=0;
$n = 0;
$nn = strlen($password);
$c = strlen($strtoencrypt);
 while($i<$c)
 {
   $encrypted_string .= substr($pos_alpha_ary[$n],strpos($ralphabet,substr($strtoencrypt,$i,1)),1);

   $n++;
   if($n==$nn) $n = 0;
   $i++;
  }
return $encrypted_string;
}

function decrypt ($strtodecrypt,$password) {
global $ralphabet;
global $alphabet;
 for( $i=0; $i<strlen($password); $i++ )
 {
   $cur_pswd_ltr = substr($password,$i,1);
   $pos_alpha_ary[] = substr(strstr($alphabet,$cur_pswd_ltr),0,strlen($ralphabet));
  }
$i=0;
$n = 0;
$nn = strlen($password);
$c = strlen($strtodecrypt);
 while($i<$c) {
   $decrypted_string .= substr($ralphabet,strpos($pos_alpha_ary[$n],substr($strtodecrypt,$i,1)),1);
   $n++;
   if($n==$nn) $n = 0;
   $i++;
  }
$decrypted_string = str_replace("[tab]","\t", $decrypted_string);
$decrypted_string = str_replace("[new]","\n", $decrypted_string);
$decrypted_string = str_replace("[ret]","\r", $decrypted_string);
return $decrypted_string;
}

Note: Only characters in $ralphabet will be encrypted or decrypted properly. Eg: Presently it doesnt have a ; (semi-colon), it you encrypt ( it will do so, but will not decrypt the encrypted to ( .

Also you must pass $password same as the one used for encrypting to decrypt properly. Works similar to crypt function

jayant · Aug 23, 2002

Originally posted by tomkleijkers
It has no use encrypting your data in the database. There's no need to encrypt data in a db for security, because if someone can hack into your db, he also can hack into your files and view the encryption.

But when you don't encrypted he gets all data directly whereas when encrypting data, he will have to do a bit of work
Maybe he is lame or lazy to do that, so data is certainly more secure

tomkleijkers · Aug 23, 2002

It's good to see that someone has written an encryption himself.
Ofcourse the moderator is right that when the data is encrypted it's more work for the hacker, but's also more work for you .

Good luck encrypting!

jayant · Aug 23, 2002

Originally posted by tomkleijkers
It's good to see that someone has written an encryption himself.

Originally posted by tomkleijkers
Ofcourse the moderator is right that when the data is encrypted it's more work for the hacker, but's also more work for you .

For first time only (when you conver all your existing data to encrypted format) and none if you don't have any content

tomkleijkers · Aug 23, 2002

I won't create a big discussion on this, but as I see it, it's not really nessecary to encrypt data in a db.

prouski · Aug 23, 2002

Thanks for all the help.

Once the script was amended to declare the $en/decrypted_string as global it works beautifully, if simply.

I appreciate the argument about not storing encrypted data, but the more obstacles I can put in the way of any would-be snooper the better.

choppsta · Sep 5, 2002

I've been asked more and more about secure extranet style sites recently and it's something I've been thinking about for some time now. Here's what I've come up with so far...

The site should be on a dedicated server (preferrably UNIX based).
This server should be protected by a firewall of some description.
The server should have SSL installed to encypt the data to and from the client.
The data in the MySQL database should be encrypted with a 2 way encryption method, such as using the PHP mcrypt module. The only problem I see then is what to do with the encryption key. Where to store it?

Any ideas on how to make it more secure or any thoughts, problems with this??

blr32 · Oct 21, 2002

The way I'm GOING to do it is to

bin2hex() the encrypted data before storing it in mysql.

Of course you need to hex2asc the string before descrypting it - use this function:

function hex2asc($myin) {
for ($i=0; $i<strlen($myin)/2; $i++) {
$myout.=chr(base_convert(substr($myin,$i*2,2),16,10));
}
return $myout;
}

You can try it out with this modified snipped which I originally took from
http://www.onlamp.com/pub/a/php/2001/07/26/encrypt.html?page=3

/* Listing all allgorithms in an array */
$cipher_arr	=	mcrypt_list_algorithms();


// Designate string to be encrypted
$string = "Applied Cryptography, by Bruce Schneier, is a wonderful cryptography reference.";

// Encryption/decryption key
$key = "Four score and twenty years ago";


while (list($this_key, $this_cipher) = each($cipher_arr)) {
echo "-------------------------------------<br>Now using: $this_cipher<br>";

// Create the initialization vector for added security.
$iv = mcrypt_create_iv(mcrypt_get_iv_size($this_cipher, MCRYPT_MODE_ECB), MCRYPT_RAND);

// Output original string
print "Original string: $string <br>";

// Encrypt $string
$encrypted_string = mcrypt_encrypt($this_cipher, substr($key,0,mcrypt_module_get_algo_key_size($this_cipher)), $string, MCRYPT_MODE_CBC, $iv);

// Convert to hexadecimal and output to browser
$encrypted_string	=	bin2hex($encrypted_string);
print "Encrypted string: $encrypted_string<p>";
$encrypted_string	=	hex2asc($encrypted_string);
$decrypted_string = mcrypt_decrypt($this_cipher, substr($key,0,mcrypt_module_get_algo_key_size($this_cipher)), $encrypted_string, MCRYPT_MODE_CBC, $iv);

print "Decrypted string: $decrypted_string<P>";
}

tomkleijkers · Oct 21, 2002

I still don't see the meaning of encrypting data in a secured database. Hackers will get it anyway. And secondly, most hackers aren't interested in small sites, or ever big sites. They only want huge sites, because it's too much work!

michael · Oct 21, 2002

Originally posted by tomkleijkers
It has no use encrypting your data in the database. If you want some people to see encrypted data and others the real values, just encrypt it when it's been viewed by an unautorised user.

So:
If the data is viewed by an autorised user: just display
Otherwise: Encrypt using md5 (or something).

There's no need to encrypt data in a db for security, because if someone can hack into your db, he also can hack into your files and view the encryption.

Regards,

Tom

Firstly, MD5 is not encryption. It's a hash. It's analgous to CRC32.
If you were to make a hash to be used one-way only, use SHA-1.

Secondly, mysql users have access to an array of encryption routines build into mySQL.

Available functions in MYSQL: MD5, SHA, Password, Encrypt, Encode, AES[de/en]crypt, Des[de/de]crypt, et al, ad naseum.

[list from memory, check your version for availability and useage rules.]

Reference:
http://www.mysql.com/doc/en/Miscellaneous_functions.html

PS: You have a nice site.

tomkleijkers · Oct 21, 2002

Alright, but you can only hash it when showing, hot hash it and save it in the db, because unhashing isn't possible.

Thnx for the site, hope you'll use it and tell it to your friends...

michael · Oct 21, 2002

Disagree.

You can save the SHA-1 hash in the db. That's the point -- not having a physical record of data which could be damaging if lost.

To store the information raw in the db and hash it on the way out would be insane -- it defeats the purpose.

The originator of this post needs to use DES or AES. SHA or MD5 is nor for him.

tomkleijkers · Oct 22, 2002

That's true ofcourse. But I still think if you encrypt it into the db, you won't have a good protection against hackers, but ofcourse it's always better then not encrypting it. It's a little bit more work ofcourse

Storing encrypted data in MySQL

Pprouski

Ttomkleijkers

Pprouski

Ttomkleijkers

Jjayant

Jjayant

Ttomkleijkers

Jjayant

Ttomkleijkers

Pprouski

Cchoppsta

Bblr32

Ttomkleijkers

Mmichael

Ttomkleijkers

Mmichael

Ttomkleijkers