1

I'm working on a Web App where the data is quite sensitive and is required to be encrypted before it is stored in the database. Now there are two ways through which I can achieve this:

  1. Encrypt data in the code
  2. Encrypt data in stored procedures (using symmetric keys)

Now I don't know which approach will be better regarding security and performance.

Rida Iftikhar
  • 23
  • 5
  • 5
    What is your threat model? That someone could get a copy of the database? If you encrypt inside the DB, how does the DB get the key? If access to the DB also means access to the key, encryption within the DB does not seem secure. – amon Jun 15 '17 at 06:26
  • 1
    @amon Yes, the threat is that someone could get a copy of the database. Well I'll be using the SQL Server's TDE feature to keep the DB secure. – Rida Iftikhar Jun 15 '17 at 06:35
  • 1
    @RidaIftikhar If you're using Transparent Data Encryption then the encryption process is... transparent. You shouldn't have to do either option 1 or 2. – Tim Jun 15 '17 at 10:15
  • @Tim Yes but TDE does not store data in the tables in encrypted form and the client persists that we do so. – Rida Iftikhar Jun 15 '17 at 10:17
  • 3
    @RidaIftikhar It does - that's its purpose. *"TDE protects data "at rest", meaning the data and log files."* https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption-tde – Tim Jun 15 '17 at 10:42
  • 1
    One other aspect to consider: If you encrypt the data in a stored procedure, then you first have to get the data *to* that stored procedure, which means that you're shipping it across your network *in plain*. This may or may not be a concern for you. – Phill W. Jun 16 '17 at 10:41
  • @PhillW. I actually is a concern, thanks for pointing that out! – Rida Iftikhar Jun 16 '17 at 13:06

2 Answers2

5

In general I would advise against using stored procedures. I've worked at a company that had a lot of logic in stored procedures and it made it really difficult to reason about and test. We ended up migrating away from stored procedures over time. They are database-specific, so if you ever want to move away from SQL Server, you will have to deal with the procs.

Code is so much easier to maintain and test, and there are strong and mature cryptography libraries available. Performance-wise, code is more horizontally-scalable than SQL Server.

I vote code.

Samuel
  • 9,137
  • 1
  • 25
  • 42
  • Thanks for your comment, but the thing is that I _have_ to use stored procedures. – Rida Iftikhar Jun 15 '17 at 10:06
  • Do you mean generally you must use stored procedures? If your organization is mandating stored procedures, I would still urge you to use code whenever possible and try to avoid creating new ones :) – Samuel Jun 15 '17 at 21:28
  • 1
    @RidaIftikhar If you *have* to use stored procedures then why did you even ask the question? Wouldn't the better question have been, how do I best manage encryption/decryption of sensitive data in stored procedures? – maple_shaft Jun 16 '17 at 11:24
  • @maple_shaft I need to use stored procedures to have all the queries in the compiled form, I don't _have to encrypt_ data there. – Rida Iftikhar Jun 16 '17 at 13:05
2

The concern isn't so much about where you perform encryption/decryption operations, but how you are managing keys, encrypted data, and how you actually go about the encryption itself.

The immediate concern I have with doing this through the use of stored procedures is that the key will most easily be accessed from within the database tier. This presents a vulnerability in that your database now presents as a component that if it alone becomes compromised, then your sensitive data is almost assuredly compromised as well.

The above is a REAL vulnerability that will prevent you from achieving standards compliance across a number of different standards (Eg. HIPAA, PCI, etc..)!

Ultimately the weakest point in a symmetric encryption scheme is weakness in how keys are managed. Some of the most secure (and expensive) solutions utilize something called an HSM that will securely store keys and provide them as needed to applications in a secure way.

Other ways you can solve this problem is by encrypting the channel between your application and database servers, and requiring the application servers to provide the key to stored procedures for encryption/decryption. This is a good solution because an attacker that compromised the database would not have access to the key, or if the attacker compromised the application server then they would not have access to the database (theoretically). This prevents a single point of failure.

maple_shaft
  • 26,401
  • 11
  • 57
  • 131
  • Thanks for explanation, it cleared a lot things for me! And after reading through comments and answers, I think it would be a better choice to encrypt everything in the code. – Rida Iftikhar Jun 16 '17 at 13:02
  • @RidaIftikhar Ok, that is honestly the least problematic approach. Your data can securely live on the database encrypted, and the key can be safely kept in an HSM or on your application server somewhere safe and protected. This gives you that protection where if either application or database tier is compromised then your data is still safe. – maple_shaft Jun 16 '17 at 13:51
  • 1
    @RidaIftikhar this is not an “either-or” but possibly a “both”: TDE protects data at rest, but not data accessed through a database connection. Application-level encryption protects individual records, but not their relationships (metadata). So these encryption schemes are complementary! And of course encryption does not solve all problems: if an attacker gains remote code execution within your app, they have plaintext access to your data because the app must have plaintext access to the data. – amon Jun 16 '17 at 17:19