Difference between revisions of "VbzCart/tables/cust cards"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(moved from "customer tables" page)
 
(→‎Future: NickName)
 
(8 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
==About==
 
==About==
−
* '''Purpose''': for recording the information necessary to get paid by credit/debit cards
+
* '''Purpose''': for recording the information necessary to get paid with credit/debit cards
−
* '''Fields''':
+
==Fields==
−
** if '''WhenInvalid''' is 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.
+
* '''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
+
* '''Address''' may be no longer necessary, except for handling legacy data
−
* '''History''':
+
* '''CardSafe''': human-readable (and searchable) summary of "safe" card data. Format subject to change.
−
** '''2009-07-09''' moved to separate wiki page; no design changes
+
* '''CardCVV''': [[wikipedia:Card security code|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, [http://php.net/manual/en/function.openssl-public-encrypt.php 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==
 
==SQL==
−
<section begin=sql /><mysql>CREATE TABLE `cust_cards` (
+
<mysql>CREATE TABLE `cust_cards` (
 
   `ID`        INT  NOT NULL AUTO_INCREMENT,
 
   `ID`        INT  NOT NULL AUTO_INCREMENT,
−
   `Name`      VARCHAR(31) COMMENT "human-assigned name for this card",
+
  `WhenEnt`    DATETIME        NOT NULL COMMENT "when record was first created",
−
   `ID_Cust`    INT COMMENT "core_custs.ID",
+
  `WhenUpd`    DATETIME    DEFAULT NULL COMMENT "when record was last updated",
−
   `ID_Addr`    INT COMMENT "cust_addrs.ID of card's billing address",
+
  `WhenEnc`    DATETIME    DEFAULT NULL COMMENT "when Encrypted field was updated",
−
   `CardNum`    VARCHAR(31) COMMENT "charge card number",
+
   `Name`      VARCHAR(31)               COMMENT "human-assigned nickname for this card",
−
   `CardExp`    DATE COMMENT "expiration date",
+
   `ID_Cust`    INT                       COMMENT "core_custs.ID",
−
   `CardCVV`    VARCHAR(7) DEFAULT NULL COMMENT "CVV(2) code",
+
  `ID_Name`    INT                      COMMENT "cust_names.ID",
−
   `OwnerName` VARCHAR(127) COMMENT "cardholder name, as it appears on card",
+
   `ID_Addr`    INT                       COMMENT "cust_addrs.ID of card's billing address",
−
   `Address`   VARCHAR(255) COMMENT "text of address, for redundancy",
+
   `CardNum`    VARCHAR(31)               COMMENT "charge card number",
−
   `CryptCard`  VARCHAR(127) COMMENT "encrypted card number & expiration",
+
   `CardExp`    DATE                     COMMENT "expiration date",
−
   `CryptAddr` VARCHAR(255) COMMENT "encrypted card billing address",
+
   `CardCVV`    VARCHAR(7)   DEFAULT NULL COMMENT "CVV(2) code",
−
   `isActive`  BOOL DEFAULT FALSE COMMENT "FALSE = don't use this number",
+
   `CardSafe`   VARCHAR(127)     NOT NULL COMMENT "unencrypted 'safe' card info in human-readable format",
−
   `WhenInvalid` DATETIME DEFAULT NULL COMMENT "deactivation timestamp",
+
   `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",
 
   `Notes`      VARCHAR(255) DEFAULT NULL COMMENT "human-entered notes about this card",
 
   PRIMARY KEY(`ID`)
 
   PRIMARY KEY(`ID`)
 
)
 
)
 
ENGINE = MYISAM;</mysql>
 
ENGINE = MYISAM;</mysql>
−
<section end=sql />
 

Latest revision as of 17:23, 22 February 2015

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>

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/tables/cust_cards&oldid=20681"