VbzCart/docs/tables/cust cards

About

• Purpose: for recording the information necessary to get paid with credit/debit cards

Fields

• WhenInvalid: if NOT NULL, then this number has been permanently destroyed/deactivated; any use of this number is therefore suspicious. Date is the earliest date it was known to be bad. Overrides isActive.
• Address may be no longer necessary, except for handling legacy data
• CardSafe: human-readable (and searchable) summary of "safe" card data. Format subject to change.
• CardCVV: Card security code

History

• 2009-07-09 moved to separate wiki page; no design changes
• 2009-10-07
  • removed CryptAddr, renamed CryptCard as Encrypted
  • added ID_Name
• 2011-11-22 added WhenEnt and WhenUpd fields
• 2013-09-01 added CardSafe
• 2013-09-25 added CardSalt; reordered some fields
• 2015-01-10 It turns out that keeping the salt doesn't work and would undercut part of the security if it did.
  • If you have all of the data except the card number (actually, the first 12 digits of the card number, which actually is effectively 11 digits because of the check digit), that makes it too easy to just try encrypting all possible combinations of digits and see which one matches.
  • As it happens, openssl_public_encrypt() uses a random seed to generate the encrypted data anyway, so the result is different each time.
  • Removing CardSalt because it's useless.
  • Adding WhenEnc so we can reconstruct which private key was used.

Future

• Address should go away, probably without any need to verify that ID_Addr agrees with it
• Records with OwnerName need to have ID_Name set, and then the OwnerName field can be removed
• isActive should eventually go away; probably the best thing is to wait until all isActive=FALSE cards have expired (end of Feb 2010), then set WhenInvalid to each card's expiry date.
• Name should be renamed to NickName.

SQL

<mysql>CREATE TABLE `cust_cards` (

 `ID`         INT  NOT NULL AUTO_INCREMENT,
 `WhenEnt`    DATETIME         NOT NULL COMMENT "when record was first created",
 `WhenUpd`    DATETIME     DEFAULT NULL COMMENT "when record was last updated",
 `WhenEnc`    DATETIME     DEFAULT NULL COMMENT "when Encrypted field was updated",
 `Name`       VARCHAR(31)               COMMENT "human-assigned nickname for this card",
 `ID_Cust`    INT                       COMMENT "core_custs.ID",
 `ID_Name`    INT                       COMMENT "cust_names.ID",
 `ID_Addr`    INT                       COMMENT "cust_addrs.ID of card's billing address",
 `CardNum`    VARCHAR(31)               COMMENT "charge card number",
 `CardExp`    DATE                      COMMENT "expiration date",
 `CardCVV`    VARCHAR(7)   DEFAULT NULL COMMENT "CVV(2) code",
 `CardSafe`   VARCHAR(127)     NOT NULL COMMENT "unencrypted 'safe' card info in human-readable format",
 `Encrypted`  VARCHAR(256)              COMMENT "encrypted card number, CVV, expiration, salt",
 `OwnerName`  VARCHAR(127)              COMMENT "cardholder name, as it appears on card",
 `Address`    VARCHAR(255)              COMMENT "text of address, for redundancy",
 `isActive`   BOOL        DEFAULT FALSE COMMENT "FALSE = don't use this card",
 `WhenInvalid` DATETIME    DEFAULT NULL COMMENT "deactivation timestamp",
 `Notes`      VARCHAR(255) DEFAULT NULL COMMENT "human-entered notes about this card",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql>