0

I'm attempting to script a credit card number in sets of four, going up to sixteen digits. I'm using CONCAT and SUBSTRING to do this.

Notes: I did not receive an error, it ran but the output was not what I wanted.

What I wanted was this: xxxx-xxxx-xxxx-xxxx What I got: 2147-4836-47- (Not just in one spot, but in all four entries)

Is this occurring because I am attempting to break up an int variable in subscript statement? Can you use int characters in a substring()? What about BIGINT?

Regardless of why I changed it to a varchar, and ran it with CONCAT and SUBSCRIPT. All appears to be well, but further input is welcomed. :)

Created Table:

CREATE TABLE credit_cards
(
credit_card_ID          int(10)             UNIQUE,
credit_card_num         int(20)         NOT NULL,
exp_date                DATE                NOT NULL,
card_fname              varchar(35)         NOT NULL,
card_lname              varchar(35)         NOT NULL,
security_code           int(5)              NOT NULL,
bill_street             varchar(50)         NOT NULL,
bill_city               varchar(25)         NOT NULL,
bill_state              varchar(2),
bill_zip                int(10)             NOT NULL,
PRIMARY KEY (credit_card_ID)
);

Insert Data:

INSERT INTO credit_cards VALUES 
(1, 0025184796520000, '2016-08-13', 'Sarah', 'Jones', 3351, '2490 Paseo Verde parkway, suite 150', 'San Diego','CA',92124),
(2, 7896541232548526, '2017-09-21', 'Desmond', 'Lowell', 1204, '3201 Kelsey Street, suite 109', 'San Diego','CA',92174),
(3, 1234567890123456, '2018-02-11', 'Mark', 'Jefferson', 1591, '876 Silverado Street, suite 304', 'Henderson','NV',89162),
(4, 4001330852539605, '2017-01-10', 'Jaime', 'Evans', 8879, '924 Shady Pines Circle, suite 120', 'Summerlin','NV',89074);

Select Data

SELECT credit_card_ID as 'Card ID', 
        CONCAT(
        SUBSTRING(credit_card_num, 1,4), '-',
        SUBSTRING(credit_card_num, 5,4), '-',
        SUBSTRING(credit_card_num, 9,4), '-',
        SUBSTRING(credit_card_num, 13,4)) as 'Card Number',
        security_code as 'Security Code',
        DATE_FORMAT(exp_date, '%m-%d-%Y') as 'Expiration Date', 
        CONCAT(card_fname, ' ', card_lname) as 'Customer Name',
        CONCAT(bill_street, ', ', bill_city, ', ', bill_state, ' ', bill_zip) as 'Billing Address'
FROM credit_cards
ORDER BY exp_date ASC;
Sierra
  • 327
  • 4
  • 11
  • If you care about leading zeros, then you don't have a number. You should use a string. You can just use `lpad()` to append them and that should fix your code. – Gordon Linoff Mar 29 '16 at 22:17
  • Thanks for the advice. :) I've never heard of lpad(), but I will be researching it thoroughly. I just changed it to VARCHAR, and found something else that I think made it wonky. It works just fine now with CONCAT and SUBSCRIPT, but I'm very curious about lpad. I'll be sure check it out. – Sierra Mar 29 '16 at 22:21
  • Additionally you have to use `BIGINT`. An `INT` won't take 20 digits. See: [MySql Docs](http://dev.mysql.com/doc/refman/5.7/en/integer-types.html) – robsn Mar 29 '16 at 22:23
  • Oh! That's really good to know! Thank you very much! :) – Sierra Mar 29 '16 at 22:24
  • NOTE: The `LPAD` function is a string function. It operates on character strings. Used with an integer argument, the argument is automatically converted to VARCHAR, and then LPAD operates on the returned string. That is, there's an implicit cast/convert from integer to varchar. – spencer7593 Mar 30 '16 at 01:19
  • PCI DSS [https://www.pcisecuritystandards.org/pdfs/pci_fs_data_storage.pdf](https://www.pcisecuritystandards.org/pdfs/pci_fs_data_storage.pdf) – spencer7593 Mar 30 '16 at 01:39

2 Answers2

1

There are two problems:

1) INT can't take 16 digits, you have to use BIGINT.

Integer types documentation for MySQL 5.7

CREATE TABLE `credit_cards` (
    `credit_card_ID` INT(10) NOT NULL,
    `credit_card_num` BIGINT(16) NOT NULL,       -- use BIGINT here
    `exp_date` DATE NOT NULL,
    `card_fname` VARCHAR(35) NOT NULL,
    `card_lname` VARCHAR(35) NOT NULL,
    `security_code` INT(5) NOT NULL,
    `bill_street` VARCHAR(50) NOT NULL,
    `bill_city` VARCHAR(25) NOT NULL,
    `bill_state` VARCHAR(2) NULL DEFAULT NULL,
    `bill_zip` INT(10) NOT NULL,
    PRIMARY KEY (`credit_card_ID`),
    UNIQUE INDEX `credit_card_ID` (`credit_card_ID`)
);

2) Leading zeroes are usually cut off. You have to use LPAD.

LPAD documentation for MySQL 5.7

LPAD(credit_card_num, 16, 0)

Read: Left pad credit_card_num with 0s to a length of 16 digits. The query could look like this:

SELECT credit_card_ID as 'Card ID', 
        CONCAT(
        SUBSTRING(LPAD(credit_card_num, 16, 0), 1,4), '-',
        SUBSTRING(LPAD(credit_card_num, 16, 0), 5,4), '-',
        SUBSTRING(LPAD(credit_card_num, 16, 0), 9,4), '-',
        SUBSTRING(LPAD(credit_card_num, 16, 0), 13,4)) as 'Card Number',
        security_code as 'Security Code',
        DATE_FORMAT(exp_date, '%m-%d-%Y') as 'Expiration Date', 
        CONCAT(card_fname, ' ', card_lname) as 'Customer Name',
        CONCAT(bill_street, ', ', bill_city, ', ', bill_state, ' ', bill_zip) as 'Billing Address'
FROM credit_cards
ORDER BY exp_date ASC;

See it live.

robsn
  • 734
  • 5
  • 18
  • Thank you very much for the preview of LPAD. That is actually very interesting and I'm quite excited to toy around with that! :) – Sierra Mar 29 '16 at 23:33
0

For whatever it's worth, I wouldn't use an integer at all to store credit card numbers. I'd use CHAR(16)--not VARCHAR since the size of the string never varies. I'll explain why.

There isn't a reason to use any sort of integer variable unless you are going to be using the numbers in some form of computation. It's easier to store them as strings (i.e. CHAR, VARCHAR, NCHAR or NVARCHAR), as you are finding out from some of the hoops you have to jump through to get what you want. Even if you are going to do some form of check digit verification, you will have less trouble stripping off numbers from a character string and converting the result to an integer than you will getting a subset of number values from an integer.

In fact, when you apply SUBSTRING to an integer, the parser implicitly converts the integer to a string before applying it, so you're creating extra overhead when you use some form of integer type in this situation. You'll be well served to ask yourself if the extra overhead is needed, and if so why. If it isn't needed you should consider simplifying as a matter of general principle.

It's important to keep in mind that numbers are often best visualized as a subset of the alphanumeric character set, and you might ask yourself just why you need to consider credit card numbers as mathematically-enabled numbers (i.e. integers) rather than as pure character data. It's easy to go wrong here, since it's a bit counterintuitive: a standard beginner error is to store ZIP codes as integers and down the line wonder how to get the leading zeros in the East Coast numbers to work out. Anybody who has tried to put ZIP codes into an Excel spreadsheet has come across this one.

BobRodes
  • 5,990
  • 2
  • 24
  • 26
  • I concur about not using integer datatype for credit card number, Or for social security number. The point about these values not being used in expressions performing arithmetic is valid. But one possible reason one might consider using a numeric type is that doing so enforces a constraint on which "characters" can be stored in the column. I'm not saying that this consideration overrides other concerns. I'm just suggesting that some consideration might have been given to a way to enforce rules about which characters would be allowed. A native "credit card number" datatype isn't available. – spencer7593 Mar 30 '16 at 01:25
  • @spencer7593 . . . Not all numbers are valid credit card numbers (see Luhn Algorithm), so merely checking digits isn't sufficient. – Gordon Linoff Mar 30 '16 at 01:30
  • @GordonLinoff: You are absolutely right. I didn't imply that an integer datatype was sufficient for validating a credit card number. I was addressing Bob's assertion *"There isn't a reason to use any sort of integer variable unless you are going to be using the numbers in some form of computation."* I don't believe Bob's assertion that is strictly true, because I believe there are occasions where an integer type can be suitable for values that aren't going to be used in computation. (My previous comment agreed that integer wasn't an appropriate datatype for storing credit card numbers.) – spencer7593 Mar 30 '16 at 01:45
  • @spencer7593 a cheaper way to do that would be to add a CHECK constraint to the field definition. For example: `CHECK ( [credit_card_num] LIKE '[3-6][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]' )` would restrict entries to major credit cards only (they begin with between 3 and 6), while allowing 7 in the first digit would include gas station cards. Also, my assertion is probably better characterized as "generally true." :) Finally, you do have to add a constraint for Luhn Algorithm as well, as Gordon points out. – BobRodes Mar 30 '16 at 03:02
  • Unfortunately, MySQL doesn't enforce CHECK constraints or user-defined types. So, when we are mapping objects to the relational model, there's an "impedance mismatch". ORM frameworks do a decent enough job and navigate around the "store everything as a string". Some relational databases (other than MySQL) do provide better support for user-defined types (or "domains" as Date et al. would refer to them). So we are still left with the problem of mapping more complex types, such as "credit card number" into actual datatypes supported by the database. – spencer7593 Mar 30 '16 at 03:49
  • The decision map "credit card number" to `CHAR` may be the best, because it's better than the other options available. (To avoid running afoul of a rule such as "don't store as integer unless used in a computation", we hope that one wouldn't opt to store a "credit card number" by breaking it into chunks of four decimal digits each, and storing each chunk as a SMALLINT, justifying that with the claim that the expression `chunk1*100000000000+chunk2*100000000+chunk3*10000+chunk4` qualifies as a "computation". – spencer7593 Mar 30 '16 at 04:02
  • I do understand your point, in particular in regards to "credit card number" the reasons we would choose not to map that to SQL integer type, or to `DECIMAL(16,0)`. I'm meant only to suggest that hard and fast rules like "there is no reason to map to integer if its not used in computation" doesn't hold up under scrutiny. (As an example, I commonly use integer types for surrogate keys. And for booleans. And those aren't used in a numeric computation.) Yes, I agree that CHAR is a better choice for "credit card number", but the reasons go beyond "its never used in computation". – spencer7593 Mar 30 '16 at 04:16
  • Personally, I'm not nearly as concerned with the datatype used for "credit card number" as I am with the (apparent) storage of the CVV/CVV2 security code and of the card holder's personal information along with the credit card number. That's a significant violation of PCI DSS. – spencer7593 Mar 30 '16 at 04:42
  • @spencer7593 I had no idea about MySQL not enforcing CHECK constraints. My bad, and thanks for mentioning it. A little googling around comes up with [this](http://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working) alternative, which uses a BEFORE INSERT trigger. Is that the way to do this in MySQL? – BobRodes Apr 05 '16 at 06:24