Take the 2-minute tour ×
Information Security Stack Exchange is a question and answer site for Information security professionals. It's 100% free, no registration required.

I'm working on an ASP.NET web application that will have to store sensitive information. I would like to encrypt the sensitive fields to protect against any possible SQL injection vulnerabilities. However, I also will need to report on these fields. For these reasons, I've excluded transparent database encryption (since it wouldn't provide protection against SQL injection) and application layer encryption (since it would make it hard to report against the data) and I'm left with database level encryption.

My plan is to use SQL Server's EncryptByPassphrase function, with an SSL connection to the database to protect the passphrase over the wire. The key would be stored in the web.config, protected by the Windows Data Protection API at the machine level.

Is this a good plan? What are the potential vulnerabilities?

share|improve this question
    
How would you identify valid sql query from attack type? Which would be decrypted and which be returned as error message. –  Saladin Mar 5 '13 at 17:31
    
@asadz the thought is that arbitrary SQL statements cannot retrieve the sensitive fields. You would need to get the web application to retrieve the key first. And you can't inject code into the complied web application to do that. –  John Mar 5 '13 at 17:34
    
So if i get the flow correct first it checks if the stmt is valid means void of sql attack strings then let it retrive the key to talk to db? –  Saladin Mar 5 '13 at 17:44
    
@Saladin Actually in this scenario, the web application will only decrypt the key from the web.config on the pages or reports where the sensitive info needs to be retrieved. The idea is that ASP.NET has already authenticated the user through the auth cookie. This prevents an unauthenticated user from finding a SQL injection vulnerability on any arbitrary page and accessing the plain text info. This does not protect the info in the case of a stolen session. –  John Aug 7 '13 at 20:10

2 Answers 2

up vote 3 down vote accepted

Yes, it's a good plan, but there are still a number of potential vulnerabilities. Really, the only attack that you're mitigating is the attack you mentioned, the ability to grab the cleartext data with an arbitrary SQL statement. You're not mitigating any other attacks, for instance, a malicious user who uses SQL injection to elevate their privileges within the application so that they can view a pages where the application expects to display this date in cleartext.

So, the real solution is defense in depth. This is a fine part of that solution (and indeed sensitive data should be encrypted at rest) but in order to effectively protect it you also need additional safeguards, such as:

  1. General SQL injection protection strategies such as only allowing access to the application user via parameterized stored procedures and denying access to the underlying tables completely.

  2. Protection against attacks relying on arbitrary code upload and execution

  3. Protection against XSS and session stealing attacks that might allow a malicious user to capture authentication cookies from an administrator.

The data is only safe as the weakest link in the overall application security, so remember that direct attack on the data itself via SQL injection is not the only thing you need to think about in order to protect it.

share|improve this answer
    
Yes I understand it's only part of it. But sensitive information just sitting there in plain text makes me rightly nervous. Good to know I'm on the right track with this portion of the strategy. It's already standard practice at my company to use parameterized queries for absolutely everything. What are you referring to when you talk about arbitrary code upload and execution? –  John Mar 5 '13 at 19:25
1  
The most common vector is a feature that allows users to upload files, but doesn't both 1) validate that they're safe and 2) store them in a non-executable location. An example would be if you had a feature for users to upload and embed images in posts, if it's not properly implemented, there's the potential for someone to figure out how to upload an ASPX file instead, and then run it within the context of the application with all the privileges of your own code. –  Xander Mar 5 '13 at 19:32
1  
For further examples, there have been several of these in WordPress, where improperly secured upload features have allowed malicious users to upload and then execute arbitrary PHP files. –  Xander Mar 5 '13 at 19:35
    
Oh, ok. I understand. Thanks for mentioning that possibility too. –  John Mar 5 '13 at 19:41
    
Oh, by the way, I think MS SQL's EncryptByPassphrase uses Triple DES. Is that algorithm still considered secure, should I even be concerned about that? –  John Mar 6 '13 at 15:35

I would like to encrypt the sensitive fields to protect against any possible SQL injection vulnerabilities.

This is probably wrong at the outset. If your database is accessed through the application, in what circumstance would the application not decrypt the data? SQL injection is an application vulnerability, and hence at a level higher than when your data is decrypted.

Encrypting data in the database is meant to prevent data exposure by unauthorized access to the database. Since your application is authorized and can decrypt, it is not really protected against injection through this method. What you should be doing to protect against injection is using parameterized queries.

What you would be protected against is somebody compromised the database server directly without being able to compromise the web application.

You would need to get the web application to retrieve the key first. And you can't inject code into the complied web application to do that.

A quick search for "buffer overflow web application" should convince you otherwise. Also, the key would be exposed if they could somehow read the application.

share|improve this answer
    
spot on cleared the confusion. –  Saladin Mar 5 '13 at 19:15
    
I do use parameterized queries. Points taken, though. My point was that unless you could get the web application to pass the decryption key to the database, you wouldn't be able to extract the sensitive information with a simple injected select statement. Getting the application to give up the encryption key is at least theoretically harder. –  John Mar 5 '13 at 19:21
    
I'd just like to know if my scenario is a valid way of dealing with this security issue. How does the industry deal with this sort of situation? –  John Mar 5 '13 at 19:22
    
@John - Ideally, you'd split your back-end (database access and business logic) and middle-tier (front-end generating part) in a multi-tiered environment, and control access between these two tiers. Your back-end part could be a service responding to requests on a completely different port and have a different set of access rules to your externally (internet, intranet, whatever) exposed middle-ware. No need to encrypt database, if you can control access to it. And not to mention encrypting records would be a nightmare to program for, possibly exposing your security layer in the process. –  Noordung Mar 5 '13 at 19:33
    
@John The answer is going to vary based on your specific application. Security is all about risk management, so what are the requirements for this data, what are the risks, and what are the appropriate mitigation? Those are going to be different for every app. One option might be that if you only need to report on the data, maybe it doesn't need to live in this database at all. Maybe you ETL it nightly into a reporting database and delete it from the online database completely. There are many possibilities. What minimizes the risk while still allowing you to do what you need to do? –  Xander Mar 5 '13 at 19:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.