Craig,
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
available.
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
statement.
$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
security.
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.
Brent
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
