Best Practice When Encryption is Necessary

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
dickey
Forum Commoner
Posts: 50
Joined: Thu May 16, 2002 8:04 pm
Location: Sydney, Australia

Best Practice When Encryption is Necessary

Post by dickey »

I have a project where the environment is php (scripting) and mssql (db). Please no laughing.

One basic tenant of the project is that all data (each field or column (mssql speak) with the exception of a numeric id (key) in each table, be encrypted before insertion into the database.

The aim therefore is to kept sensitive data away from even senior db admins.

Encryption in MSSQL is a joke. As I understand it beside the very flimsy and largely undocumented treasures (laugh) like PWDENCRYPT and is PWDCOMPARE, MSSQL relies heavily on third party encryption solutions.

I have no mandate to install any third party app to assist encryption in MSSQL, so that leads me to my topic of discussion.

I know how to implement encryption via mcrypt in PHP, then insert encryted data into MSSQL.

However what you gain in security you inevitably sacrifice in functionality.

I believe you probably sacrafice:

- the choice of data types (from memory int, decimals and dates are an issue as encrypted data (whilst the same length) is incompatible with many data types). Therefore you sacrifice implementing validation at the db end.
- secondly you are forced in encrypt variables when filtering data, and decrypt found sets to be using them in reports and calculations etc.
- encrypting / decrypting results in some overhead in dev time etc.
- stored procedures are also problematic when the data is encrypted.

For instance I had considered good practice would be to generate a different IV (ie mcrypt_create_iv) and different key for each table in the db. The belief here is that I would further enhance security. In practice its a bastard when encrypting vars with different IV's and Keys as search criteria when attempting a multi table join etc.

I would appreciate some advice on what is considered best practice when operating with encrypted data.

- Thanks Andrew
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

It occurs to me that if everything is encrypted, you might as well serialize the object, encrypt it, and just have a simple (id, encrypted text) schema, as encryption effectively renders pretty much all the benefits of atomicity useless.

Another thing: if the PHP script knows the encryption key, then anyone with access to the scripts can decrypt the data.

I really can't help you here. To me it sounds like a hopelessly paranoid set of specifications.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

If its a big of an issue, then tell your system administrator you require to update to a more sensible database ;)
Post Reply