RE: [PHP-GENERAL] how do I protect/encrypt sensitive data in a database?

> the client side. Root could always > install some logging trojan on the webserver otherwise. You I meant that root (or someone with root access) couldn't just connect to the db and start doing SELECT's and stuff. Obviously I can't control every scenario, and I agree that if people are this dishonest, I would bail. I'm talking about normal circumstances.. for example, say someone is using PHPMyAdmin (web based program) and accidentally clicks on the 'options' database and sees the list or something like that. > Assuming we don't need to go quite that far down the cypherpunk > road, the idea you had about using > the user's password to encrypt their data works, but only if > they're the only one inputting it, > which probably isn't true ("Yeah, I have 9,999,999 options. What > of it?"). Actually, that is true in this case. It's more of a 'portfolio' manager. It's so an employee can input their option grants and see how many they have vested, when they'll vest, how much their worth under various hypothetical stock prices, how much they'll have to pay to exercise the options, etc... It's nothing that the company is using to maintain the records -- it's more of a "fun thing", but a mild degree of security is still required. NOBODY is going to input their info and actually use this thing if there is the possiblity that others can casually see their numbers. Especially *ME* being the designer/coder of the database, they'll not want lowly ol' me to know. dig? > isn't?) why not set up an OpenBSD box > with nothing other than an SSL webserver running on it and Linux box, Apache, openSSL planned, but it's a switched, proxy, intranet, so sniffers are probably unlikely. And I also won't be storing names, so people will just be numbers -- hard to know who is who then. > or you could do what I've been thinking about and write a wrapper > for the libcrypto functions > provided by OpenSSL. Alternately, an even more secure approach If only I had the C/C++ skills to do things like that. :) For now, the Open Source community will just have to enjoy the benefits of my "Options Portfolio Manager" or "OPiuM" (hey, I just made that up right now! ;-) when it's done and I release it to everyone. I guess after thinking more about this, I'm wondering if I can do something like (pseudo - code): CREATE TABLE Option_Table ( option_id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, option_employee_id INT(10) NOT NULL, option_grant INT(10) NOT NULL, option_grant_date DATE DEFAULT '0000-00-00' NOT NULL, option_vest_commencement_date DATE DEFAULT '0000-00-00' NOT NULL, option_exercise_price DECIMAL(5,2) NOT NULL, INDEX(option_employee_id), INDEX(option_vest_commencement_date) ); INSERT INTO Option_Table VALUES (null, ENCODE($option_employee_id, $passkey), ... ENCODE($option_grant_date, $passkey), ...); then later retrieve them with a statement like: SELECT DECODE(option_employee_id, $passkey), DECODE(option_grant_date, $passkey) FROM Option_Table WHERE DECODE(option_employee_id, $passkey) = $option_employee_id; Notice how EN/DECODE() require "strings" but the values in my table are various types (INT, DATE, DECIMAL) etc... I'm under the impression that mySQL stores everything as strings internally so I'm sorta hoping this will work. What I'm trying to avoid is this (notice they're all CHAR() i.e. strings): CREATE TABLE Option_Table ( option_id CHAR(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, option_employee_id CHAR(10) NOT NULL, option_grant CHAR(10) NOT NULL, option_grant_date CHAR(10) DEFAULT '0000-00-00' NOT NULL, option_vest_commencement_date CHAR(10) DEFAULT '0000-00-00' NOT NULL, option_exercise_price CHAR(8) NOT NULL, INDEX(option_employee_id), INDEX(option_vest_commencement_date) ); The documentation on the Encode/Decode() functions is vague at best and implies it only works on strings, but doesn't expand upon what a string _really_ is IYKWIM. > A perusal of RISKS Digest (anyone doing security should subscribe > to BUGTRAQ and RISKS Digest) I'm on BUGTRAQ, what is the url/email for RISKS? And what does that list talk about?