Data type for Credit Card
-
What is the preferred datatype for storing Credit card info?
-
Varchar would be my guess (without any experience in that domain).
-
Depends. The prefered way (for us) is INT and store it as a 16 digit number. Then use the front-end app to add the dashes if needed.
HOWEVER, there is one important fact to consider - PCI compliance. PCI compliance requires that credit card data is kept encrypted and behind a firewall.
-SQLBill
-
Here's where I show my in-experience. Bigint assumes that the card number will NEVER have a leading 0. Now I have never seen that on a card but I would make sure it cannot happen, ever, anywhere in the world, from now untill the next 100 years...
Or I'd get ready to revamp the app when needed (shouldn't be a huge change, but it still is one you could avoid by proper design).
-
For a variety of reasons, we've always stored them as varchars, but mostly because I've always stored numbers that weren't used for calculations that way. By the same token, I store phone numbers, SSNs, zip/postal codes, etc. in varchar columns. We don't store any hyphens, spaces, etc., as we always do a lot of pre-processing on the number prior to authorization anyway, most of which is string manipulation, so formatting it after that is a piece of cake. Although currently no major credit cards begin with zero, they actually all have initial numeric sequences by which you can identify the card type. Visa starts with 4, most Discover cards start with 6011, etc. That's not to say that Ninja's RGR'us doesn't have a point, as that could easily change tomorrow.
While the mod 10 validation does perform numerical calculations, you use substring functions to pull out those digits. Also, you use a substring to determine card type, length to validate proper number of digits for the type, and substring (or right) to grab the check digits. It just seems to me that I need to convert less often when I'm working with varchar than with a numeric data type.
Still, plenty of people have no problems storing it as various numeric datatypes, so figure out what works best for your situation.
-
All interesting points for the initial validation. I'm wondering, once the credit card has been validated once, Do you still have to do lots of manipulation with those numbers, or is that info simply encrypted only to be partially shown in reports?
-
Create a rule:
AS
@CrCd LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
Then create a user datatype based on this rule.
Every number will be validated once when stored.
No further validation required.
Probably it would be a good idea to include expire date into this datatype as well.
_____________
Code for TallyGenerator -
First of all... INT is NOT capable of storing a 16 digit number so just forget about that.
Serqiy's idea is great but you must take that one step further... every credit card number must also pass the industry standard LUHN 10 Checksum...
Google LUHN 10 Checksum...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I think this works
SELECT CONVERT(BIGINT, 1234567890123456) AS CCNumber
-
Also make sure you have another constraint to check for LUHN algorithm.
See here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76195 for more information.
N 56°04'39.16"
E 12°55'05.25" -
Agreed... BIGINT will certainly work... But consider a couple of things before you decide to use any numeric datatype for any "numbers" column such as credit card numbers, bank account numbers, SSN's, Zip codes, Telephone "numbers", etc... some will have required leading zeros (obviously, not CC#'s) and the fact that, many times, you'll need to do such things as verify length as compared to what the first digit is (CC#), verify a range of numbers according to what the first 2 digits are (CC#), manipulate individual digits (CC#, Luhn 10 checksum is a good example), provide the "last 4 digits" (CC#, Bank Account, SSN), and parse the first and second "triplet" for Area Code and Exchange on telephone numbers, etc. Yes, all of those things can be done mathematically, but it's a lot easier to do with strings.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry... didn't read the whole thread... I pretty much repeated what David McFarland already posted.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I use binary and save it encrypted.
-
I will throw in some extra stuff here.
1) There are some simple checks you can do without LUHN such as
VISA starts 4
MasterCard starts 5
Discover starts 6011
Lenghts are standard
2) You are dealing with sensetive data which needs to be protected, I would store as maybe a varchar(100) and use a one way protection scheme (using CAPICOM APIs is easy enough) to encrypt the data you store against say the users password or other unique data. But you should take care not to store unencrypted just in case you are ever compromised. Fed gov won't take lightly to you failing to protect the customer.
3) For more protection if web based do not have the SQL Server running on the same box as the Web server and do your best to harden the system against attacks. As well, the SQL Server should talk multiprotcol for encryption. And have a valid certificate as well as use only SSL on the server.
4) Consider requiring changing passwords on that system often or at least complex passwords. And disable accounts that fail multiple times for sure so brute force or guessing attacks fail.
5) Even when confirming data make sure you do not display the entire value just maybe the last 4-5.
Not sure how much involvement you have in desin besides DB but protect yourself by showing you are unwilling to accidently expose customers, you know people love scapegoates.
-
I was just doing my Devil's advocate here. My instinct was varchar + encryption, looks like I was pretty much dead on!
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply