SQL injection and how to protect your website

Experienced developers are expensive. In a world where cutting cost seems like the best option, companies try to maximize their profit by spending less and less on good talent. It is much cheaper to hire someone who just learned php a few weeks ago then a seasoned developer. But it becomes a very bad investment when the newbie introduces insecure code. The problem is, a lot of things learned from those LAMP CRUD application tutorial do not focus much on security. When this code is introduced to a commercial application, the damage can be very expensive. I like how stackoverflow users are fighting very hard to eradicate SQL injection, I am doing my part too but it seems like it is much easier to find insecure code on-line. That said, I will attempt to scare you off your feet so you know better what is the cost of SQL injection.

Disclaimer: This information is meant for educational purposes only. You and only you will be held responsible for any way you use it.

The injection

A SQL injection, also known as a Bobby Table, is not the only one but it is the most popular vulnerability found on websites. It is usually found in this form:

$email = $_POST['email'];
$password = $_POST['pass'];
$check_user = "SELECT * FROM users WHERE password='$password' AND email='$email'";

Actual example from stackoverflow: source

The problem is this code looks perfectly fine, at least to the untrained eye. It is valid in its form and syntax. However, a clever user can easily manipulate the parameters to log in without having to the proper credentials. For example the user can pass a command in the pass variable to modify the WHERE clause:

$password = $_POST['pass']; // where pass is: ' OR 1=1 --
$check_user = "SELECT * FROM users WHERE password='$password' AND email='$email'";

This will be directly injected in the query and will result in this:

SELECT * FROM users WHERE password='' OR 1=1 -- ' AND email='email@email.com'

This is just a simple example. SQL injection is not an exact science. Every query is different and may require a different strategy to exploit. I read an interesting conversation yesterday on a forum where someone was challenging the legitimacy of the threat of SQL injection. Sure, most of the stuff you add to the parameter will simply break the query and cause a SQL error on the page. This is not a problem, the problem is when an experience user use it to reverse engineer the structure of your database and steal all your information, including passwords.

Scenario

So you have a photo sharing website that has lots of users. You allow them to log in and upload or share their photos. You have an image gallery and the URL look like this: http://www.example.org/gallery.php?ImageID=22 . This URL gets the record identified by 22 from the gallery table and returns the image title, the image path, the description and the date added. Unfortunately you don't sanitize the parameter and inject it directly in your query.

$imageID = $_GET["ImageID"];
$query = "SELECT title,imagepath,description,dateadded FROM imageGallery WHERE id= $imageID LIMIT 1";

Using the previous hack will not have any benefits here because we are just selecting an image. Since your site has users however, there is a great chance that you have a table full of user names, emails, and passwords. Will this SQL injection vulnerability allow a malicious user to get these credentials? You bet!

Like I said earlier, SQL injection is not an exact science. First of all the attacker only assumes the format of your query. From the URL all that can be deduced is that the query contains something = 22. Adding some random characters after 22 may cause the page to fail which is a good indicator that the input is not sanitized properly. The title of the image is displayed on the page, so if we can replace the title field we can work our way to slowly reconstruct the structure of the database.

The UNION ALL clause can be used to add more rows to the resulting query. However we need to know the number of columns for it to work. Here is what can be done, we can try a number of parameters. Whichever doesn't return an error will be our correct one:

1. http://www.example.org/gallery.php?ImageID=22 UNION ALL SELECT 1 LIMIT 1 -- 
2. http://www.example.org/gallery.php?ImageID=22 UNION ALL SELECT 1,1 LIMIT 1 -- 
3. http://www.example.org/gallery.php?ImageID=22 UNION ALL SELECT 1,1,1 LIMIT 1 -- 
4. http://www.example.org/gallery.php?ImageID=22 UNION ALL SELECT 1,1,1,1 LIMIT 1 --

In our case number 4 will be the successful one since the query contains 4 fields.

Note: I found that most of the sites vulnerable of SQL injection also use very poor error handling. Something like mysql_query($sql) or die("Error: ".mysql_error()) is very common, which helps the attacker debug his query.

Hijacking the fields can now begin. Offsetting the results by one and modifying the ones should allow us to see the data directly on the page:

http://www.example.org/gallery.php?ImageID=22 UNION ALL SELECT 123456,1,1,1 LIMIT 1,1 --

Running this should change the title of the image to 123456.

That's it! Now we can run any query we want on the database. How? you may ask. Well, we have the first field in the UNION directly returning data from the database. So if we modify the field to be a sub query, we can get tables information from the information_schema database. I will only write the sub query part for readability, so replace the 123456 from the previous query with this sub query.

// the sub-query
(SELECT table_name FROM information_schema.tables LIMIT 0,1)
                                                        ^---> the offset

By incrementing the offset one number at a time you can get the list of all the tables in the database printed as the image title. This is the perfect place for an automated script because the number of tables can be very large. The same can be done on the COLUMNS table to retrieve the list of columns. After that there is no limit to what data you can retrieve from the database.

Protecting your query.

_real_escape_string()

The first line of defense is escaping the user inputs. Let's see how it works with our first query:

$email = mysqli_real_escape_string($link,$_POST['email']);
$password = mysqli_real_escape_string($link,$_POST['pass']);
$check_user = "SELECT * FROM users WHERE password='$password' AND email='$email'";

So here is what happens if I try the first hack:

// the resulting query
SELECT * FROM users WHERE password='\' OR 1=1 -- ' AND email='email@email.com'
>                                  ^-------------^

The password value is surrounded by single quotes and any quotes in between will be escaped properly. That's nice it worked. So how about the second query:

$imageID = mysqli_real_escape_string($link,$_GET["ImageID"]);
$query = "SELECT title,imagepath,description,dateadded FROM imageGallery WHERE id= $imageID LIMIT 1";

Lets say I access this URL http://www.example.org/gallery.php?ImageID=22 UNION ALL SELECT 123456,1,1,1 LIMIT 1,1 -- the result will be:

SELECT title,imagepath,description,dateadded FROM imageGallery WHERE id= 22 UNION ALL SELECT 123456,1,1,1 LIMIT 1,1 -- LIMIT 1

Well that didn't work. The attacker can still query stuff from our database. The reason is *_real_escape_string does not add surrounding quotes to your input, it just escapes it. The proper way to escape an Integer value is to use the intval() function or cast the value to int.

// either of the two can be used
$imageID = intval($_GET["ImageID"]); 
$imageID = (int) $_GET["ImageID"];
$query = "SELECT title,imagepath,description,dateadded FROM imageGallery WHERE id= $imageID LIMIT 1";

// and the same URL input will result in :  
SELECT title,imagepath,description,dateadded FROM imageGallery WHERE id= 0 LIMIT 1

If the attacker adds any character that other than an integer to the parameter, the value will be converted to zero by default. This way the worst thing that can happen is the image not being found.

Parameterized queries with PDO

Using Parameterized queries should make it even simpler. PDO will handle it all for you. Let's convert our query to it:

$db = new PDO('...', '...username...', '...pass...');
$imageID = $_GET["ImageID"];
$query = "SELECT title,imagepath,description,dateadded FROM imageGallery WHERE id= :imageid LIMIT 1";
$stmt = $db->prepare($query);
$stmt->bindParam(':imageid',$imageID, PDO::PARAM_INT);
$stmt->execute();

The same method can be used for our first query.

$db = new PDO('...', '...username...', '...pass...');
$email = $_POST['email'];
$password = $_POST['pass'];
$query = "SELECT * FROM users WHERE password=:password AND email=:email";
$stmt = $db->prepare($query);
$stmt->bindParam(':password',$password, PDO::PARAM_STR);
$stmt->bindParam(':email',$email, PDO::PARAM_STR);
$stmt->execute();

PDO will handle adding quotes to the parameter if necessary.

Conclusion

Now you have an idea how not sanitizing your inputs can lead to serious problems. The steps you take to protect yourself are not hard at all. The problem is people tend to think their website is not important so it doesn't matter. What they don't know is SQL injection is only one of the problems caused by improper handling of user input. In some circumstance it can allow the attacker to execute server side code on your server and do a lot more damage. Your server can be used as a zombie to attack other servers on demand or be part of a DDOS cluster. To summarize this post: Don't trust any input coming from the user.

Useful resources:

• intval()
• integer type casting
• mysqli_*
• mysqli_real_escape_string()
• PDO
• prepared statements
• Deprecating mysql_* functions

---