Prion Interactive

This post is about securely storing data in a MySQL table. And by secure I mean storing encrypted data. To keep it simple I’m going to use symmetric encryption (i.e. secret key encryption) rather than asymmetric encryption (i.e. public key encryption). The post is a bit of a red herring because it starts off examining MySQL’s AES_ENCRYPT/AES_DECRYPT functionality but then reveals why you might not want to use it. I then offer a PHP alternative.

To fuel this experiment I am going to demonstrate how to store credit card numbers in an encrypted manner. Normally, you don’t want to store credit card details because who needs the hassle? But sometimes business rules dictate that you need to keep card details around for a while (for example, maybe you don’t bill the card until the purchased item is shipped). At any rate, there are legitimate reasons for storing credit card numbers in a database and the PCI-DSS, the data security standard for looking after credit card details, has a lot to say about what you may and may not do. I’m not saying this solution is compliant with PCI-DSS, since compliance is more of a policy matter than a technical one, but you sure as heck don’t want to be storing credit card numbers in plain text in your database, that’s suicide.

The first thing to do is create a table in your database to hold the encrypted data. I’ll keep it super simple. Run this code through your MySQL server or through phpMyAdmin to create the table. Note the use of the varbinary data type. I’ve found that 32 is a reasonable size for storing credit card info. If you need to store something else, maybe you’ll need to make it bigger.

CREATE TABLE `creditcards` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `number` varbinary(32) NOT NULL,
  `name` varbinary(32) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Now that we have a table, the next thing we’re going to need is a secret key. I typically use the GRC Ultra High Security Password Generator to create a 63 character string of gibberish. Visit the page and copy the ’63 random printable ASCII characters’ string into a text file. I name this file .secret and I store it in a folder above the webroot. If you haven’t done that before than read this post about storing files above the webroot on media temple. The important thing is to put the file somewhere where it can’t be addressed by a URL on the server. You see, it’s a secret.

Now, somewhere that is web addressable, create a new PHP file named, I dunno, ‘secure.php’ and add the following code at the top.

if (!($fd = fopen({/full/path/to/.secret}, "r"))) {
  die("Can't fine file");
}
fscanf($fd, "%s", $secret);
fclose($fd);

You’ll need to fill in the full path to the .secret file for the open statement. What this snippet does is read the file from its location above the webroot and place the contents of the file into the variable $secret. This is how our secret key gets loaded into PHP.

Lots of people ask around the Internet “how do I keep my secret key safe” and I think this approach, at least on a single server in a hosting environment that is not physically under your control, is pretty good. For a Media Temple server, you’ve got the physical security down pat, if you’ve got solid ssh and ftp passwords you’ve got your network security locked down, and as long as you secure your PHP code so that people can’t upload scripts to be executed on the server, you’re doing alright. My impression is that a lot of people want to know how to protect the integrity of an encrypted database when someone has complete access to the server. This is like asking how to stop the coup after the king is dead and the usurper is on the throne; it’s far too late at that point. These tactics work when someone does not have complete access to your server, if they do, then it’s game over man, game over.

So, first attempt to store encrypted data in MySQL will use the built in AES_ENCRYPT function. Code the following INSERT statement.

$insert_sql = "insert into creditcards (";
$insert_sql.= "number,";
$insert_sql.= "name";
$insert_sql.= ") values (";
$insert_sql.= "AES_ENCRYPT('4111111111111111', ?),"; // the ? are bound parameters in PDO
$insert_sql.= "AES_ENCRYPT('Prion Interactive', ?)";
$insert_sql.= ");";
$parameters = array($secret, $secret); // I know, there is a more elegant way to do it

I typically use PDO for all my database needs so the following code will open a PDO connection for MySQL. If you’re using mysql then replace the bound parameters ? with the $secret variable escaped with mysql_real_escape_string and surrounded in single quotes.

First, set up the server.

define("HOST", "{host}");
define("DB_NAME", "{database_name}");
define("DB_USER", "{db_username}");
define("DB_PASS", "{db_password}");
$db = new PDO("mysql:host=" . HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASS, array(PDO::ATTR_PERSISTENT, true));
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Naturally, you need to fill in your own database info for the define statements.

After initializing PDO user the following command to run the $insert_sql query.

$st = $db->prepare($insert_sql); // has bound variables
$st->execute($parameters); // parameters contains the values to bind

Save all the above to the PHP file and run it. Then go have a gander in phpMyAdmin and browse the creditcards table, you’ll see the encrypted info in there.

If you want to decrypt the info you go the opposite way with a SELECT statement.

$select_sql = "select AES_DECRYPT(number, ?) as number from creditcards order by id desc limit 1;";
$parameters = array($secret);
$st = $db->prepare($select_sql); // has bound variables
$st->execute($parameters); // parameters contains the values to bind
$result = $st->fetchAll(PDO::FETCH_ASSOC);
print $result[0]["number"]; // should print 4111111111111111

So, wonderful, it worked right? Encrypted data in the database, secret key stored securely below the web root, perfect.

Except for one thing. Do you replicate your MySQL database? For back-up or master-slave or anything like that? Because if you do then you know that that functionality is achieved using the bin logs. That’s a problem.

Media Temple does not turn bin logging on by default. This is another situation where you need to edit the /etc/my.cnf file to enable some server functionality. SSH on to the server and open the file in vi.

vi /etc/my.cnf

Move down to some empty lines and add the following three lines.

log_bin=mysql-bin
expire_logs_days = 5
max_binlog_size = 100M

Quickly, this tells MySQL to name the bin log file mysql-bin, that these files (of which there can be many) should only stick around for 5 days and that the max size should never exceed 100 megs.

So what is bin logging? Every command that is issued to the server that modifies data, that is, every insert, update, delete, what have you, everything but select, is logged to the bin log. The back up servers, or the MySQL slaves, when configured properly, look for those bin logs and run their contents to make a clone of the master database. It’s a pretty good way to do backups. Except in this case.

Restart the MySQL server.

/etc/init.d/mysqld restart

Now go and reload the PHP page so that it inserts the encrypted data again.

Back in SSH, cd to /var/lib/mysql and look for a file named mysql-bin.000001. The first of many bin log files. Issue the following command to see its contents.

mysqlbinlog mysql-bin.000001

Do you see what’s in there? Yeah, the insert statement WITH the plain text values to be encrypted and WITH the secret key for the whole world to see. Bin log files are, as you might expect, bin files, but mysqlbinlog prints them out as plain text. Nevertheless, all the work we went to encrypt that data is undone by the presence of this bin file. Not only that, but bin files get spread around to different servers, so they need to travel. It’s a big problem. But it also makes sense since of course the back up server or the slave servers need the full insert statement to make an accurate duplicate, how else could they function? What it means for us is that the AES_ENCRYPT/AES_DECRYPT method of MySQL, while cool, is not suitable if bin logging is needed for any reason.

Be sure to go back into my.cnf and remove those lines about bin logging if you don’t want to keep them. MySQL bin logs can eat your hard drive if you’re not careful. Don’t forget to reboot the server if you change my.cnf.

So, what to do? What must be done is that the data needs to be encrypted by PHP and inserted into the database in encrypted form. This will preserve the security of the data in the tables and allow the use of bin logging since the statements being logged will only contain the encrypted data, not the plain text and not the secret key.

How to do this? We’re going to use PHP’s mcrypt library with the RIJNDAEL 256 cipher algorithm. There is a variety of ciphers to choose from but this one will serve for the demonstration.

Modify your PHP page to include the following code (keep the code that loads the .secret key from above the web root and that initializes the PDO database connection).

$secret = mhash(MHASH_MD5, $secret);
$cipher_alg = MCRYPT_RIJNDAEL_256;
$iv = mcrypt_create_iv(mcrypt_get_iv_size($cipher_alg, MCRYPT_MODE_ECB), MCRYPT_RAND);

First, I mhash the $secret variable so that it’s an acceptable size for the MCRYPT_RIJNDAEL_256 algorithm. Next I define the cipher and then I create the ‘initialization vector’ $iv.

The following code uses the cipher to encrypt the credit card number and name.

$number = mcrypt_encrypt($cipher_alg, $secret, "4111111111111111", MCRYPT_MODE_CBC, $iv);
$name = mcrypt_encrypt($cipher_alg, $secret, "Prion Interactive", MCRYPT_MODE_CBC, $iv);

And the following code is the modified $insert_sql statement.

$insert_sql = "insert into creditcards (";
$insert_sql.= "number,";
$insert_sql.= "name";
$insert_sql.= ") values (";
$insert_sql.= "?,?";
$insert_sql.= ");";
$parameters = array($number,$name);
$st = $db->prepare($insert_sql); // has bound variables
$st->execute($parameters); // parameters contains the values to bind

Where once again I am using PDO to handle the insert.

Have a look inside the creditcards table using phpMyAdmin and you’ll see the encrypted text.

To decrypt the contents, use the following code.

$select_sql = "select number from creditcards order by id desc limit 1;";
$result = Database::getDatabase()->query($select_sql);
$number_encrypted = $result[0]["number"];
$number_decrypted = mcrypt_decrypt($cipher_alg, $secret, $number_encrypted, MCRYPT_MODE_CBC, $iv);
$number_decrypted = rtrim($decrypted_str, "\0\4");
print $number_decrypted;

If you want to satisfy yourself that bin logging is not disclosing your secrets you can turn it back on, reboot the server, rerun the PHP file and examine the logs. Or you can trust me when I tell you that only the encrypted values are in the bin logs now.

Ta-da.

I’m no pro at securing MySQL databases but I know that you shouldn’t use the full access user from within PHP. The following is a description of how I create a second, limited MySQL user for PHP applications hosted on a Media Temple DV server.

The default database user is created through Plesk in the traditional way. Sign into Plesk, select the “Domain” menu option, click the domain name, click the “Databases” icon, and click the “Add New Database” icon. Fill in the database name and click save.

Now click the “Add New Database User” icon to create the first user for the database. The form that appears asks for a user name and a password. For the username I follow the form “db_{project_name}_full” where {project_name} is a short word that represents the project. The key element is the use of “_full” in the name to act as an identifier that reminds me “this user has full privileges”.

Also crucial, at this point, and always, is the creation of a strong password. I like to use the Strong Password Generator to generate a nice 20-character string of nonsense to use as a password. Of course I can’t remember it so I save it in a password manager on my desktop. The chances of someone breaking into my computer and stealing the password is far less than someone guessing a shorter, more memorable password from the comfort of their computer room. Create a good one and use it.

It’s important to have a user with full permissions because such a user is needed to access phpMyAdmin and create and manage the database’s tables. Within the context of Plesk this is perfectly acceptable but within the context of PHP scripts full access can be dangerous. This is because, despite our best efforts, we may accidentally write some code that permits an SQL injection. By creating a user with limited MySQL access, no “DROP” permissions for example, we can partially mitigate the danger of an SQL injection.

To create the limited user you’ll need command line access. Media Temple has a wiki for that.

Sign on to the server and start MySQL.

mysql -u admin -p

Media Temple has set the default MySQL user to be “admin” instead of “root” and the password for “admin” is the same password used to sign into Plesk.

Once in to MySQL (you’ll see the mysql> command prompt) issue the following command.

GRANT SELECT ON {database_name}.* TO '{database_username}' IDENTIFIED BY '{username_password}';

Let’s break this down. {database_name} is the name of the database that was created in Plesk. {database_username} is the name of the new user being created (the act of granting permissions creates the user if they don’t already exist). Following the naming pattern above I use the form “db_{project_name}_limited” where {project_name} is the same short word and the presence of “_limited” tells me that this is the user with limited access. Create another strong password and drop it into {username_password}.

Clearly this command only allows the new user to SELECT. Perhaps that’s all you need. If so, you’ve created the most secure database user ever since they can only read your database. However, you probably need a bit more functionality. Issue the following two commands to add INSERT and UPDATE permissions.

GRANT INSERT ON {database_name}.* TO '{database_username}' IDENTIFIED BY '{username_password}';
GRANT UPDATE ON {database_name}.* TO '{database_username}' IDENTIFIED BY '{username_password}';

Now the limited user has SELECT, INSERT, and UPDATE. That’s all I ever give my limited user. That’s right, no DELETE. When I delete something in the CMS I set a “deleted” column to the current unix epoch time stamp and manage my SELECTs around on that column. This means that no one can inject a “DELETE from important_table” command and wipe out my important table. Of course, an UPDATE can be nearly as destructive but UPDATEs are necessary. Security is a balance after all.

If you want DELETE, issue the following command.

GRANT DELETE ON {database_name}.* TO '{database_username]' IDENTIFIED BY '{username_password}';

Finally, no matter what, issue this command to make sure all the privileges stick.

FLUSH PRIVILEGES;

You can also do a SELECT to see your new user and his permissions.

SELECT * FROM mysql.user;

There are a lot of Ys and Ns in that output but you can sort through it.

One note, it is possible to combine all the GRANT commands into one like this:

GRANT SELECT, INSERT, UPDATE ON {database_name}.* TO '{database_username}' IDENTIFIED BY '{username_password}';

But I usually do them one at a time just to be fastidious.

Finally, the obvious bit, use the new limited user and password as the user that accesses the MySQL database in PHP. Using these credentials will ensure that your PHP can’t do anything to the database that you haven’t explicitly given it permission to do.