Best way to encrypt credit card information?

Question

Best way to encrypt credit card information?

WebTechie

SSChampion

Points: 11405
More actions
August 3, 2017 at 5:21 am

#343943

Hello,
We have a payments table that is populated when a user buys something from our website. The credit card is plain text varchar field.
The request was made to encrypt it in the payments table and then move it to another table (bank staging table) to create a file for the bank.
1. One option that I thought of was to add a varbinary column to the payments table.
2. Create a trigger on insert.
3. In the trigger, open symmetric key, populate the varbinary column, close symmetric key and empty plain text column.
Then, in the SSIS package that creates the bank file, have a proc that can call the payments table, open the symmetric key, decrypt the column, get the plain text value, close symmetric key and create bank excel file.
Ok, that was my thought and I actually got it to work well. Management didn't like that approach.
Anyone have a different way to handle credit card information being encrypted and coming into the payments table as a varchar field?
This is using SQL Server 2016. The payments table is in a database that is part of a high availability group.
Thanks.
Things will work out. Get back up, change some parameters and recode.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

WebTechie - Thursday, August 3, 2017 5:21 AM
Ok, that was my thought and I actually got it to work well. Management didn't like that approach.

Why not?
Without changing the data type, that's probably the way you're going to have to do it (unless the insert is in a stored proc)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

theyre probably unhappy with an excel spreadsheet doing the rounds with unencrypted card numbers.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

WebTechie SSChampion Points: 11405 More actions · Answer 3

Management didn't want to add another column. They asked me if I would have a stored procedure handle this. My thought is handle what?

The stored proc or a trigger would be doing the same thing.

1. The credit information is hitting a varchar field.
2. That field needs to be encrypted but we need the ability to get the unencrypted value for to build the bank file.

Am I missing something? Is there a method to having a varchar field but have it be encrypted?

Another option would be to just put the same code that is in the trigger into a stored proc.

Thanks.

Things will work out. Get back up, change some parameters and recode.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

The stored procedure does the insert, taking parameters, meaning that you can change the data type to varbinary, and have the procedure directly insert the encrypted value. A trigger, since it fires after the update starts, can't do that.

And, yes, I'd also be unhappy about a spreadsheet with unencrypted credit card data in it too

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

WebTechie SSChampion Points: 11405 More actions · Answer 5

Thanks Gail.

But the column is varchar and the manager doesn't want to change that. The stored proc could get an encrypted value, but how would I directly place an encrypted value into a varchar column. I think you would still need to add a varbinary column. Unless, you mean, directly add it to the bank staging table?

Thanks as always for your thoughts.

Things will work out. Get back up, change some parameters and recode.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

WebTechie - Thursday, August 3, 2017 6:10 AM
Thanks Gail.
But the column is varchar and the manager doesn't want to change that. The stored proc could get an encrypted value, but how would I directly place an encrypted value into a varchar column. I think you would still need to add a varbinary column. Unless, you mean, directly add it to the bank staging table?
Thanks as always for your thoughts.

I think what Gail is saying is that the procedure does the initial insert of the row into the table and is called from the front end application. Instead of firing an insert statement directly, it called the procedure to do it instead. I also feel that this is the right approach.

Regarding what management wants, they want you to store the card number in an encrypted state. Do they expect you to store it in a varchar column? I hope not. The two options are to create a new varbinary column or to change the existing column to a varbinary, both of which they don't like. If, after explaining the options to them, they still don't like either one, try asking what they have in mind. I guess another option would be to use a home-grown encryption algorithm to change the value you're storing in the varchar column, but you'll likely end up having to change the size of the column anyway and I don't like it because a lot can go wrong. Knowing what I know now, I'd opt for the stored procedure and replacement column.

Edit: BTW, kudos on the effort to encrypt the data in the column. It's nice to hear that it's being taken seriously somewhere, given the number of hacks that seem so prevalent in the world today.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

jonas.gunnarsson 52434 Ten Centuries Points: 1240 More actions · Answer 7

Hi, how many chars is the varchar column?
Possible extend it, and using a base 64 string (hex), and use convert from varbinary to varchar?

WebTechie SSChampion Points: 11405 More actions · Answer 8

Thanks everyone.

This discussion really helps. Originally, I mentioned that we could change the datatype to varbinary and have the web application use a convert function to insert the encrypted value directly.

I will create a stored procedure that does the work of the trigger.

Things will work out. Get back up, change some parameters and recode.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

WebTechie - Thursday, August 3, 2017 6:10 AM
Thanks Gail.
But the column is varchar and the manager doesn't want to change that.

He doesn't want to change it, doesn't want a new column, but wants the data encrypted. Hmmm...

The options the manager has are:
1) A second column that's varbinary
2) Change the data type to varbinary
3) Store the credit card data unencrypted.

Ask him which one he prefers.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

You can propose them to use ROT-5 encoding and even using it twice for improved security.
Then, you can let them know that they shouldn't be taking shortcuts on data security.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323518 More actions · Answer 11

AES encryption results in an encrypted string that is still text, so that might require only an enlargement of the field size, and not a data type change.

I demoed this like five years ago in the thread below, you could use AES256 today instead of the AES128 from my example:

https://www.sqlservercentral.com/Forums/Topic1266687-146-1.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Perry Whittle SSC Guru Points: 234013 More actions · Answer 12

GilaMonster - Thursday, August 3, 2017 7:10 AM
He doesn't want to change it, doesn't want a new column, but wants the data encrypted. Hmmm...
The options the manager has are:
1) A second column that's varbinary
2) Change the data type to varbinary
3) Store the credit card data unencrypted.
Ask him which one he prefers.

+1

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉