One of the most common form of attacks against web applications is SQL Injection. In the most part, the language that the web application is written in is irrelevant, be that PHP, ASP, Python, Perl, C, etc. As long as the back end database uses something SQL based, be that MySQL, MSSQL, etc, again, we’re in business. This probably covers over 99% of web applications out there. Both the security consultant and the php developer or web application developer in general has to be aware of the implications of SQL Injection. Here’s how it works:
Take the following PHP code:
$link = mysql_connect(“localhost”, “dbuser”, “dbpass”);
$sql = “SELECT email FROM users WHERE user_id=” . $_POST[‘user_id’] . “;”;
$result = mysql_query($sql);
When I call the code above from my browser, using file.php?user_id=10 the query SELECT email FROM users WHERE user_id=10; will get executed.
Should I enter say file.php?user_id=10;INSERT INTO… then I should be able to execute this at the end of the query: SELECT email FROM users WHERE user_id=10;INSERT INTO…
Now this is an obvious hole for a penetration tester to exploit, more often than not, single quotes are used within the code, for example:
$sql = “SELECT email FROM users WHERE user_id='” . $_POST[‘user_id’] . “‘;”;
Trying to run the same exploit will result in: SELECT email FROM users WHERE user_id=’10;INSERT INTO…’; which is invalid. Instead, I could execute something like this: test.php?user_id=10′;INSERT INTO…; SELECT 1 WHERE ‘1’=’1
This results in: SELECT email FROM users WHERE user_id=’10’;INSERT INTO…; SELECT 1 WHERE ‘1’=’1′;
‘1’=’1′ will of course always evaluate to true, and solves the pen tester’s extra single quote problem.
Now lets say that we want to get data from the database that we’re not supposed to get, this would be with a SELECT query. Assume that we know another field is ‘username’ and we want to get the email address for ‘steve’. Calling: test.php?user_id=10′ OR username=’steve will result in a query of: SELECT email FROM users WHERE user_id=’10’ OR username=’steve’; If our page only returns one record and not the full recordset which is most likely the case, we might execute test.php?user_id=-1′ OR username=’steve and assuming that user_id -1 does not exist, we will only receive the one record where the username is ‘steve’.
The Final Quote Problem can also be solved by the security consultant by appending ‘–‘ to the end of the query. — denotes a mysql comment and therefore anything afterwards is ignored.
In a lot of cases, the page does not just output the record set as we would have liked it to, it simply performs some database operation in the background. In this case, on error, we might receive a missing image, some broken HTML code, or an SQL error. On success, the page would render correctly. We can not get the data that we want, however we can tell the difference between success and error, and in this case, we can only operate ‘blindly’ with Blind SQL Injection.
To insure that the pen tester are able to perform blind SQL, in the first instance, append ‘ AND 1=1 to the end of the query which should return true as before. Then append ‘ AND 1=0 to the end of the query which will return false regardless. As long as the page outputs we see are different, the site is vulnerable to Blind SQL Injection. Assuming we want to execute the query SELECT password FROM users WHERE user_id=’1′; The essence is to guess each character in the string and note whether our guess is true or false depending on the page output.
To the end of the query, append AND ASCII(SUBSTRING((SELECT CONCAT(username,0x3a,password) FROM users LIMIT 0,1),1,1))>50 If this returns true, then we know that the first character of the password has an ascii table value greater than 50. Next we can try 51, or 60, etc. Until we eventually narrow down the character that we want. There are other methods although this isn’t intended to be a SQL Injection HOWTO 🙂
I’m not going to go into anymore detailed examples of blind SQL, however there are automated tools such as sqlmap that a penetration tester can use to operate this guessing process for you. Bear in mind though that it will generate a lot of web traffic and a large number of SQL queries.
What can a PHP developer do to address the issue? SQL Injection is easy enough to prevent against – quite simply, the developer just needs to ‘escape’ all input. All quotes and other special characters automatically have a back slash ” inserted before them before database execution. In our example:
$sql = “SELECT email FROM users WHERE user_id='” . mysql_real_escape_string($_POST[‘user_id’]) . “‘;”;
Should the security consultant now try and enter test.php?user_id=10′ OR username=’steve the query will be convered into: SELECT email FROM users WHERE user_id=’10’ OR username=’steve’;
The above is of course invalid SQL and will not execute.