-1

Let's say two programmers are working on a website for a store. They've created a small database for an online store. Users would have to register and provide a username(e-mail address) password,home address, and have the option to store the credit card information on their account. Obviously, you don't want attackers to get any of this information. Assume one of the programmers was careless and didn't filter SQL statements and didn't take proper measures to ensure malicious code wouldn't be accepted. The programmer that is working on the database, what can he do it ensure that, even if an attacker was able to enter SQL statements into the form and gain access to the database, it would be encrypted? What type of encryption is this case, would work best?

According to this SQL Server doesn't use salting when encrypting.

What I'm looking for is, if a user entered the correct username/password he would gain access to his own account, but should an attacker use SQL injection, he would get an encrypted database that he must attack in order to get the information.

Note: The programmers are using SQL Sever 2012

Improve this question
18
  • You are looking to protect all data? Or just the password hashes? Are you looking to protect the data such that even if the remote attacker had a copy of the source code and related configuration, they still wouldn't be able to access the data?
    – ChrisInEdmonton
    Jan 14, 2014 at 15:21
  • @ChrisInEdmonton - Well, what I am looking for is, in SQL Server, how would you store user data, such that, if an attacker were to get a hold of the database, by SQL injection, he would get random strings and would have to decrypt it. It would be impossible, cause said hacker, doesn't have the key.
    – Charles Whitfield
    Jan 14, 2014 at 15:27
  • 1
    Storing Credit Card information is "can of worms" that you don't want to open. In regards to Database encryption you have to decide what attack vectors you are trying to protect from. This question is too broad to be answered.
    – Vladimir Oselsky
    Jan 14, 2014 at 15:35
  • @SaUce - I stated SQL injection as a possible attack.
    – Charles Whitfield
    Jan 14, 2014 at 15:36
  • 1
    What @techie007 recommends would prevent attacker from seeing data belonging to other users, only data of (hacked) user will be seen to attacker but if he found a way to break into 1 users account he will be able using SQL injection to determine other users and than log into each account and get their data. Bottom answer is NO. There are entire books that are written on Secure Coding.
    – Vladimir Oselsky
    Jan 14, 2014 at 16:18

2 Answers 2

Reset to default
2

SQL Server encryption is for protecting data at rest, to protect backup files and prevent someone from accessing the database data files in a raw format.

it is NOT for protecting data from an app that has been written and configured to access that very data. keep in mind, attacks on your infrastructure proxied through one of your applications use that applications credentials and privileges to perform the attack. its like giving your housekey to your lowlife bother-in-law so he can dogsit, but expecting him not to be able to steal your TV. Even if you did your encryption at the app tier, the app has the keys to teh encryption, so any attackers do as well. the only real option is to tie the keys to the users, not the app itself, so that no user could ever decrypt cc data that was not their own.

Second, in order to legally store credit card data, you must meet industry PCI standards for security, which are well beyond the scope of this forum.

Everything about your question tells me that you should never ever ever let your coders store CC data. you guys are no where close to where you need to be to handle that data securely.

Edit: I note you have removed all references to credit cards from your OP, so if that is not your goal, disregard my comments on PCI standards. My comments on SQLServer encryption remain valid.

Improve this answer
8
  • This is just hypothetical. I just wanted to understand what encryption method could be used for SQL Server in the way I described.
    – Charles Whitfield
    Jan 14, 2014 at 15:24
  • use a login system that allows you to store crypto certificates for each user. then at the application layer (not sql server) encrypt the sensitive data for that one user specifically before storing. that way, even if the app is compromised, the only the compromised users data that can be accessed, and the app becomes incapable of exposing all users data. if the application can access the data at all, it is vulnerable to sql injection. if you encrypt before you store though, an attacker would get gibberish.
    – Frank Thomas
    Jan 14, 2014 at 15:27
  • How would a company, for example, Microsoft, store the username/passwords for e-mail accounts in an encrypted way? So, if an attacker tried SQL injection, all he would obtain is random strings that he would have decrypt using a key he doesn't have. So Assume Microsoft is using SQL Server 2012 for the database. Sorry ignore this. I was typing this as you were making edits to your above post.
    – Charles Whitfield
    Jan 14, 2014 at 15:31
  • sites don't store passwords. they store a (salted) hash of the password, so that if it is ever stolen, the attacker can't use that info to login. this is one place where strong one-way hashing is better than two way encryption. What got adobe in trouble was that they encrypted their user account data instead of hashing the passwords, and over time, the ency algorithm they used became vulnerable (3des if you are interested). use SHA512 or an equivalent strenght hasher ( do not use SHA1 or MD5)
    – Frank Thomas
    Jan 14, 2014 at 15:33
  • Thanks for the clarification. So where exactly are the passwords stored? "then at the application layer (not sql server) encrypt the sensitive data for that one user specifically before storing". Thanks for that. Is there a website or tutorial that shows how this could be done?
    – Charles Whitfield
    Jan 14, 2014 at 15:42
0

Please refer ASP.Net membership provider's dbo.aspnet_Membership table. It stores a user level password salt and encrypts the passwords before storing them. Very similar to your requirement.

There is not out-of-the-box functionality available in SQL Server, unless you use the option to encrypt a column. But then you are stuck with having to use the varbinary data type and also face a possible performance degradation (20% slower as per some articles). Refer: http://technet.microsoft.com/en-us/library/ms179331.aspx

Improve this answer

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