I have an ASP.NET Web Application using SQL Server 2008.
When a post code is submitted through the web application, it is encrypted, and then the encrypted value is sent to the database and inserted into a table.
I now need to find a way of querying these post codes from the web application by searching for parts of the post code like:
SELECT PostCode
FROM Table
WHERE PostCode LIKE @PostCode + '%'
How can I do this without storing a decryption function on the database? (Which I believe is bad for security?)
There are really only two options here.
Encrypt the data you are submitting to your select query. That way you are comparing two different encrypted values. Of course, this will fail if you are using a salt..
Don't encrypt the values prior to storage. Instead, just save it normally. If it really does need to be encrypted, use the encryption methods available within SQL Server to automatically encrypt the column: http://msdn.microsoft.com/en-us/library/ms179331.aspx
The purpose of your encryption is basically to protect information "at rest". SQL server can do this by itself.
First Way
Can you send the encrypted Post Code in database from your ASP.Net Application and query it using the encrypted Post code like below....
SELECT PostCode
FROM Table
WHERE PostCode LIKE @EncryptedPostCode + '%'
Second Way
Article on Querying encrypted columns.
Encrypted Columns and SQL Server Performance
So, you send "Court" to your DB, it stores something like "#&%^^" and you want to look for the string "Court" on the DB?
I think the only way is to encrypt the search string on the web application (since you already do it to insert) and compare it with the encrypted version on the DB.
