3

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?

Improve this question
4
  • How would you identify valid sql query from attack type? Which would be decrypted and which be returned as error message.
    – Saladin
    Mar 5, 2013 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, 2013 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, 2013 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, 2013 at 20:10

3 Answers 3

Reset to default
4

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.

Improve this answer
5
  • 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, 2013 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, 2013 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, 2013 at 19:35
  • Oh, ok. I understand. Thanks for mentioning that possibility too.
    – John
    Mar 5, 2013 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, 2013 at 15:35
3

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.

Improve this answer
5
  • spot on cleared the confusion.
    – Saladin
    Mar 5, 2013 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, 2013 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, 2013 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.
    – TildalWave
    Mar 5, 2013 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, 2013 at 19:45
0

I second @Jeff Ferland in that there is a confusing statement in your question:

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

SQL injection is an application level problem, while DB encryption is meant to avoid insider threat, or someone who may have direct access to your database.

I am not very clear about which DBMS you are using, but I think what you need here is a column-based, database-level encryption. In the commercial world, MyDiamo is the common solution for open source DB.

If you're using proprietary DBMS(Oracle, MSSQL, etc.) for a database-level encryption with Access Control capabilities, you'll probably want to go with MyDiamo's sister solutions suite D'Amo. Most other solutions on the market will be mainly file-level encryption which as you stated above, would thwart your purpose.

Note: I have been working as a DB encryption consultant and dealt with the above solutions for a while.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .