Finding and Decrypting a Record
Since the encryption scheme uses a shared IV on the LastName field, if a user types in a last name exactly the same as a previously encrypted value, it will yield the same encrypted value, thus making it searchable. The stored procedure below performs a simple SELECT
statement on the LastNameShared
column using the @LastName
parameter as a varbinary value rather than the text value you would use for an un-encrypted column.
CREATE PROCEDURE [dbo].[FindClient]
SELECT ClientID, Vector, FirstNameCrypted, LastNameCrypted, LastNameShared, ShelterName
FROM Clients WHERE LastNameShared=@LastName
In the application's code, you take the value the user typed in the last name field, encrypt it using the shared IV and pass it to the FindClient
stored procedure. If you get a result, then the first thing you will need to do is get the IV stored on that record and create a new instance of SimpleAES using the encryption key and the IV that you got from the database for that record. You will then use this instance of SimpleAES to decrypt each of the columns and display the result to the user.
|Author's Note: While you can create indexes on varbinary columns, keep in mind that the encrypted columns for which you're using a private IV can't be searched; therefore it's a waste of time to index them. Only index the columns that use a shared IV.
Taking It Further
private void FindByLastNameButton_Click(object sender, System.EventArgs e)
using(SqlDataReader reader =
byte PrivateVector =
SimpleAES privateAES = new SimpleAES(
Feel free to use the downloadable source code
and database included with this article for study, but keep in mind that you have considerable work still to do before putting encrypted columns into practice, most notably determining where to store and manage your private encryption keys. It's definitely a lot more work to design your application to support encrypted columns, but if you're storing data that could cost someone their life if revealed, it's a critical must.