Best Practice When Encryption is Necessary
Posted: Sun Apr 16, 2006 5:41 am
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
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