Database advice (nerd alert)

[Slurp56 0]

I am storing some encryted values in my database. Any advice on what is the best datatype to use?

The values are encrypted in 256bit SSL and then encoded to base64 to weed out weird characters.

Right now I've got my database column setup as a datatype of blob and each value is 344bytes. Is this the best datatype to use? Would it be wiser to store it as a varchar(256) or even a text field?

My database is MySQL 4.0.20d.

Thanks.

________________________________________
I have proof-read this post 500 times, but I guarantee you'll still manage to find a flaw.

[MikeMcLean 0]

If you are base64 encoding just for the database, there isn't a real reason to. You can use a BLOB and store the binary encrypted value.

Of course, if you need the base64 for other reasons, then you can store the base64 as a TEXT or a VARCHAR. If you know that every value is below a certain maximum size, use the appropriate VARCHAR size.

The general rule of thumb is pick the smallest, most-specific column type for your data.

If you are in a very, very tight DB disk space requirement and/or you know that you have many, many records and only a few will have actual data for the encrypted data then there are other considerations. These, most likely, don't apply -- but if they do and you want to ask, go ahead. This post is getting too geeky already

It wouldn't hurt you to think like a fucking serial killer every once in a while - just for the sake of prevention

[AggieDave 6]

Damn guys, I bet even BillVon is holding his head, fetal infront of his keyboard rocking back and forth...

Actually, its pretty refreshing, its been a few years since I've even thought about the technical side to SQL.

--"When I die, may I be surrounded by scattered chrome and burning gasoline."

[bob.dino 1]

Depending on the application, you may find MySQL's built-in support for encrypted columns handy.

Otherwise, just use a [var]binary column. BLOBs are only really needed for large amounts of data (hence Binary Large OBject). I'm not sure about MySQL, but Oracle doesn't store BLOBs/CLOBs with the rest of the row, making lookups and searches involving *LOBs very very slow.

Edit: For the record, you're not storing something in "256 bit SSL". SSL is not an encryption algorithm. It uses encryption algorithms such as RC4, AES, and Blowfish to establish secure connections between two endpoints. Encryption can be very confusing .

[flyingferret 0]

I say use Paradox
I support it everyday at work. Amazingly it supports BLOBs. Pretty impressive considering the age (*cough* obsolesence)

--
All the flaming and trolls of wreck dot with a pretty GUI.

[Slurp56 0]

Thanks for all the advice!

I am still trying to recover from a rough night of partying like a rockstar, so I hope this is coherent.

The 2 fields in the database or both 344 characters in length, and I dont see any reason it should change. Being that varchar can only hold up to 255 characters I guess that is out of the question. I guess I will leave the datatype as a blob then.

As far as SSL encryption goes, I am encrypting the values using RSA. I think thats right anyways, I use openSSL to generate a pair of RSA keys, then use the php openSSL functions to encrypt the data.

The base64 encoding is used because I am putting the encrypted string in my source code when I retrieve it. When I click a button, it copy's the encrypted string to my clipboard and then I can paste it into an app on my local computer to decrypt it. If I dont use base64 encoding, then anytime there are HTML characters in the string, it messes up my code.

________________________________________
I have proof-read this post 500 times, but I guarantee you'll still manage to find a flaw.