0

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

Curtis
  • 101,612
  • 66
  • 270
  • 352

3 Answers3

4

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.

NotMe
  • 87,343
  • 27
  • 171
  • 245
  • Cheers, so should I not be encrypting values in this way then? – Curtis Jan 31 '12 at 15:05
  • @Curt: Not if you want them searchable. Generally speaking, fields such as passwords should be one-way hashed with salt. Fields that you need to do mass searching for should be handled by either reversible encryption or, preferably, the encryption mechanism of the storage container, in this case SQL server. – NotMe Jan 31 '12 at 16:01
  • @Curt: If you have a situation where only 1 app will decrypt the field and only after it has already retrieved the info, then you can rely on the apps encryption keys. However, I'd say this is a brittle approach. The keys inside your database server are slightly more secure than ones found on your web server due to how the different servers are exposed. Namely the web server is directly accessible over the internet and your database server shouldn't be. – NotMe Jan 31 '12 at 16:03
  • @ChrisLively - Cheers cheers, I assumed I was doing it the right way and was struggling to find a good way of solving this, but it seems like I was going about it all wrong! So I should concentrate on the database security and not worry so much about the individual data inside? My worry is that if a development team is given access to the database, they have access to all this stored information. – Curtis Jan 31 '12 at 16:15
  • @Curt: The user accounts that have been granted access to that encryption key will have access to decrypt the data. This is no different than giving a developer access to your production web server where that key is located. Generally speaking: developers should NEVER have access to production data, especially in a situation in which the data needs to by encrypted. Instead you should have dedicated operations people who can access that, who are appropriately background checked. If a developer needs to replicate an issue then your ops people can work with them to set that up. – NotMe Jan 31 '12 at 16:20
  • Security is more than just the technology; it has to do with the people as well. When you understand that *most* data loss is the result of insiders then you can start properly defending against them. Trust No One except those you absolutely must; and verify them on an ongoing basis. As a side note: in 20+ years of working in this industry I've never encountered a situation that truly could only be debugged using production data. Easier? absolutely; Necessary? no. – NotMe Jan 31 '12 at 16:25
  • @ChrisLively - Thanks for your help Chris, I think you're right in saying that the production database shouldn't be accessed directly by developers. With point 2 of your answer, would encrypting the column not be a waste if someone could access the database anyway? Wouldn't they be able to decrypt in themselves? – Curtis Feb 01 '12 at 09:28
  • @Curt: The user account can only decrypt it if that account has access to the keys. Read the following: http://sqlsafety.blogspot.com/2010/10/encrypting-column-level-data-in-sql.html – NotMe Feb 01 '12 at 15:07
1

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

Pankaj
  • 9,749
  • 32
  • 139
  • 283
0

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.

Diego
  • 34,802
  • 21
  • 91
  • 134