NOTICE: Whilst this document provides valid information, more secure mechanisms for accessing databases using PHP are now available. mysqli should be used instead of mysql_query.

Buy this as as a 155-page colour PDF for only $3

Chapter 7 - phpMyAdmin

phpMyAdmin provides a much nicer view of a database than the mysql command-line interface. It was bundled as part of Red Hat 5, but is not part of Red Hat 6, so we will have to go beyond what is in Red Hat 6. One popular option is the EPEL (Extra Packages for [Red Hat] Enterprise Linux) repository. As its name suggests, this provides various additional RPMs which are not included in the standard Red Hat Enterprise Linux release, so EPEL is the obvious choice.

Whilst EPEL gives you the benefit of a far wider range of software, if the reason you chose RHEL (or CentOS) was the fact that the core distribution is all tested in-house and supported by Red Hat, then obviously you are losing that advantage by straying beyond the fold of Red Hat. Enabling all of EPEL, with its many additional packages, could be seen as diluting the value of the Red Hat software. You can avoid the EPEL route, and all that it brings with it, by downloading phpMyAdmin from phpmyadmin.net and simply extracting it into /var/www/html (then rename phpmyadmin-<version> to phpmyadmin). The phpMyAdmin RPM does bring in a lot of extra packages with it, but they are not necessary for most normal uses of phpMyAdmin, and I have succesfully installed it this way on many systems, even more "esoteric" Unixes such as Solaris and AIX, with no problems.

Red Hat Only: The 'optional' repository is also required to use EPEL packages on Red Hat Enterprise Linux (not CentOS, OEL, Scientific Linux, etc) for dependencies: # subscription-manager repos --enable rhel-6-server-optional-rpms

Download the EPEL metapackage

Check the current release of EPEL 6 at http://download.fedoraproject.org/pub/epel/6/x86_64/repoview/epel-release.html.
Change the /x86_64/ in the URL to /i386/ for the 32-bit version.
Change the /epel/6/ in the URL to /epel/5/ for Red Hat 5, and so on.

[root@lamp ~]# rpm -Uvh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
Retrieving http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
warning: /var/tmp/rpm-tmp.bcslNo: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Preparing... ########################################### [100%]
1:epel-release ########################################### [100%]
[root@lamp ~]#

Install phpMyAdmin

You can now install phpMyAdmin directly via yum. The package and its dependencies will be found from the official repo if available, otherwise it will come from EPEL.

[root@lamp ~]# yum install phpmyadmin
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
epel/metalink | 18 kB 00:00
* base: mirror.ox.ac.uk
* epel: mirror.muntinternet.net
* extras: mirror.ox.ac.uk
* updates: mirror.simwood.com
epel | 4.2 kB 00:00
epel/primary_db | 5.0 MB 00:04
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package phpMyAdmin.noarch 0:3.5.7-1.el6 will be installed
--> Processing Dependency: php-mysql >= 5.2.0 for package: phpMyAdmin-3.5.7-1.el6.noarch
--> Processing Dependency: php-mcrypt >= 5.2.0 for package: phpMyAdmin-3.5.7-1.el6.noarch
--> Processing Dependency: php-mbstring >= 5.2.0 for package: phpMyAdmin-3.5.7-1.el6.noarch
--> Processing Dependency: php-gd >= 5.2.0 for package: phpMyAdmin-3.5.7-1.el6.noarch
--> Processing Dependency: php-php-gettext for package: phpMyAdmin-3.5.7-1.el6.noarch
--> Running transaction check
---> Package php-gd.x86_64 0:5.3.3-22.el6 will be installed
--> Processing Dependency: libpng12.so.0(PNG12_0)(64bit) for package: php-gd-5.3.3-22.el6.x86_64
--> Processing Dependency: libjpeg.so.62(LIBJPEG_6.2)(64bit) for package: php-gd-5.3.3-22.el6.x86_64
--> Processing Dependency: libpng12.so.0()(64bit) for package: php-gd-5.3.3-22.el6.x86_64
--> Processing Dependency: libjpeg.so.62()(64bit) for package: php-gd-5.3.3-22.el6.x86_64
--> Processing Dependency: libfreetype.so.6()(64bit) for package: php-gd-5.3.3-22.el6.x86_64
--> Processing Dependency: libXpm.so.4()(64bit) for package: php-gd-5.3.3-22.el6.x86_64
--> Processing Dependency: libX11.so.6()(64bit) for package: php-gd-5.3.3-22.el6.x86_64
---> Package php-mbstring.x86_64 0:5.3.3-22.el6 will be installed
---> Package php-mcrypt.x86_64 0:5.3.3-1.el6 will be installed
--> Processing Dependency: libmcrypt.so.4()(64bit) for package: php-mcrypt-5.3.3-1.el6.x86_64
---> Package php-mysql.x86_64 0:5.3.3-22.el6 will be installed
--> Processing Dependency: php-pdo(x86-64) for package: php-mysql-5.3.3-22.el6.x86_64
---> Package php-php-gettext.noarch 0:1.0.11-3.el6 will be installed
--> Running transaction check
---> Package freetype.x86_64 0:2.3.11-14.el6_3.1 will be installed
---> Package libX11.x86_64 0:1.5.0-4.el6 will be installed
--> Processing Dependency: libX11-common = 1.5.0-4.el6 for package: libX11-1.5.0-4.el6.x86_64
--> Processing Dependency: libxcb.so.1()(64bit) for package: libX11-1.5.0-4.el6.x86_64
---> Package libXpm.x86_64 0:3.5.10-2.el6 will be installed
---> Package libjpeg-turbo.x86_64 0:1.2.1-1.el6 will be installed
---> Package libmcrypt.x86_64 0:2.5.8-9.el6 will be installed
---> Package libpng.x86_64 2:1.2.49-1.el6_2 will be installed
---> Package php-pdo.x86_64 0:5.3.3-22.el6 will be installed
--> Running transaction check
---> Package libX11-common.noarch 0:1.5.0-4.el6 will be installed
---> Package libxcb.x86_64 0:1.8.1-1.el6 will be installed
--> Processing Dependency: libXau.so.6()(64bit) for package: libxcb-1.8.1-1.el6.x86_64
--> Running transaction check
---> Package libXau.x86_64 0:1.0.6-4.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
phpMyAdmin noarch 3.5.7-1.el6 epel 4.4 M
Installing for dependencies:
freetype x86_64 2.3.11-14.el6_3.1 updates 359 k
libX11 x86_64 1.5.0-4.el6 base 584 k
libX11-common noarch 1.5.0-4.el6 base 192 k
libXau x86_64 1.0.6-4.el6 base 24 k
libXpm x86_64 3.5.10-2.el6 base 51 k
libjpeg-turbo x86_64 1.2.1-1.el6 base 174 k
libmcrypt x86_64 2.5.8-9.el6 epel 96 k
libpng x86_64 2:1.2.49-1.el6_2 base 182 k
libxcb x86_64 1.8.1-1.el6 base 110 k
php-gd x86_64 5.3.3-22.el6 base 106 k
php-mbstring x86_64 5.3.3-22.el6 base 455 k
php-mcrypt x86_64 5.3.3-1.el6 epel 18 k
php-mysql x86_64 5.3.3-22.el6 base 81 k
php-pdo x86_64 5.3.3-22.el6 base 75 k
php-php-gettext noarch 1.0.11-3.el6 epel 21 k

Transaction Summary
================================================================================
Install 16 Package(s)

Total download size: 6.8 M
Installed size: 26 M
Is this ok [y/N]: y
Downloading Packages:
(1/16): freetype-2.3.11-14.el6_3.1.x86_64.rpm | 359 kB 00:00
(2/16): libX11-1.5.0-4.el6.x86_64.rpm | 584 kB 00:00
(3/16): libX11-common-1.5.0-4.el6.noarch.rpm | 192 kB 00:00
(4/16): libXau-1.0.6-4.el6.x86_64.rpm | 24 kB 00:00
(5/16): libXpm-3.5.10-2.el6.x86_64.rpm | 51 kB 00:00
(6/16): libjpeg-turbo-1.2.1-1.el6.x86_64.rpm | 174 kB 00:00
(7/16): libmcrypt-2.5.8-9.el6.x86_64.rpm | 96 kB 00:00
(8/16): libpng-1.2.49-1.el6_2.x86_64.rpm | 182 kB 00:00
(9/16): libxcb-1.8.1-1.el6.x86_64.rpm | 110 kB 00:00
(10/16): php-gd-5.3.3-22.el6.x86_64.rpm | 106 kB 00:00
(11/16): php-mbstring-5.3.3-22.el6.x86_64.rpm | 455 kB 00:00
(12/16): php-mcrypt-5.3.3-1.el6.x86_64.rpm | 18 kB 00:00
(13/16): php-mysql-5.3.3-22.el6.x86_64.rpm | 81 kB 00:00
(14/16): php-pdo-5.3.3-22.el6.x86_64.rpm | 75 kB 00:00
(15/16): php-php-gettext-1.0.11-3.el6.noarch.rpm | 21 kB 00:00
(16/16): phpMyAdmin-3.5.7-1.el6.noarch.rpm | 4.4 MB 00:04
--------------------------------------------------------------------------------
Total 716 kB/s | 6.8 MB 00:09

Here another new signing key needs to be imported, this time for the EPEL packages.

warning: rpmts_HdrFromFdno: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
Importing GPG key 0x0608B895:
Userid : EPEL (6) <epel@fedoraproject.org>
Package: epel-release-6-8.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
Is this ok [y/N]: y
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
Installing : php-mbstring-5.3.3-22.el6.x86_64 1/16
Installing : php-php-gettext-1.0.11-3.el6.noarch 2/16
Installing : libmcrypt-2.5.8-9.el6.x86_64 3/16
Installing : php-mcrypt-5.3.3-1.el6.x86_64 4/16
Installing : 2:libpng-1.2.49-1.el6_2.x86_64 5/16
Installing : libX11-common-1.5.0-4.el6.noarch 6/16
Installing : freetype-2.3.11-14.el6_3.1.x86_64 7/16
Installing : libjpeg-turbo-1.2.1-1.el6.x86_64 8/16
Installing : libXau-1.0.6-4.el6.x86_64 9/16
Installing : libxcb-1.8.1-1.el6.x86_64 10/16
Installing : libX11-1.5.0-4.el6.x86_64 11/16
Installing : libXpm-3.5.10-2.el6.x86_64 12/16
Installing : php-gd-5.3.3-22.el6.x86_64 13/16
Installing : php-pdo-5.3.3-22.el6.x86_64 14/16
Installing : php-mysql-5.3.3-22.el6.x86_64 15/16
Installing : phpMyAdmin-3.5.7-1.el6.noarch 16/16
Verifying : php-pdo-5.3.3-22.el6.x86_64 1/16
Verifying : libXau-1.0.6-4.el6.x86_64 2/16
Verifying : libjpeg-turbo-1.2.1-1.el6.x86_64 3/16
Verifying : php-php-gettext-1.0.11-3.el6.noarch 4/16
Verifying : libXpm-3.5.10-2.el6.x86_64 5/16
Verifying : freetype-2.3.11-14.el6_3.1.x86_64 6/16
Verifying : php-mysql-5.3.3-22.el6.x86_64 7/16
Verifying : php-mcrypt-5.3.3-1.el6.x86_64 8/16
Verifying : libX11-common-1.5.0-4.el6.noarch 9/16
Verifying : libX11-1.5.0-4.el6.x86_64 10/16
Verifying : php-mbstring-5.3.3-22.el6.x86_64 11/16
Verifying : libxcb-1.8.1-1.el6.x86_64 12/16
Verifying : 2:libpng-1.2.49-1.el6_2.x86_64 13/16
Verifying : php-gd-5.3.3-22.el6.x86_64 14/16
Verifying : libmcrypt-2.5.8-9.el6.x86_64 15/16
Verifying : phpMyAdmin-3.5.7-1.el6.noarch 16/16

Installed:
phpMyAdmin.noarch 0:3.5.7-1.el6

Dependency Installed:
freetype.x86_64 0:2.3.11-14.el6_3.1 libX11.x86_64 0:1.5.0-4.el6
libX11-common.noarch 0:1.5.0-4.el6 libXau.x86_64 0:1.0.6-4.el6
libXpm.x86_64 0:3.5.10-2.el6 libjpeg-turbo.x86_64 0:1.2.1-1.el6
libmcrypt.x86_64 0:2.5.8-9.el6 libpng.x86_64 2:1.2.49-1.el6_2
libxcb.x86_64 0:1.8.1-1.el6 php-gd.x86_64 0:5.3.3-22.el6
php-mbstring.x86_64 0:5.3.3-22.el6 php-mcrypt.x86_64 0:5.3.3-1.el6
php-mysql.x86_64 0:5.3.3-22.el6 php-pdo.x86_64 0:5.3.3-22.el6
php-php-gettext.noarch 0:1.0.11-3.el6

Complete!
[root@lamp ~]#

Configure phpMyAdmin

Not a lot of configuration is required; the default setting from the RPM is that phpMyAdmin is only available to localhost. To change that, edit /etc/httpd/conf.d/phpMyAdmin.conf, and remove or edit these sections. Notice that there are sections for different versions of Apache, which use either the "Require ip ..." or the "Allow from ..." syntax.

<Directory /usr/share/phpMyAdmin/>
<IfModule mod_authz_core.c>
# Apache 2.4
<RequireAny>
Require ip 127.0.0.1
Require ip 192.168.1.13
Require ip ::1
<RequireAny>
<IfModule>
<IfModule !mod_authz_core.c>
# Apache 2.2
Order Deny,Allow
Deny from All
Allow from 127.0.0.1
Allow from 192.168.1.13
Allow from ::1
<IfModule>
<Directory>

<Directory /usr/share/phpMyAdmin/setup/>
<IfModule mod_authz_core.c>
# Apache 2.4
<RequireAny>
Require ip 127.0.0.1
Require ip 192.168.1.13
Require ip ::1
<RequireAny>
<IfModule>
<IfModule !mod_authz_core.c>
# Apache 2.2
Order Deny,Allow
Deny from All
Allow from 127.0.0.1
Allow from 192.168.1.13
Allow from ::1
<IfModule>
<Directory>

After editing any of the /etc/httpd configuration files, you will need to restart httpd

[root@lamp ~]# service httpd restart
Stopping httpd: [ OK ]
Starting httpd: [ OK ]
[root@lamp ~]#

Log in to phpMyAdmin

To log in to phpMyAdmin, just browse to http://127.0.0.1/phpmyadmin, or http://<ip.of.ser.ver>/phpmyadmin

Log in using the MySQL root password. Have a click around the web interface, there is not a lot to see so far as we have not set up any databases, but you can start to get a feel for the interface.

Create an Application Database

Every application should have its own database, so we shall create a database for a simple "Wish List" application. It will store lists of things that the users would like for their birthday, wedding, or other special event, and their friends can tick them off when they have got them.

From the home page of phpMyAdmin, click on the "Databases" tab at the top. Enter "wishlist" as the database name, and click the "Create" button.

The new database is now shown on the left hand side.

Configure MySQL Users

The next thing to do is to set up a low-privilege user account, which can be used by our PHP application to access the database. Although we did some tweaking of MySQL user accounts earlier via the mysql command-line tool, we can now do it somewhat more intuitively via the phpMyAdmin interface.

This account will not be able to change the database structure, or do anything too drastic to the database. This is another example of Defense in Depth, and an example of the principle of least privilege. Think of even a large and complicated web site, such as amazon.com or facebook.com. No matter what happens, there is no conceivable situation in which it would be reasonable for somebody accessing the site over the internet to trigger a "DROP TABLES" or similar type of event. So rather than just trusting the code, the firewall, the sanity testing and all of our other manual tricks to work, we can also ensure that whatever happens, this user will not be permitted to make any silly changes anyway. Any actual admin work can be done using another account, such as root or even a dedicated admin account for this particular database. This doesn't allow us to relax other safety and security measures, but it just means that if there is a failing in one of them, we should still be safe.

In phpMyAdmin, click on the wishlist database on the left hand panel:

Then choose the "Privileges" tab at the top, and click "Add user".

In the first section, define the following fields:

User name: wishuser
Host: localhost
Password: wishpass
Re-type: wishpass

Then scroll down to the "Database for user" section.

Check that the "Grant all privileges on database "wishlist"" option is checked, and that nothing under "Global privileges" is checked. Then click the "Add user" button.

When you click "Add user", you get back to the Privileges page, with the SQL command tastefully highlighted in purple, black, green and pink at the top of the page. Lower down, you can see that wishuser is now listed in the table.

You can see at the end of the SQL, it says "GRANT ALL PRIVILEGES ON `wishlist`". Because this user does not need to do much more than simple SQL statements like "select description from items" or "delete from items where item_id='379'", we can limit its privileges quite dramatically compared to the default setting. Click on "Edit Privileges" next to the newly-created user account, and deselect all of the "Structure" and "Administration" privileges, and press the "Go" button.

The Privileges tab now shows the restricted privileges for the wishuser account. This isn't about restricting ourselves, or saying that the people who will be given the password to this account are not trusted, but rather a case of ensuring that if something were to go wrong, it should not be possible for this account to cause massive damage. Also, it means that if (for example) tables are lost, or new triggers are added, then anybody with only wishuser level of access is automatically in the clear.

Now that the stack is installed and has a basic configuration, you are ready to start creating your LAMP application. That is what is covered in the next book in this series. It will take you through designing and building a real-life application; what follows is a preview of the current draft of the followup book to whet your appetite.

How to Build a LAMP Server (Linux, Apache, MySQL, PHP)