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
Best Practice When Encryption is Necessary
Moderator: General Moderators
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
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.
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.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact: