VbzCart/tables/cust cards - Revision history

Woozle: /* Future */ NickName

2015-02-22T17:23:41Z

Future: NickName

← Older revision		Revision as of 17:23, 22 February 2015
Line 23:		Line 23:
	* Records with '''OwnerName''' need to have '''ID_Name''' set, and then the OwnerName field can be removed		* 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.		* '''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==
	<mysql>CREATE TABLE `cust_cards` (		<mysql>CREATE TABLE `cust_cards` (

Woozle: storing salt makes no sense; storing encryption timestamp instead

2015-01-10T23:42:42Z

storing salt makes no sense; storing encryption timestamp instead

← Older revision		Revision as of 23:42, 10 January 2015
Line 14:		Line 14:
	* '''2013-09-01''' added '''CardSafe'''		* '''2013-09-01''' added '''CardSafe'''
	* '''2013-09-25''' added '''CardSalt'''; reordered some fields		* '''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==		==Future==
	* '''Address''' should go away, probably without any need to verify that ID_Addr agrees with it		* '''Address''' should go away, probably without any need to verify that ID_Addr agrees with it
Line 21:		Line 26:
	<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",
			`WhenEnc` DATETIME DEFAULT NULL COMMENT "when Encrypted field was updated",
	`Name` VARCHAR(31) COMMENT "human-assigned nickname for this card",		`Name` VARCHAR(31) COMMENT "human-assigned nickname for this card",
	`ID_Cust` INT COMMENT "core_custs.ID",		`ID_Cust` INT COMMENT "core_custs.ID",
Line 31:		Line 37:
	`CardCVV` VARCHAR(7) DEFAULT NULL COMMENT "CVV(2) code",		`CardCVV` VARCHAR(7) DEFAULT NULL COMMENT "CVV(2) code",
	`CardSafe` VARCHAR(127) NOT NULL COMMENT "unencrypted 'safe' card info in human-readable format",		`CardSafe` VARCHAR(127) NOT NULL COMMENT "unencrypted 'safe' card info in human-readable format",
	~~`CardSalt` VARCHAR(128) COMMENT "to prevent precalculated lookups",~~
	`Encrypted` VARCHAR(256) COMMENT "encrypted card number, CVV, expiration, salt",		`Encrypted` VARCHAR(256) COMMENT "encrypted card number, CVV, expiration, salt",
	`OwnerName` VARCHAR(127) COMMENT "cardholder name, as it appears on card",		`OwnerName` VARCHAR(127) COMMENT "cardholder name, as it appears on card",

Woozle: CardCVV: link to Wikipedia article

2014-07-26T19:01:58Z

CardCVV: link to Wikipedia article

← Older revision		Revision as of 19:01, 26 July 2014
Line 5:		Line 5:
	* '''Address''' may be no longer necessary, except for handling legacy data		* '''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.		* '''CardSafe''': human-readable (and searchable) summary of "safe" card data. Format subject to change.
			* '''CardCVV''': [[wikipedia:Card security code\|Card security code]]
	==History==		==History==
	* '''2009-07-09''' moved to separate wiki page; no design changes		* '''2009-07-09''' moved to separate wiki page; no design changes

Woozle: CardSalt

2013-09-25T16:59:16Z

CardSalt

← Older revision		Revision as of 16:59, 25 September 2013
Line 13:		Line 13:
	* '''2011-11-22''' added WhenEnt and WhenUpd fields		* '''2011-11-22''' added WhenEnt and WhenUpd fields
	* '''2013-09-01''' added '''CardSafe'''		* '''2013-09-01''' added '''CardSafe'''
			* '''2013-09-25''' added '''CardSalt'''; reordered some fields
	==Future==		==Future==
	* '''Address''' should go away, probably without any need to verify that ID_Addr agrees with it		* '''Address''' should go away, probably without any need to verify that ID_Addr agrees with it
Line 22:		Line 23:
	`WhenEnt` DATETIME NOT NULL COMMENT "date when record was first created",		`WhenEnt` DATETIME NOT NULL COMMENT "date when record was first created",
	`WhenUpd` DATETIME DEFAULT NULL COMMENT "date when record was last updated",		`WhenUpd` DATETIME DEFAULT NULL COMMENT "date when record was last updated",
	`Name` VARCHAR(31) COMMENT "human-assigned ~~name~~ for this card",		`Name` VARCHAR(31) COMMENT "human-assigned nickname for this card",
	`ID_Cust` INT COMMENT "core_custs.ID",		`ID_Cust` INT COMMENT "core_custs.ID",
	`ID_Name` INT COMMENT "cust_names.ID",		`ID_Name` INT COMMENT "cust_names.ID",
Line 30:		Line 31:
	`CardCVV` VARCHAR(7) DEFAULT NULL COMMENT "CVV(2) code",		`CardCVV` VARCHAR(7) DEFAULT NULL COMMENT "CVV(2) code",
	`CardSafe` VARCHAR(127) NOT NULL COMMENT "unencrypted 'safe' card info in human-readable format",		`CardSafe` VARCHAR(127) NOT NULL COMMENT "unencrypted 'safe' card info in human-readable format",
			`CardSalt` VARCHAR(128) COMMENT "to prevent precalculated lookups",
			`Encrypted` VARCHAR(256) COMMENT "encrypted card number, CVV, expiration, salt",
	`OwnerName` VARCHAR(127) COMMENT "cardholder name, as it appears on card",		`OwnerName` VARCHAR(127) COMMENT "cardholder name, as it appears on card",
	`Address` VARCHAR(255) COMMENT "text of address, for redundancy",		`Address` VARCHAR(255) COMMENT "text of address, for redundancy",
	~~`Encrypted` VARCHAR(127) COMMENT "encrypted card number, CVV, expiration",~~		`isActive` BOOL DEFAULT FALSE COMMENT "FALSE = don't use this card",
	`isActive` BOOL DEFAULT FALSE COMMENT "FALSE = don't use this ~~number~~",
	`WhenInvalid` DATETIME DEFAULT NULL COMMENT "deactivation timestamp",		`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",

Woozle: more tidying

2013-09-01T22:13:17Z

more tidying

← Older revision		Revision as of 22:13, 1 September 2013
Line 9:		Line 9:
	* '''2009-07-09''' moved to separate wiki page; no design changes		* '''2009-07-09''' moved to separate wiki page; no design changes
	* '''2009-10-07'''		* '''2009-10-07'''
	* '''2011-11-22''' added WhenEnt and WhenUpd fields
	** removed CryptAddr, renamed CryptCard as Encrypted		** removed CryptAddr, renamed CryptCard as Encrypted
	** added ID_Name		** added ID_Name
			* '''2011-11-22''' added WhenEnt and WhenUpd fields
	* '''2013-09-01''' added '''CardSafe'''		* '''2013-09-01''' added '''CardSafe'''
	==Future==		==Future==
Line 18:		Line 18:
	* '''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.		* '''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.
	==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 "date when record was first created",
Line 39:		Line 39:
	)		)
	ENGINE = MYISAM;</mysql>		ENGINE = MYISAM;</mysql>
	~~<section end=sql />~~

Woozle: /* Fields */ not a rigorous format

2013-09-01T22:00:13Z

Fields: not a rigorous format

← Older revision		Revision as of 22:00, 1 September 2013
Line 4:		Line 4:
	* '''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.		* '''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
	* '''CardSafe''': human-readable (and searchable) summary of "safe" card data~~: T-''nnnn'' x''nnnn'' p''postal_code''~~		* '''CardSafe''': human-readable (and searchable) summary of "safe" card data. Format subject to change.

	==History==		==History==
	* '''2009-07-09''' moved to separate wiki page; no design changes		* '''2009-07-09''' moved to separate wiki page; no design changes

Woozle: CardSafe; tidying

2013-09-01T21:49:37Z

CardSafe; tidying

← Older revision		Revision as of 21:49, 1 September 2013
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: T-''nnnn'' x''nnnn'' p''postal_code''
	** '''2009-07-09''' moved to separate wiki page; no design changes		==History==
	** '''2009-10-07'''		* '''2009-07-09''' moved to separate wiki page; no design changes
	** '''2011-11-22''' added WhenEnt and WhenUpd fields		* '''2009-10-07'''
	*** removed CryptAddr, renamed CryptCard as Encrypted		* '''2011-11-22''' added WhenEnt and WhenUpd fields
	*** 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		* '''2013-09-01''' added '''CardSafe'''
	** Records with '''OwnerName''' need to have '''ID_Name''' set, and then the OwnerName field can be removed		==Future==
	** '''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.		* '''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.
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE TABLE `cust_cards` (		<section begin=sql /><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 "date when record was first created",
	`WhenUpd` DATETIME DEFAULT NULL COMMENT "date when record was last updated",		`WhenUpd` DATETIME DEFAULT NULL COMMENT "date when record was last updated",
	`Name` VARCHAR(31) COMMENT "human-assigned name for this card",		`Name` VARCHAR(31) COMMENT "human-assigned name for this card",
	`ID_Cust` INT COMMENT "core_custs.ID",		`ID_Cust` INT COMMENT "core_custs.ID",
	`ID_Name` INT COMMENT "cust_names.ID",		`ID_Name` INT COMMENT "cust_names.ID",
	`ID_Addr` INT COMMENT "cust_addrs.ID of card's billing address",		`ID_Addr` INT COMMENT "cust_addrs.ID of card's billing address",
	`CardNum` VARCHAR(31) COMMENT "charge card number",		`CardNum` VARCHAR(31) COMMENT "charge card number",
	`CardExp` DATE COMMENT "expiration date",		`CardExp` DATE COMMENT "expiration date",
	`CardCVV` VARCHAR(7) DEFAULT NULL COMMENT "CVV(2) code",		`CardCVV` VARCHAR(7) DEFAULT NULL COMMENT "CVV(2) code",
	`OwnerName` VARCHAR(127) COMMENT "cardholder name, as it appears on card",		`CardSafe` VARCHAR(127) NOT NULL COMMENT "unencrypted 'safe' card info in human-readable format",
	`Address` VARCHAR(255) COMMENT "text of address, for redundancy",		`OwnerName` VARCHAR(127) COMMENT "cardholder name, as it appears on card",
	`Encrypted` VARCHAR(127) COMMENT "encrypted card number, CVV, expiration",		`Address` VARCHAR(255) COMMENT "text of address, for redundancy",
	`isActive` BOOL DEFAULT FALSE COMMENT "FALSE = don't use this number",		`Encrypted` VARCHAR(127) COMMENT "encrypted card number, CVV, expiration",
	`WhenInvalid` DATETIME DEFAULT NULL COMMENT "deactivation timestamp",		`isActive` BOOL DEFAULT FALSE COMMENT "FALSE = don't use this number",
			`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`)

Woozle: added WhenEnt and WhenUpd

2011-11-22T19:45:01Z

added WhenEnt and WhenUpd

← Older revision		Revision as of 19:45, 22 November 2011
Line 7:		Line 7:
	** '''2009-07-09''' moved to separate wiki page; no design changes		** '''2009-07-09''' moved to separate wiki page; no design changes
	** '''2009-10-07'''		** '''2009-10-07'''
			** '''2011-11-22''' added WhenEnt and WhenUpd fields
	*** removed CryptAddr, renamed CryptCard as Encrypted		*** removed CryptAddr, renamed CryptCard as Encrypted
	*** added ID_Name		*** added ID_Name
Line 16:		Line 17:
	<section begin=sql /><mysql>CREATE TABLE `cust_cards` (		<section begin=sql /><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",
			`WhenUpd` DATETIME DEFAULT NULL COMMENT "date when record was last updated",
	`Name` VARCHAR(31) COMMENT "human-assigned name for this card",		`Name` VARCHAR(31) COMMENT "human-assigned name for this card",
	`ID_Cust` INT COMMENT "core_custs.ID",		`ID_Cust` INT COMMENT "core_custs.ID",

Woozle: 10/7 changes

2009-10-08T14:27:36Z

10/7 changes

← Older revision		Revision as of 14:27, 8 October 2009
Line 6:		Line 6:
	* '''History''':		* '''History''':
	** '''2009-07-09''' moved to separate wiki page; no design changes		** '''2009-07-09''' moved to separate wiki page; no design changes
			** '''2009-10-07'''
			*** removed CryptAddr, renamed CryptCard as Encrypted
			*** added ID_Name
			* '''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.
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE TABLE `cust_cards` (		<section begin=sql /><mysql>CREATE TABLE `cust_cards` (
Line 11:		Line 18:
	`Name` VARCHAR(31) COMMENT "human-assigned name for this card",		`Name` VARCHAR(31) COMMENT "human-assigned name for this card",
	`ID_Cust` INT COMMENT "core_custs.ID",		`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",		`ID_Addr` INT COMMENT "cust_addrs.ID of card's billing address",
	`CardNum` VARCHAR(31) COMMENT "charge card number",		`CardNum` VARCHAR(31) COMMENT "charge card number",
Line 17:		Line 25:
	`OwnerName` VARCHAR(127) COMMENT "cardholder name, as it appears on card",		`OwnerName` VARCHAR(127) COMMENT "cardholder name, as it appears on card",
	`Address` VARCHAR(255) COMMENT "text of address, for redundancy",		`Address` VARCHAR(255) COMMENT "text of address, for redundancy",
	`~~CryptCard~~` VARCHAR(127) COMMENT "encrypted card number & expiration",		`Encrypted` VARCHAR(127) COMMENT "encrypted card number, CVV, expiration",
	~~`CryptAddr` VARCHAR(255) COMMENT "encrypted card billing address~~",
	`isActive` BOOL DEFAULT FALSE COMMENT "FALSE = don't use this number",		`isActive` BOOL DEFAULT FALSE COMMENT "FALSE = don't use this number",
	`WhenInvalid` DATETIME DEFAULT NULL COMMENT "deactivation timestamp",		`WhenInvalid` DATETIME DEFAULT NULL COMMENT "deactivation timestamp",

Woozle: moved from "customer tables" page

2009-07-09T19:35:58Z

moved from "customer tables" page

New page

==About==
* '''Purpose''': for recording the information necessary to get paid by credit/debit cards
* '''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.
** '''Address''' may be no longer necessary, except for handling legacy data
* '''History''':
** '''2009-07-09''' moved to separate wiki page; no design changes
==SQL==
<section begin=sql /><mysql>CREATE TABLE `cust_cards` (
`ID` INT NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(31) COMMENT "human-assigned name for this card",
`ID_Cust` INT COMMENT "core_custs.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",
`OwnerName` VARCHAR(127) COMMENT "cardholder name, as it appears on card",
`Address` VARCHAR(255) COMMENT "text of address, for redundancy",
`CryptCard` VARCHAR(127) COMMENT "encrypted card number & expiration",
`CryptAddr` VARCHAR(255) COMMENT "encrypted card billing address",
`isActive` BOOL DEFAULT FALSE COMMENT "FALSE = don't use this number",
`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>
<section end=sql />