Saturday, August 06, 2011

SQL Injection (Primer 1) - PHP Escaping and Like Operators

This post talks about exploiting the SQL queries with LIKE operator in use. However, this situation and target can be specific in nature but one can use the concept that is discussed below to go after exploiting the SQL injection. In order to discuss this part, let's take an example as presented below

if (isset($_POST['submit_search'])){
$search_name = htmlentities(mysql_real_escape_string($_POST['search_user']));
$age = stripslashes($_POST['age']);

$query1 = mysql_query("SELECT * FROM user_table WHERE username LIKE '$search_user' AND age=('$age')") or die("SQL Error Mate");

In this example, "search_user" is the parameter that is provided as an initial input point to the application user. As one can see, this parameter is escaped using mysql_real_escape_string(escapes special characters in a string for use in an SQL statement) and then with htmlentities (convert all applicable characters to HTML entities). There is another parameter as "age" which is set with stripslashes(returns a string with backslashes stripped off)

So in this case, where the SQL injection can be done. Following consideration leads to successful SQL injection

1. There is an age parameter which takes the value from the application user. This parameter is expected by the server in the POST request.

2. As the age parameter is using stripslashes function, it is good to inject legitimate value and then closing it appropriately.

3. In this case, one must not concentrate on exploiting the search_user parameter rather hit on the age parameter. As it is POST request, it is easy to play with proxy to set up the value for the age parameter.

4. In general, when anyone run the successful query with the legitimate username such as root, another information such as age will be thrown on the HTML page. This clearly indicates the fact that query is consuming some another parameter too.

5. So the payloads such as age=';-- produces an error as follows

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';--')' at line 1"

This means the application is vulnerable to POST based SQL injection and appropriate query is required to exploit it successfully.

6. One can try for fuzzing it with different requests such as Union SQL Poisoning tricks. For example: age=27') union select 1,2,3,4,5,6,7,password from user_table -- -

For any POST based SQL injection, always try to verify the fact which field is required to be attacked and whether a new parameter can be injected or not.

You may encounter this type of scenario in hacking challenges. :)

This solution is an outcome of collaborative work with Rohit (Rb1337). Hope to share some more thoughts on SQL injections.


jas- said...

What about the a combined use of stored procedures, the PDO abstraction layer, sprintf (for formatting dynamic sql statements) and data sanitation.

Using a stored procedure for authentication of user.

CREATE DEFINER='username'@'localhost' PROCEDURE Auth_CheckUser(IN `email` VARCHAR(128), IN `pword` LONGTEXT, IN `challenge` LONGTEXT)
COMMENT 'Performs authentication check'
SELECT COUNT(*) AS x FROM `authentication` WHERE AES_DECRYPT(BINARY(UNHEX(email)), SHA1(challenge))=email AND AES_DECRYPT(BINARY(UNHEX(password)), SHA1(challenge))=pword;

Utilizing a PDO connection & queries (using PHP).

try {
$dbconn = new PDO(mysql:host='.$hostname.';dbname='.$database, $username, $password);
} catch(PDOException $e) {
$dbconn = false;
$query = $dbconn->prepare($query);
try {
$results = $query->fetchAll(PDO::FETCH_ASSOC);
} catch(PDOException $e){
$results = $e->getMessage();

The always necessary data sanitation (using the newer PHP filter_var() family of functions)

function _filter($data)

I prefer sprintf to generate any dynamic sql statements, and the use of stored procedures allow strict data type requirements.

$sql = sprintf('CALL Auth_CheckUser("%s", "%s", "%s")', _filter($email), _filter($password), _filter(_hash($dbKey, _salt($dbKey, 2048))));
$r = $db->query($sql);

I have tried to inject various sql statements in this manner with no on data leaks or additional compromises, perhaps you can see a way to do it?

Aditya K Sood said...

@jas : Stored procedures and prepared statements are always the way to go. A string sanitization routine in PHP such as "mysql_real_escape_string" and "htmlentities" if use in collaboration with prepared statements, then one can achieve more secure platform.

jas- said...

Thanks, however...

The mysql_real_escape_string() function has been depreciated in favor of the newer PDO abstraction layer.

The htmlentities() functionality has also been depreciated (AFIK) in favor of the newer filter_var() functions coupled with the optional FILTER_FLAG_* family of options.

Aditya K Sood said...

@jas that's right. However, in real time plethora of websites are still using it. So its more of a generic scenario. Development is always on the way but it takes time to incorporate it in already developed base such as PHP applications.