CODEWITHSUNDEEP
asp.netsql-serversecuritysql-server-2008encryption
Curtis
Curtis

Reputation: 103338

Querying encrypted values in database

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?)

Upvotes: 0

Views: 2525

Answers (3)

ChrisLively
ChrisLively

Reputation: 88044

There are really only two options here.

  1. 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..

  2. 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.

Upvotes: 4

Pankaj
Pankaj

Reputation: 10095

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

Upvotes: 1

Diego
Diego

Reputation: 36126

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.

Upvotes: 0

Related Questions