Re: [announce] OPiuM -- stock Options Portfolio Manager

> and YES, I did try them on DATE and INT fields, even on CHAR and VARCHAR -- > you MUST use CHAR BINARY or VARCHAR BINARY or they won't decode. The other > types won't even encode. you get 0 or 000-00-00 in the table. Quite true, given that there's no sensible way to store random binary data as a date or int. I was refering to the fact that having to store stuff as strings does not me you must limit yourself to just string logic. You can make the store/restore process reasonably transparent. > > it would also completely break > > things like indexes and queries. > > WHAT?! You can't INDEX an ENCODED field currently either (well, you prolly > can, but what good does it do you, when you search, you're searching for the > DECODED'd value, not the ENCODED one) and the extensions mentioned clearly > illustrated both an INSERT and a SELECT. it's not rocket science. it's That was my point - being able to use indexes (to make things fast) and WHERE/ORDER is what makes MySQL useful. By introducing an extension where different rows are encrypted with different passwords, there's not much point to it as you're giving up almost all of the benefits. What good is an index when 99% of the data can't be decrypted with the password you have? What is the point to a complicated where constraint when only a small amount of the data can be meaningfully used? Functionally, I don't think there's any difference between your approach and ENCODE(serialize()) and unserialize(DECODE()). If that's the case, what point is there to placing it in MySQL? > > Another > > approach would be to define objects for > > all of your records, use serialize() to turn each object into a > > string (which could be encoded using > > the mcrypt functions) and store that. Once you retrieve that > > object back, use unserialize() to > > restore it. > > this isn't at all secure IMHO. anyone can unserialize the data then. My way, > only the user who knows the key 'secret' can undo their own data. You missed the part about encoding the serialized data. The idea is that we make the encryption portion transparent to PHP so that you can unserialize the data and get all of your favorite classes and datatypes back without ever needing to worry about the fact that the database doesn't see them as anything other than one large block of bytes.