MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:BD Date:March 3 2002 10:29pm
Subject:Re: Database Encryption
View as plain text  

At 10:02 AM 3/3/2002 , you wrote:
>In an effort to encrypt private data in my database (national id numbers, 
>credit card numbers, passwords) I have had to resort to writing code in my 
>application using the Blowfish algorithm.  Is there any facility for doing 
>this within MySQL (on Windows)?  Any third party tools out there?  For 
>speed reasons, I don't want to encrypt everything, just the private data 
>in database?
>If I have to use the Blowfish algorithm in my client app, is there any 
>standard way of handling keys.  I realize this opens up a huge area, but I 
>was curious if there are any simple ways of key handling short of storing 
>it in the executable code of the client app.

What can I say except perhaps, "Good Luck".<g>

Blowfish isn't as secure as Triple-DES  but if you're only protecting 
against wanna-be hackers it should be ok.  You have to weigh the level of 
security you need against the speed of the algorithm. It would be really 
nice if MySQL supported table wide encryption so if someone found a 
backdoor into your data directory, all of your data would be safe from 
prying eyes. Also if someone broke in and stole your server, table wide 
encryption will slow them down. Table wide encryption is also needed on web 
servers where you may be sharing database files with other web applications.

There are a lot of problems involved in trying to encrypt individual 
columns, especially if they are indexed. Read on.

Because MySQL doesn't support table wide encryption, you're going to have 
to program your encrypt/decrypt function every time you update, insert, or 
select data (which is a real pain). The more programs you have accessing 
the database, the bigger the pain. This is going to be  made easier once 
triggers are implemented. You didn't say if this was a web application or 
not. If you're using PHP then there is a PHP library called php_mcrypt.dll 
that you can use. It has several encryption algorithms including blowfish. 
You will need to compile it for Windows because the binaries are not 

When the data field is encrypted, so is the corresponding index. This is 
fine from a security standpoint, but limits the usefulness of indexes.This 
means to search for an encrypted customer#, you will have to encrypt the 
customer # you are searching for, before it gets put into the Select 

$CustId = ToSQLText(Encrypt("1342", "secretpw"));
$Sql = "select * from customer where cust_id = $CustId";

The ToSQLText function will escape any quotes and other binary characters 
(like returns/linefeeds) that may be generated by the encryption algorithm. 
Now this also presents a problem. Encrypting a number like 1342 will 
usually produce binary unprintable characters and would be invalid 
characters for MySQL Integer or other Numeric column types. So that means 
changing your column types to character for the encrypted fields (OUCH!), 
or find an encryption algorithm that does not produce binary characters. 
Perhaps one that just scrambles the text from "this is a test" to "s sttahi 
its e" or from 1234 to 3241. That way you can keep your same data types. 
(before anyone blows a gasket, the pw would not use the same pattern each 
time!)  As for your Char and VarChar columns, if you encrypt them to binary 
data then these columns will have to be set to Binary.

Of course encrypting indexes also means you can't do ranges like "Select 
cust_id from table where cust_id > 100;" won't work any more because 
encrypted data is random. Once the data is retrieved by the Select 
statement, you will need to decrypt the fields. In PHP you would return the 
results in an array and decrypt the encrypted fields. Of course you also 
need to know if the wrong password was given because then you don't want to 
decrypt the fields into garbage because then it would be re-encrypted to 
garbage when the record is saved with the wrong password, then no one will 
be able to access the encrypted data.

So encrypting individual fields is wrought with problems. I would not 
recommend it unless it was really necessary and you're willing to spend the 
time and effort implementing it. :(

>Also, while we are on the subject, any recommendations of inexpensive SSH 
>server (and client) software for connecting two Windows 98 machines?

Win'98? Yikes! Why not just put your database in a cardboard box and put it 
on the stoop? It would probably offer more security especially if you use 
thick corrugated cardboard.<g>

You shouldn't use Win'98 because the operating system doesn't have any real 
If you're serious about security, upgrade to Win2k (XP?) which has much 
better security. Their NTFS drives can also be encrypted at the OS level 
(you need to check around to see if there is any problem running MySQL on 
an encrypted NTFS partition).  NTFS encrypted drives won't stop everyone, 
but it will slow most people down. You can also run Apache on Win2k and 
that is capable of SSH. You don't need to use IIS or PWS with Win2k which 
limits the # of connections.

I hope this helps.


Do You Yahoo!?
Get your free address at

Database EncryptionScalper3 Mar
  • Re: Database EncryptionBD3 Mar