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
(added WhenEnt and WhenUpd)
(→‎Future: NickName)
 
(6 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]]
−
** '''2009-10-07'''
+
==History==
−
** '''2011-11-22''' added WhenEnt and WhenUpd fields
+
* '''2009-07-09''' moved to separate wiki page; no design changes
−
*** removed CryptAddr, renamed CryptCard as Encrypted
+
* '''2009-10-07'''
−
*** added ID_Name
+
** removed CryptAddr, renamed CryptCard as Encrypted
−
* '''Future''':
+
** added ID_Name
−
** '''Address''' should go away, probably without any need to verify that ID_Addr agrees with it
+
* '''2011-11-22''' added WhenEnt and WhenUpd fields
−
** Records with '''OwnerName''' need to have '''ID_Name''' set, and then the OwnerName field can be removed
+
* '''2013-09-01''' added '''CardSafe'''
−
** '''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.
+
* '''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,
−
   `WhenEnt`    DATETIME     NOT NULL COMMENT "date when record was first created",
+
   `WhenEnt`    DATETIME         NOT NULL COMMENT "when record was first created",
−
   `WhenUpd`    DATETIME DEFAULT NULL COMMENT "date when record was last updated",
+
   `WhenUpd`    DATETIME     DEFAULT NULL COMMENT "when record was last updated",
−
   `Name`      VARCHAR(31) COMMENT "human-assigned name for this card",
+
  `WhenEnc`    DATETIME    DEFAULT NULL COMMENT "when Encrypted field was updated",
−
   `ID_Cust`    INT COMMENT "core_custs.ID",
+
   `Name`      VARCHAR(31)               COMMENT "human-assigned nickname for this card",
−
   `ID_Name`    INT COMMENT "cust_names.ID",
+
   `ID_Cust`    INT                       COMMENT "core_custs.ID",
−
   `ID_Addr`    INT COMMENT "cust_addrs.ID of card's billing address",
+
   `ID_Name`    INT                       COMMENT "cust_names.ID",
−
   `CardNum`    VARCHAR(31) COMMENT "charge card number",
+
   `ID_Addr`    INT                       COMMENT "cust_addrs.ID of card's billing address",
−
   `CardExp`    DATE COMMENT "expiration date",
+
   `CardNum`    VARCHAR(31)               COMMENT "charge card number",
−
   `CardCVV`    VARCHAR(7) DEFAULT NULL COMMENT "CVV(2) code",
+
   `CardExp`    DATE                     COMMENT "expiration date",
−
   `OwnerName`  VARCHAR(127) COMMENT "cardholder name, as it appears on card",
+
   `CardCVV`    VARCHAR(7)   DEFAULT NULL COMMENT "CVV(2) code",
−
   `Address`    VARCHAR(255) COMMENT "text of address, for redundancy",
+
  `CardSafe`  VARCHAR(127)    NOT NULL COMMENT "unencrypted 'safe' card info in human-readable format",
−
  `Encrypted`  VARCHAR(127) COMMENT "encrypted card number, CVV, expiration",
+
  `Encrypted`  VARCHAR(256)              COMMENT "encrypted card number, CVV, expiration, salt",
−
   `isActive`  BOOL DEFAULT FALSE COMMENT "FALSE = don't use this number",
+
   `OwnerName`  VARCHAR(127)             COMMENT "cardholder name, as it appears on card",
−
   `WhenInvalid` DATETIME DEFAULT NULL COMMENT "deactivation timestamp",
+
   `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"