6

Often I have stored credit card numbers in varchar(16). That works, but it takes 17 bytes per number.

Storage is not that big a deal, but I like to be efficient, for both storage requirement, and table search time.

If I could use decimal(16) unsigned, I could cut the storage requirement to 7 or 8 bytes, and still preserve readability as well as much of the compatibility.

This would strip leading zeros. Can I depend on all credit card numbers starting with a non-zero number?

skaffman
  • 398,947
  • 96
  • 818
  • 769
700 Software
  • 85,281
  • 83
  • 234
  • 341
  • Are the credit cards always 16 digits? Just pad-left with zeroes if so. – tenfour Aug 23 '11 at 17:12
  • 7
    Are you storing billions of credit card numbers for this to make a difference, then also you are PCI DSS compliant yet do not know the answer to this question? I'd just say wow, that you can do this in only a varchar. Encryption algorithms must have come on a long way! – Layke Aug 23 '11 at 17:13
  • @tenfour: Not always. Sometimes they are 15 digits. – 700 Software Aug 23 '11 at 17:16
  • 2
    I'd say "efficiency" is not exactly what you want when storing credit card numbers. You want encryption. Which leads to the next thing, I can not envision a system that you would honestly want to be able to search through the entire credit card number. Last 4? possibly, but not the whole thing. This screams failure in every possible way. – NotMe Aug 23 '11 at 17:19
  • 4
    Security should be your primary concern not size. **You shouldn't store credit card information without encryption**. consider [AES_ENCRYPT() and AES_DECRYPT()](http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html), also read [using mySQL to store credit card info](http://forums.mysql.com/read.php?30,14020,31632) – Pedro Lobito Aug 23 '11 at 17:23
  • @tenfour: Uh, no. Leading zeroes are significant (in other words, credit card numbers are *not* integers), and the length can be 13+ digits. – Piskvor left the building Aug 23 '11 at 17:25
  • 1
    @Tuga: You shouldn't be storing credit card numbers *at all*, if you can help it, but that's slightly off-topic ;) – Piskvor left the building Aug 23 '11 at 17:27
  • @Piskvor: couldn't agree more, but if you've to store it at least encrypt it. – Pedro Lobito Aug 23 '11 at 17:29
  • 1
    @Tuga: I'm not really in disagreement - if you're storing card numbers, you need to achieve PCI DSS compliance, which includes, but is not limited to, encryption. (obligatory: IANAL, and YMMV in different countries) – Piskvor left the building Aug 23 '11 at 17:33
  • 6
    Lol at how this was closed off because it's "off topic". This is a great question. – But I'm Not A Wrapper Class Jul 29 '13 at 20:22

3 Answers3

13

Credit card numbers (like phone numbers and postal codes) are not numeric and should never be stored in a numeric datatype. They are inherently string data. Numbers that are not intended to be used in mathematical calculations (except autoassigned integers that are used as ids) are string data, they will be used as string data, they will be queried as string data.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • What if I always add "1" to the card number and store it that way? I could keep it in integer type big enough (like uint64_t in c++, for example) and then if I'd like to compare two card numbers, it'd be much less expensive operation. – Jezor Sep 03 '16 at 23:58
  • 1
    SInce credit card numbers should always be encrypted, comparing them is something you should never be doing. Store them as some sort fo character data type and encrypt them. – HLGEM Sep 06 '16 at 13:34
  • My application is meant to use credit cards that way (unencrypted), it must efficiently search for BIN numbers of credit cards, so it will perform a lot of comparisons. My question is: what solution will be more efficient, and not necessarily more secure? (: – Jezor Sep 06 '16 at 14:14
7

According to Wikipedia, the first digit can indeed be 0:

The first digit of a credit card number is the Major Industry Identifier (MII), which represents the category of entity which issued the credit card. Different MII digits represent the following issuer categories:

  • 0 – ISO/TC 68 and other future industry assignments
  • etc.

So no, I don't think you'd want to use storage that omits leading zeros.

Community
  • 1
  • 1
Tim Jones
  • 1,766
  • 12
  • 17
3

Only ISO/TC 68 credit cards start with leading zeros (see also the Wikipedia entry for ISO/IEC 7812). So it seems that they'd be very rare, but possibly existing

michel-slm
  • 9,438
  • 3
  • 32
  • 31