How do I provide encryption for all the data in a public application?

March 23, 2005 3:44 PM

How should I keep data encrypted in my database? (php, mysql help)

I’ve built a web-based note taking / to do application that I’ve been finding extremely useful. I’d like to set up a user database and let some friends, and eventually maybe the public, use it. To retain privacy I’d rather not be able to see the content in a readable form in the database. How can I do this? I’ve thought about using something like mcrypt to encrypt all the strings and decrypt them with their own key, but how does one deal with a forgotten key? Is there someway to store the key in the database itself? Would they need to log enter the key at each log-in? Is it safe to store it as a cookie?

I’m sure there’s a fairly simple method of doing this, but I can’t think of it. The platform is PHP 4.3.1 and MySql. My level of knowledge is pretty high, but all self-taught.

Also, I’m happy to hear any other stories about doing something like this and maybe some issues I haven’t even thought of.

posted by miniape to computers & internet (10 comments total)

miniape - I think you're over or underthinking it, depending.

The easiest way by far to do what you're intending, which is to keep users from reading each others data, is to enforce very strict program-side requirements for a username field, and to have the user log in. Another way to do it would be to create a separate database and MySQL user id (assuming this isn't on a shared hosting platform) for each user and use MySQL's permissions systems to partition things off.

Encryption is quite possibly a waste of your time. It's good to keep things like passwords encrypted, but if you're just trying to keep people from reading each other's notes ... not worth going through it.

(OTOH, it might be worth considering ... if you are trying to protect people's data from third party attackers. Even then, you face man-in-the-middle attacks that involve intercepting the remote key, combining it with your local key, and decrypting the data.)
posted by SpecialK at 4:36 PM on March 23, 2005

After reading SpecialK's comment, I think I should add that I'm not just trying to hide data between users, but also from myself. So when I browse through the database via phpMyAdmin I can't read everyone's thoughts.

I'm less familiar with MySql than with PHP, so I'll look into the permissions systems.
posted by miniape at 5:38 PM on March 23, 2005

If you just want to avoid reading other people's stuff without necessarily locking yourself out of it, you could just crypt using the user's username. It would be trivial to decrypt, but you won't see anything accidentally.

If you crypted using the password, you'd have to decrypt and recrypt whenever anyone changed their password. But since you could easily capture the password on entry it's actually no more secure from you than just a token crypt based on the username.
posted by krisjohn at 5:55 PM on March 23, 2005

Store the key as a cookie on the user's computer. Each time they save/load data, read the cookie and encrypt/decrypt. This way you never have to store it.

You can use the key as the user's password. To verify it, simply encrypt a known phrase using the key when they create their account and store it in the users table. The when they log in, test that the password/key decrypts the phrase correctly and then set the cookie.

(I don't think being able to retrieve data when the password is forgotten is compatible with keeping the data secret, although I suppose you could store the key and encrypt with the user's date of birth or some other fixed value, which you don't store)
posted by cillit bang at 6:00 PM on March 23, 2005

Another option is to use the str_rot13 function.

This would make it so you can't easily read it but without the overhead of encryption.

Here is a section on ROT13 of an excellent, free PHP book that I use: http://www.hudzilla.org/phpbook/read.php/17_3_4
posted by entropy at 6:02 PM on March 23, 2005

I second the rot13 recommendation, as it seems to be the easiest way to prevent you from reading data with the added benefit of native php support.

Of course, I have just noticed some geeks talking about being able to read rot13 text, so if you can do this, then disregard my suggestion :)
posted by jikel_morten at 8:21 PM on March 23, 2005

This is for the privacy of users and you guys are suggestions rot13? This isn't to avoid seeing a spoiler, it's to protect users' privacy.
posted by null terminated at 10:55 PM on March 23, 2005

no, it's to stop the admin accidentally reading the text while he's maintaining the db. rot13 is just fine for that. in fact, for this scenario it's just as good as almost any other scheme - it's very difficult to encrypt stuff on the system in a way that is going to guarantee privacy against the admin, so whatever fancy schmancy scheme you were thinking of would be no better.
posted by andrew cooke at 6:10 AM on March 24, 2005

I'd suggest base64 encoding or encrypting with a constant password, as rot13 doesn't touch numbers and other non-alphabetic characters (you might accidently read amounts of money, or see ascii art, or something).

But ultimately, your users are going to have to trust you. You can go through a lot of effort (say, doing all the encryption client-side), but ultimately you are in control of the entire system. I don't think it's possible to protect your users from yourself.
posted by reynaert at 6:31 AM on March 24, 2005

Thanks for all the responses. I'll probably go with Rot 13. I was hoping for something more secure because I think it would encourage people to use it more freely, but I already maintain about a dozen email accounts which I theoretically have access to and I've never once looked at one. I don't think any one even considers it when they send mail. Ideally I'd like to say there's no way for me to spy on users, but I'm sure it won't even be a concern of the average user.

Thanks again. I'm sure I'll have more questions by the time I'm done with this.
posted by miniape at 7:36 AM on March 24, 2005