Have you ever wondered what all the fuss about SQL injection is? Does it really affect you? Should I worry about it? How do you know your site is safe from the perils of SQL injection! Should I sleep at night, or, should I monitor my web-server logs for the nasty malicious hackers. In this post I’ll step you through setting up a simple website with a HTML log in page that submits a username and password to a PHP page that is susceptible to SQL injection.
Setting up the web-server
Before we start, let me state that all the steps shown in this post are tailored for the CentOS 6 operating system. The command to achieve the same actions on other Linux flavours may differ and you may have to find a suitable substitute.
Installing a basic web-server with PHP support
In order to set-up a basic web-server we first need to install the following packages:
These packages can be installed using the YUM update manager as follows:
yum install httpd php php-mysql mysql-server
These packages provide the target server with a web server (httpd) with support for the PHP server-side language (php). The package which provides support for MySQL within PHP is also added (php-mysql). And, finally a prerequisite for the aforementioned is added: the MySQL database itself (mysql-server). On executing the command above YUM will evaluate the package dependencies and present you with a list of what will be installed:
Dependencies Resolved ============================================================================================================== Package Arch Version Repository Size ============================================================================================================== Installing: mysql-server i686 5.1.61-1.el6_2.1 updates 8.3 M php i686 5.3.3-3.el6_2.6 updates 1.1 M php-mysql i686 5.3.3-3.el6_2.6 updates 76 k Updating: httpd i686 2.2.15-15.el6.centos.1 updates 819 k Installing for dependencies: mysql i686 5.1.61-1.el6_2.1 updates 891 k perl-DBD-MySQL i686 4.013-3.el6 base 134 k perl-DBI i686 1.609-4.el6 base 705 k php-cli i686 5.3.3-3.el6_2.6 updates 2.2 M php-common i686 5.3.3-3.el6_2.6 updates 523 k php-pdo i686 5.3.3-3.el6_2.6 updates 72 k Updating for dependencies: httpd-tools i686 2.2.15-15.el6.centos.1 updates 70 k mysql-libs i686 5.1.61-1.el6_2.1 updates 1.2 M Transaction Summary ============================================================================================================== Install 9 Package(s) Upgrade 3 Package(s) Total download size: 16 M Is this ok [y/N]:
Simply accept the propossed list to install the packages. YUM may prompt you to import the GPG key of the software repository if it is the first time YUM has accessed the repository. If so accept to complete the installation:
Is this ok [y/N]: y Downloading Packages: (1/12): httpd-2.2.15-15.el6.centos.1.i686.rpm | 819 kB 00:01 (2/12): httpd-tools-2.2.15-15.el6.centos.1.i686.rpm | 70 kB 00:00 (3/12): mysql-5.1.61-1.el6_2.1.i686.rpm | 891 kB 00:01 (4/12): mysql-libs-5.1.61-1.el6_2.1.i686.rpm | 1.2 MB 00:01 (5/12): mysql-server-5.1.61-1.el6_2.1.i686.rpm | 8.3 MB 00:07 (6/12): perl-DBD-MySQL-4.013-3.el6.i686.rpm | 134 kB 00:00 (7/12): perl-DBI-1.609-4.el6.i686.rpm | 705 kB 00:00 (8/12): php-5.3.3-3.el6_2.6.i686.rpm | 1.1 MB 00:01 (9/12): php-cli-5.3.3-3.el6_2.6.i686.rpm | 2.2 MB 00:02 (10/12): php-common-5.3.3-3.el6_2.6.i686.rpm | 523 kB 00:00 (11/12): php-mysql-5.3.3-3.el6_2.6.i686.rpm | 76 kB 00:00 (12/12): php-pdo-5.3.3-3.el6_2.6.i686.rpm | 72 kB 00:00 -------------------------------------------------------------------------------------------------------------- Total 503 kB/s | 16 MB 00:32 warning: rpmts_HdrFromFdno: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6 Importing GPG key 0xC105B9DE: Userid : CentOS-6 Key (CentOS 6 Official Signing Key) Package: centos-release-6-2.el6.centos.7.i686 (@base/$releasever) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6 Is this ok [y/N]:
Once complete YUM should confirm the packages were installed successfully:
Installed: mysql-server.i686 0:5.1.61-1.el6_2.1 php.i686 0:5.3.3-3.el6_2.6 php-mysql.i686 0:5.3.3-3.el6_2.6 Dependency Installed: mysql.i686 0:5.1.61-1.el6_2.1 perl-DBD-MySQL.i686 0:4.013-3.el6 perl-DBI.i686 0:1.609-4.el6 php-cli.i686 0:5.3.3-3.el6_2.6 php-common.i686 0:5.3.3-3.el6_2.6 php-pdo.i686 0:5.3.3-3.el6_2.6 Updated: httpd.i686 0:2.2.15-15.el6.centos.1 Dependency Updated: httpd-tools.i686 0:2.2.15-15.el6.centos.1 mysql-libs.i686 0:5.1.61-1.el6_2.1 Complete!
Before we start our newly installed web-server we first need to configure and start the MySQL service. Once started we’ll connect to the service and create a new database which will hold our table of users and passwords for the login page.
Starting and securing the database
Once installed, the default for the MySQL database daemon is to start a boot. To check this we can use the chkconfig utility:
chkconfig --list mysqld mysqld 0:off 1:off 2:off 3:off 4:off 5:off 6:off
It is pretty normal to configure a database to automatically start a boot time, so let’s correct this – again the chkconfig utility can be used:
chkconfig mysqld on
We can now start the MySQL database. This will initialise the database and create the system tables. To start the database we use the service command e.g.
service mysqld start Initializing MySQL database: Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password' Alternatively you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd /usr/mysql-test ; perl mysql-test-run.pl Please report any problems with the /usr/bin/mysqlbug script! Starting mysqld: [ OK ]
It’s important to take note of the advice given here. The processes of starting the database for the first time has triggered MySQL to advise us on certain security actions we should be take. As we are responsible and want to ensure our database is super secure, let’s do it. We’ll start be setting the password for the database root user:
/usr/bin/mysqladmin -u root password 'LetMeiIn!'
Next, to be super safe and secure we’ll run the script too:
/usr/bin/mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MySQL to secure it, we'll need the current password for the root user. If you've just installed MySQL, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MySQL root user without the proper authorisation. You already have a root password set, so you can safely answer 'n'. Change the root password? [Y/n] n ... skipping. By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] Y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] Y ... Success! By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] Y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] Y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MySQL installation should now be secure. Thanks for using MySQL!
As stated on the output above the script disallowed root access from anywhere but the localhost, it removed the test database that ships with MySQL and reloaded the privilege table. That leaves us with a secure instance of MySQL without any database. Thus the next logical step is to create the database that will hold our table of users. In order to do this we first must log on to the MySQL command interface as the root user:
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 5.1.61 Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Once logged on simply issue the following command to create a database:
mysql> create database inject; Query OK, 1 row affected (0.00 sec)
Assuming you received a positive response (as above), the next step is to create a user who can access the database. It is worth pointing out that you should never use the root user to access the database. This is because the root user has root privileges! And, if your website was to fall foul to SQL injection attack we’d all prefer the attacker to only see and subset of tables; rather than the entire database as root can see!
To create a user from the MySQL console run the following:
mysql> grant all on inject.* to 'loginaccount' identified by 'password'; Query OK, 0 rows affected (0.00 sec)
The command above creates the user and at the same time grants the user access to all objects on the inject database. Please DO NOT set the password of this user to ‘password’. Make it something far more secure to avoid nasty hackers from brute-forcing your database.
The final step to complete the configuration is to create the table of users. Again, using the MySQL command prompt enter the following:
mysql> CREATE TABLE `inject`.`users` ( `idusers` INT NOT NULL , `name` VARCHAR(45) NOT NULL , `email` VARCHAR(45) NOT NULL , `password` VARCHAR(45) NOT NULL , PRIMARY KEY (`idusers`) ); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO `inject`.`users` (`idusers`, `name`, `email`, `password`) VALUES (0, 'paul', 'firstname.lastname@example.org', 'Secret123'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO `inject`.`users` (`idusers`, `name`, `email`, `password`) VALUES (1, 'ian', 'email@example.com', '12345'); Query OK, 1 row affected (0.00 sec)
That completes the database configuration and part 1 of this post. To conclude we now have a database called ‘inject’ which contains a schema called ‘inject’, This schema holds a table of users called ‘users’. We can now progress and configure the web server (httpd), ensuring the default httpd website is accessible to the public. Once httpd is configured we’ll replace the default page with a simple HTML log in form and we’ll also code a simple PHP log in processor which will be vulnerable to SQL injection. I’ll then demonstrate how to carry out the SQL injection attack and finally discuss how to prevent it!