Requirement 3 of the PCI DSS v1.2 is:
“Protect Stored Cardholder Data”
As vague as that sounds, the PCI DSS enumerates exactly what that covers:
|Data Element||Storage Permitted||Protection Required||PCI DSS Req 3.4|
|Primary Account Number||Yes||Yes||Yes|
|Full Magnetic Stripe Data||No||N/A||N/A|
* These data elements must be protected if stored in conjunction with the PAN. This protection should be per PCI DSS requirements for general protection of the cardholder data environment. Additionally, other legislation (for example, related to consumer personal data protection, privacy, identity theft, or data security) may require specific protection of this data, or proper disclosure of a company’s practices if consumer-related personal data is being collected during the course of business. PCI DSS, however, does not apply if PANs are not stored, processed, or transmitted.
For those data points that can be stored, MySQL offers a variety of encryption functions to secure the data. For the purposes of this example, we’ll use AES_ENCRYPT() and AES_DECRYPT(). Let’s begin by creating a table to house the data. The formula for determining the space requirements is 16*(CEILING(string_length/16)+1) so we’ll create the table as follows:
mysql> CREATE TABLE `cc_info` ( -> `id` int unsigned NOT NULL auto_increment, -> `cc_num` binary(32) NOT NULL, -- Sorry AMEX -> `service_code` varbinary(32) NOT NULL, -> `name_on_card` varbinary(48) NOT NULL, -- Assuming a 32-char max, -> PRIMARY KEY (`id`)) -> ENGINE=InnoDB; Query OK, 0 rows affected (2.79 sec)
Populating it with some data, where I’ve chosen ‘secret_key’ as the key_str used to encrypt:
mysql> INSERT INTO `cc_info` (`cc_num`, `service_code`, `name_on_card`) VALUES ( -> AES_ENCRYPT('1234123412341234', 'secret_key'), -> AES_ENCRYPT('1234', 'secret_key'), -> AES_ENCRYPT('John Doe', 'secret_key')); Query OK, 1 row affected (0.35 sec)
If we try and SELECT the data, we get
mysql> SELECT id, cc_num, service_code, name_on_card FROM cc_info\G *************************** 1. row *************************** id: 1 cc_num: ?? ? q$?!~c?3Pg?"xu&3?:?,am? service_code: y.??A?? ?? ?a?? name_on_card: ?93s?!? X?8?|nZ 1 row in set (0.00 sec)
We need to use AES_DECRYPT as follows:
mysql> SELECT id, -> AES_DECRYPT(`cc_num`, 'secret_key') AS `cc_num`, -> AES_DECRYPT(`service_code`, 'secret_key') AS `service_code`, -> AES_DECRYPT(`name_on_card`, 'secret_key') AS `name_on_card` -> FROM `cc_info`\G *************************** 1. row *************************** id: 1 cc_num: 1234123412341234 service_code: 1234 name_on_card: John Doe 1 row in set (0.00 sec)
The PCI DSS v1.2 standard says that we must use “strong cryptography” to protect the data, where they define “strong cryptography” as “Cryptography based on industry-tested and accepted algorithms, along with strong key lengths and proper key-management practices. Cryptography is a method to protect data and includes both encryption (which is reversible) and hashing (which is not reversible, or “one way”). SHA-1 is an example of an industry-tested and accepted hashing algorithm. Examples of industry-tested and accepted standards and algorithms for encryption include AES (128 bits and higher), TDES (minimum double-length keys), RSA (1024 bits and higher), ECC (160 bits and higher), and ElGamal (1024 bits and higher).”
MySQL uses 128-bit key length, satisfying the requirement. You can, however, take things a step further by editing the file include/my_aes.h and updating the following line:
#define AES_KEY_LENGTH 128 /* must be 128 192 or 256 */
and compiling from source. I DO NOT recommend taking this approach unless you have a very compelling reason, as it is easy to mess up the build process, more difficult to manage upgrades, and generally more error-prone than simply using standard package managers for your chosen distribution.
So we’re done, right? Nope:) We must secure the information not only in the database, but in logs, backups, and any other place it resides on disk. If we’re using MySQL replication, which is quite typical, we must also consider the binary logs. Looking at the transactions above, we see:
%> mysqlbinlog log-bin.000001 ... BEGIN /*!*/; # at 172 #100406 16:35:31 server id 1 end_log_pos 200 Intvar SET INSERT_ID=1/*!*/; # at 200 #100406 16:35:31 server id 1 end_log_pos 461 Query thread_id=1 exec_time=0 error_code=0 use cc/*!*/; SET TIMESTAMP=1270596931/*!*/; INSERT INTO `cc_info` (`cc_num`, `service_code`, `name_on_card`) VALUES ( AES_ENCRYPT('1234123412341234', 'secret_key'), AES_ENCRYPT('1234', 'secret_key'), AES_ENCRYPT('John Doe', 'secret_key')) /*!*/; # at 461 #100406 16:35:31 server id 1 end_log_pos 488 Xid = 6 COMMIT/*!*/; ...
Unfortunately we can see all of the information that we worked so hard to secure! As of MySQL 5.1, you can use row-based replication, which logs changes in individual table rows rather than simply replicating full SQL statements. The same statements as above, as executed when binlog_format=ROW, appear in the binary log as:
%> mysqlbinlog -v log-bin.000001 ... #100406 16:39:30 server id 1 end_log_pos 936 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1270597170/*!*/; BEGIN /*!*/; # at 936 # at 989 #100406 16:39:30 server id 1 end_log_pos 989 Table_map: `cc`.`cc_info` mapped to number 15 #100406 16:39:30 server id 1 end_log_pos 1090 Write_rows: table id 15 flags: STMT_END_F BINLOG ' Msa7SxMBAAAANQAAAN0DAAAAAA8AAAAAAAAAAmNjAAdjY19pbmZvAAQD/g8PBv4gIAAwAAA= Msa7SxcBAAAAZQAAAEIEAAAQAA8AAAAAAAEABP/wBAAAACCY3AusAHEkreUIIX5jyzNQZ90ieHUm M8Y6BgflLGFtjxB5Lo7nQeK6zQr+wQCVYabiELI5M3P+IYoAWOo4iHxuWhI= '/*!*/; ### INSERT INTO cc.cc_info ### SET ### @1=1 ### @2='??\x0b?\x00q$??\x08!~c?3Pg?"xu&3?:\x06\x07?,am?' ### @3='y.??A??\x0a??\x00?a??' ### @4='?93s?!?\x00X?8?|nZ\x12' # at 1090 #100406 16:39:30 server id 1 end_log_pos 1117 Xid = 23 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; ...
Here, we can only see the encrypted binary data; thus meeting PCI standards. MySQL offers a third binlog_format, and that is MIXED. This is essentially where non-deterministic statements are executed with row-based logging, while deterministic statements use statement-based logging (this is an oversimplification, full details on MIXED logging format can be found here). Since these encryption functions are deterministic, the full SQL statements will be shown in the log and won’t work in a PCI environment.
We can’t obfuscate MySQL’s other logs, though. It is generally considered best practice to ensure that the slow query log, error log, and any other log in which full SQL queries may appear to be written on an encrypted volume if you’re using the “pure MySQL” approach to encryption. If encryption is done at the application layer, the logs are obfuscated for you.
These methodologies described above represent but one of the “Pure MySQL” ways to meet requirement 3. The other most common approaches are to call encryption functions within the application or to use a full-disk encryption solution. Be very careful with this approach, though; in my experience with these, they are mostly expensive, buggy, a performance killer, or some combination thereof (YMMV)! If this approach is chosen, I encourage one of your tests to be to physically yank the power cord during a load test; see if you can access the data after.
Much of the body of requirement 3 is dedicated to key management, documentation, distribution, storage, and generation. Here we’ll go through the remaining requirements that are relevant to our MySQL-only focus.
3.1 – Keep cardholder data storage to a minimum. Develop a data retention and disposal policy. Limit storage amount and retention time to that which is required for business, legal, and/or regulatory purposes, as documented in the data retention policy.
This is just generally good advice, not storing what you won’t or can’t use. It pollutes the database, requires more on-disk (and potentially in-memory) space while providing no business value. The important thing here is to develop a policy. A quick Google search for PCI Policy Templates can show some samples that can provide a jumping-off point while developing policies for your own organization.
3.2 – Do not store sensitive authentication data after authorization (even if encrypted)
The subsections of 3.2 basically boil down to a “don’t list”: DO NOT
- store sensitive authentication data after authorization (even if encrypted) [3.2]
- store the full contents of any track from the magnetic stripe (located on the back of a card, contained in a chip, or elsewhere). This data is alternatively called full track, track, track 1, track 2, and magnetic-stripe data [3.2.1]
- store the card- verification code or value (three- digit or four-digit number printed on the front or back of a payment card) used to verify card-not- present transactions [3.2.2]
- store the personal identification number (PIN) or the encrypted PIN block [3.2.3]
3.3 – Mask PAN when displayed (the first six and last four digits are the maximum number of digits to be displayed).
As a user, I actually consider this maximum to be incredibly rude! Imagine if you logged in to Amazon and they displayed your “masked” card as: 123412******1234! The standard method of display is **** 1234 (where the number of stars are variable). Since we generally don’t want unencrypted data floating around the network, we can access the data as follows:
mysql> SELECT RIGHT(AES_DECRYPT(`cc_num`, 'secret_key'),4) AS `last_four` -> FROM `cc_info` -> WHERE `id`=1; +-----------+ | last_four | +-----------+ | 1234 | +-----------+ 1 row in set (0.00 sec)
And then format it with leading *’s on the front-end as required.
3.4 – Render PAN, at minimum, unreadable anywhere it is stored (including on portable digital media, backup media, in logs) by using any of the following approaches: One-way hashes based on strong cryptography, Truncation, Index tokens and pads (pads must be securely stored)
This cannot be done natively in MySQL when using replication without one of the approaches listed above or writing logs to a dedicated encrypted partition.
3.4.1 – If disk encryption is used (rather than file- or column-level database encryption), logical access must be managed independently of native operating system access control mechanisms (for example, by not using local user account databases). Decryption keys must not be tied to user accounts.
When you evaluate a full-disk encryption solution, you must evaluate it primarily against the metrics outlined in the PCI DSS.