In part one of this post we installed and configured MySQL and installed the required package for our web-server (http). In this post I intend on explaining how to 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 login form and we’ll also code a simple PHP login 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!.
Installing a basic web-server with PHP support
In step one we installed the core web-sever packages: httpd, php and php-mysql. By default the web-server (httpd) is configured to not start at boot e.g.
chkconfig --list httpd httpd 0:off 1:off 2:off 3:off 4:off 5:off 6:off
We’d like our web-site to be available after a reboot,so let’s modify this – again the chkconfig utility can be used:
chkconfig httpd on
In addition to the web-server starting automatically we also want the public to be able to access our web-site. By default the firewall on Linux (iptables) will not permit external access to the httpd daemon. Thus, we need to open up port 80. This is achieved by modifying the file /etc/sysconfig/iptables as follows:
# Firewall configuration written by system-config-firewall # Manual customization of this file is not recommended. *filter :INPUT ACCEPT [0:0] :FORWARD ACCEPT [0:0] :OUTPUT ACCEPT [0:0] -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT -A INPUT -p icmp -j ACCEPT -A INPUT -i lo -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT -A INPUT -j REJECT --reject-with icmp-host-prohibited -A FORWARD -j REJECT --reject-with icmp-host-prohibited COMMIT
In the example above I simply added the highlighted line which instructs the firewall to accept traffic on port 80. To activate this restart iptables e.g.
service iptables restart iptables: Flushing firewall rules: [ OK ] iptables: Setting chains to policy ACCEPT: filter [ OK ] iptables: Unloading modules: [ OK ] iptables: Applying firewall rules: [ OK ]
We are now ready to start the web-server for this first time:
service httpd start Starting httpd: [ OK ]
Once started it is wise to ensure the default web-site is available. This can be achieve by browsing to your IP address. You should see the following:
Our web-server is now configured and running.
Creating a simple log-in page
The web-server package (httpd) serves the content of the local directory /var/www/html/ which is created during the installation. This is the location that we are going to create our login page. Therefore, using your favourite editor, create the file /var/www/html/index.html and paste in the following:
<!doctype html> <html lang="en"> <head> <meta charset="utf-8" /> <title>Login - SQL Injection Welcome</title> </head> <body> <header> <h1>Simple Login Form</h1> </header> <article> <p>Please provide the authentication credentials to continue:</p> <section> <form method="POST" action="login.php" enctype="application/x-www-form-urlencoded"> <p>Username: <input type="text" name="username" id="username" size="25" required/></p> <p>Password: <input type="password" name="passwd" id="passwd" size="25" required/></p> <p> <input type="submit" name="submit" id="submit" value="Submit"/> <input type="reset" name="reset" id="reset" value="Reset"/> </p> </form> </section> </article> <footer> Copyright Super secure company! </footer> </body> </html>
Save the file (once the above has been inserted) and then open the page in a browser. You should see the following:
The page includes a form that prompts the user for their username and password. When input, the user can submit their credentials. The act of submitting the page will post the username and password to the server. The target page to post the details to is specified in the form’s action attribute: in this example the form details will be posted to login.php. Thus, we’ll need to create this page. Using your favourite editor, create the file /var/www/html/login.php and paste in the following:
<?php mysql_connect("localhost", "loginaccount", "LetMeIn") or die('Could not connect'); $username=$_POST['username']; $pwd=$_POST['passwd']; $sql="SELECT * FROM `inject`.users WHERE name='$username' and password='$pwd'"; $result=mysql_query($sql); $count=mysql_num_rows($result); $sSuccessMsg = ($count>0?"Login details verified":"Wrong Username or Password"); ?> <!doctype html> <html lang="en"> <head> <meta charset="utf-8" /> <title>Log on attempt result</title> </head> <body> <header> <h1>Login Result</h1> </header> <article> <p><?php echo $sSuccessMsg;?></p> </article> <footer> Copyright Super secure company! </footer> </body> </html>
The code above can be explained as follows:
|mysql_connect(“localhost”, “loginaccount”, “LetMeIn”) or die(‘Could not connect’);||This line attempts to open a connection to the MySQL database.|
|$username=$_POST[‘username’];||The posted username value is stored in a local PHP variable.|
|$pwd=$_POST[‘passwd’];||The posted password value is stored in a local PHP variable.|
|$sql=”SELECT * FROM `inject`.users WHERE name=’$username’ and password=’$pwd'”;||A SQL select statement is created using the posted data and stored in a local PHP variable.|
|$result=mysql_query($sql);||The SQL statement is execute against the database.|
|$count=mysql_num_rows($result);||The count of rows returned by the statement is ascertained.|
|$sSuccessMsg = ($count>0?”Login details verified”:”Wrong Username or Password”);||Finally the message to display to the user is ascertained. If the credentials match a record on the database the message will read “Login details verified”. An incorrect login will be presented with the message “Wrong Username or Password”.|
That concludes the configuration and we can now test the login form!
Testing the login form
Before we test the login form let’s remind ourselves what user accounts we created in step one. In part one we created the database table called ‘users’. We created two records within the user table. These records contain the users and passwords that should be permitted access to our logon system. The username and passwords we created are:
A successful logon with a valid user name and password
Using the username “paul” and password “Secret123” we are granted access!
A failed logon with invalid user name and password
Using the username “paul” and password “12345” we are denied access!
A successful logon with an invalid user name and password
Using the username “paul” and password “‘ OR 1=1 AND ”=’” we are granted access! In order to understand how this happened let’s modify the PHP code in login.php to print our the SQL statement e.g.
<?php $sql="SELECT * FROM `inject`.users WHERE name='$username' and password='$pwd'"; echo $sql;
I’ve simply added the echo statement, so resubmit the credentials and then we can see the SQL statement that will be executed e.g.
This instantly gives us the answer! The cleverly crafted password has modified the logic of our SQL statement so that it always finds a matching record. And thus, always logs the user onto our system! Scarily simple.
Preventing SQL injection
Wikipedia explains that the most common was of fixing this is straightforward, though an error-prone. The Wikipedia page is referring to the ‘escape’ method. This escapes characters that have a special meaning in SQL. The manual for an SQL DBMS explains which characters have a special meaning, which allows creating a comprehensive blacklistof characters that need translation. For instance, every occurrence of a single quote (
') in a parameter must be replaced by two single quotes (
'') to form a valid SQL string literal. For example, in PHP it is usual to escape parameters using the function
mysql_real_escape_string(); before sending the SQL query:
<?php $username=mysql_real_escape_string($_POST['username']); $pwd=mysql_real_escape_string($_POST['passwd']);
Once this small change has been made the login form now correctly rejects the password e.g.
Although this method prevents the injection attack, it may be error-prone because the developer can easily forget use the escape call; thus, leaving random pages susceptible. I personally recommend using bound variables. The reason for this is:
- Bound variables are immune from SQL injection
- SQL statements using bound variable have a greater chance of being re-used from the cache – basically it’s more efficient.
PHP supports MySQL prepared statements using the Mysqli (MySQL Improved) extension in PHP 5 via the MySQLi_STMT class. They are easy to use once you get used to the differences from writing raw SQL statements. Here’s the script above updated to support bound variables:
<?php $db = mysqli_connect("localhost", "loginaccount", "password") or die('Could not connect'); $username=mysql_real_escape_string($_POST['username']); $pwd=mysql_real_escape_string($_POST['passwd']); // Create statement object $stmt = $db->stmt_init(); $stmt->prepare("SELECT COUNT(*) CNT FROM `inject`.users WHERE name=? and password=?"); $stmt->bind_param($username,$pwd); $stmt->execute(); $stmt->bind_result($count); $sSuccessMsg = ($count>0?"Login details verified":"Wrong Username or Password"); ?> <!doctype html> <html lang="en"> <head> <meta charset="utf-8" /> <title>Log on attempt result</title> </head> <body> <header> <h1>Login Result</h1> </header> <article> <p><?php echo $sSuccessMsg;?></p> </article> <footer> Copyright Super secure company! </footer> </body> </html>
The login page is now immune from SQL injection. And, that concludes this rather longer than anticipated post 🙂