Installing Encryption For
MySQL With PHPMYADMIN

The following tutorial explains how you can upgrade phpmyadmin with the Web Encryption Extension. I'm using the latest version 4.0.5 for this explanation. You will be able to encrypt selected fields of any database that you can access after logging into phpmyadmin with your own set of public keys. Information that is stored encrypted in the database can be decrypted and read in clear text only if you are able to use the corresponding private key. In order to decrypt database entries you need to enter the secret information that protects the private key on the server.

Download

To make things as easy as possible for you I have bundled a tar archive that contains everything you'll need in one single download. After downloading the archive extract it in a directory and upload all files into the top level directory where phpmyadmin is installed on your server. Make sure that all new files are owned by the user that runs the webserver process and that restrictive file permissions (700) are preserved.

Decisions And Setup

Before you decide about how to use the Web Encryption Extension inside phpmyadmin, you'll need to provide a directory outside the webserver tree where WEE can store public keys for users. This directory must be writeable by the webserver process and access permissions need to be set to 700 for this directory. Make sure that this directory (i.e. /home/gpg) is set in the main configuration file "gpgconfig.php" in the following way

$GPGDIR = "/home/gpg";

It's essential that you check carefully that your place for keys is secured. Phpmyadmin performs user logins with the database server and returns an unique user identification string, the db user name. This identification string can be used to create a separate directory for every user to store his or her keys separately.

If you encounter difficulties trying to use this directory outside the web server's root directory, chances are that SELinux is responsible for this misbehaviour and you may have to teach SELinux to play nicely with the Web Encryption Extension.

You may consider that the key directory becomes part of a regular backup, but be careful not to expose the content of this directory to any backup unprotected.

Inside the archive, you'll find a file ready to be used as the main configuration file. This configuration is very simple as you're using the new feature of flexible input names introduced in version 1.1 of the code. With $FLEXIBLE = "yes"; turned on, all scripts read their input from a textarea whose name is determined by an inputselector element in the html code. With this flexible method to provide the names of usable data fields through a menu the user can select the field on which encryption, decryption, signature or verification will be performed subsequently. But the selection menu has to be adapted to your requirements. See line 247 of the file "tbl_change.php.new" for details.

Encrypting database fields will always replace the clear text input when you chose to use the encryption result. But for decryption you can select whether you will allow to replace the encrypted database field with the clear text after decryption or not. Setting $FORYOUREYESONLY = "yes"; in the configuration file will make sure that a user who is able to decrypt a database field can only see the result of his or her decryption but cannot replace the encrypted field in the database using this software. This option reminds the database user that decryption is used to access the stored information only, not to change it.

Placing Buttons in the Code

As a last step you need to place buttons in the code that activate the five WEE scripts. Fortunately changes to the code will only apply to a single file "tbl_change.php" which is used when you click to edit database entries. You may simply use the appropriate file in the archive "tbl_change.php.new" to replace the code, if you are using version 4.0.5, or you can inspect the file "tbl_change.php" to find the code for the inputselector menu and the buttons to update any other version you may use. I have placed all buttons in this single file to provide the full functionality in one place.

User Authentication and Key Management

It's absolutely essential that only users who have successfully logged into the mysql database server are able to use the additional encryption scripts and that every user has access to his own key files only. Nobody should be able to call the new scripts directly without authentication. All scripts make use of the file "wee-auth.php", which controls access to the scripts. Proper user authentication is performed here, based on calls to phpmyadmin code, that makes sure that permission to use the scripts is granted only to users with a successful login. This file also assigns the right key directory by overwriting the variable $GPGDIR with the proper path to a user's key directory based on the user name used to log into the database.

Changing the entire look and feel of the script's popup windows is also possible by adapting the stylesheet file "gpgstyle.css".