[RESOLVED] encode/decode dilema

jasonyoung

I have some data to encode and it works fine. It also decodes fine. (FYI, these aren't passwords, just IP addresses, subnet masks...) Since I need to see these fields in their plain text format at later times, I've been led to believe encode/decode should be my best suit. My question is when I insert the info into MySQL, what format should the columns be in. VARCHAR works. But so does BLOB.

Any ideas?

Thanks guys!

AIS4U

jasonyoung:

I am pretty sure Blob is case sensitive whereas varchar is not so if you were to do a search on a Blob and the case doesn't agree you won't find the item.

I'm not 100% positive, but pretty sure that is at least one difference.

herve

How do you encode ? And for what reason/purpose ?

If you're using something like base64_encode() / base64_decode() it will not really matter.

Well VARCHAR have a 255 max length where a TEXT column can be bigger (64 K or even greater).

BLOB usually are used to store binary data (content of an image for instance)
not strings.

So it depends of the output of your coding function (is it binary or not ?) and the length
of your data (is it less of 255 characters or greater ?).

jasonyoung

How do you encode ? And for what reason/purpose ?

I've changed to AES_ENCRYPT. Basically what I'm storing is client information abount their internal LAN. They are directed to a secure site and fill out a form. Initially I was just emailing the form (via PHP) to our installation team at the office, but realizing this isn't secure, it defeated the purpose of having them visit a secure site in the first place.

Now where I'm going with things is to have their form fields encrypted upon insertion to the DB and only send a notification email about an entry made to the DB w/o any sensitive information in the body. Since we know how/where to go to get the info, we'll browse there manually and have the output form decrypt the fields for viewing. That way nothing is plain text.

It seems like in VARCHAR is see the encrypted characters, but in BLOB I only see BLOB 'xx' bytes. So is one more secure?

If you're using something like base64_encode() / base64_decode() it will not really matter.

Not sure where this comes in. Heres an example of what my insert query looks like:

VALUES ('$accountname',
AES_ENCRYPT('$siteaddress','$crypt0'),
AES_ENCRYPT('$citystatezip','$crypt6')

The key is in an include file in another password protected directory. The kicker is the data needs only be in the DB for a day or so. Even so, thats long enough to be compromised. Our output form which decrypts the data has a link at the botton which deletes the entry from the DB.

Make any sense?

herve

Yes, that's more clear.

Quoting : http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html

The encryption and compression functions return binary strings. For many of these functions, the result might contain arbitrary byte values. If you want to store these results, use a BLOB column rather than a CHAR or (before MySQL 5.0.3) VARCHAR column to avoid potential problems with trailing space removal that would change data values.

So here's your answer 🙂

The AES_CRYPT function return binary strings, so you have to store them in BLOB
columns, not in CHAR/VARCHAR/TEXT column.

jasonyoung

Thanks Herve. That about covers it. Appreciate your time.