How can I perform an SQL injection on the following PHP mysql_* code?

Question

I have an existing application which used to use the deprecated mysql_* functions to perform database queries. I have since changed most database access (and all that has user input) to PDO, so I believe I am relatively safe from injection attacks. However, I was wondering how one would perform an injection attack on the previous code just so I can demonstrate how unsafe it is should the need arise.

I have a link in the format:

http://localhost/api/view.php?id=

Which is then passed, unsanitized, into the select function below:

$db->select('invitations','Replied, Response, Registered',null,"Id = '".$id."'");
$res = $db->getResult();

I then do a var_dump() to see the result.

I have tried something like:

• http://localhost/api/view.php?id=<id>' => array(1) { [0]=> string(185) "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 ''<id>''' at line 1" }

• http://localhost/api/view.php?id=<id>" or 1=1" => array(0) { }

But it doesn't seem like those types of queries would be successful. Ideally, I would want to be able to do something severe. I was hoping for something like '); DROP TABLE users;--, but I cannot seem to make anything of the sort occur.

Here is the full select function:

public function select($table, $rows = '*', $join = null, $where = null, $order = null, $limit = null){
 // Create query from the variables passed to the function
 $q = 'SELECT '.$rows.' FROM '.$table;
 if($join != null){
 $q .= ' JOIN '.$join;
 }
 if($where != null){
 if (is_array($where)) {
 $filter = $where;
 $where = " 0 = 0 ";
 $qs = "";
 for ($i=0;$i<count($filter);$i++){
 switch($filter[$i]['data']['type']){
 case 'string' : $qs .= " AND ".$filter[$i]['field']." LIKE '%".$filter[$i]['data']['value']."%'"; Break;
 case 'list' :
 if (strstr($filter[$i]['data']['value'],',')){
 $fi = explode(',',$filter[$i]['data']['value']);
 for ($q=0;$q<count($fi);$q++){
 $fi[$q] = "'".$fi[$q]."'";
 }
 $filter[$i]['data']['value'] = implode(',',$fi);
 $qs .= " AND ".$filter[$i]['field']." IN (".$filter[$i]['data']['value'].")";
 }else{
 $qs .= " AND ".$filter[$i]['field']." = '".$filter[$i]['data']['value']."'";
 }
 Break;
 case 'boolean' : $qs .= " AND ".$filter[$i]['field']." = ".($filter[$i]['data']['value']); Break;
 case 'numeric' :
 switch ($filter[$i]['data']['comparison']) {
 case 'eq' : $qs .= " AND ".$filter[$i]['field']." = ".$filter[$i]['data']['value']; Break;
 case 'lt' : $qs .= " AND ".$filter[$i]['field']." < ".$filter[$i]['data']['value']; Break;
 case 'gt' : $qs .= " AND ".$filter[$i]['field']." > ".$filter[$i]['data']['value']; Break;
 }
 Break;
 case 'date' :
 switch ($filter[$i]['data']['comparison']) {
 case 'eq' : $qs .= " AND ".$filter[$i]['field']." = '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break;
 case 'lt' : $qs .= " AND ".$filter[$i]['field']." < '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break;
 case 'gt' : $qs .= " AND ".$filter[$i]['field']." > '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break;
 }
 Break;
 }
 }
 $where .= $qs;
 }
 $q .= ' WHERE '.$where;
 }
 if($order != null){
 $q .= ' ORDER BY '.$order;
 }
 if($limit != null){
 $q .= ' LIMIT '.$limit;
 }
 // Check to see if the table exists
 if($this->tableExists($table)){
 // The table exists, run the query
 $query = @mysql_query($q);
 if($query){
 // If the query returns >= 1 assign the number of rows to numResults
 $this->numResults = mysql_num_rows($query);
 // Loop through the query results by the number of rows returned
 for($i = 0; $i < $this->numResults; $i++){
 $r = mysql_fetch_array($query);
 $key = array_keys($r);
 for($x = 0; $x < count($key); $x++){
 // Sanitizes keys so only alphavalues are allowed
 if(!is_int($key[$x])){
 if(mysql_num_rows($query) > 1){
 $this->result[$i][$key[$x]] = $r[$key[$x]];
 }else if(mysql_num_rows($query) < 1){
 $this->result = null;
 }else{
 $this->result[$key[$x]] = $r[$key[$x]];
 }
 }
 }
 }
 return true; // Query was successful
 }else{
 array_push($this->result,mysql_error());
 return false; // No rows where returned
 }
 }else{
 return false; // Table does not exist
 }
}

How would I be able to perform an SQL injection?

---

EDIT:

The value is received via browser using $_GET['id']. There is, in fact, a second value which is not used in an SQL query but a switch statement. So, the full request looks like this:

http://localhost/api/view.php?rsvp=<status>&id=<id>

That may help.

EDIT 2:

It may have been important to mention that the ids in question are in fact GUIDs, not numeric values. I ran all queries in the comments so far with a proper ID. Here are some results:

• http://localhost/api/view.php?id=62FD23D8-B6C0-03F1-D45A-C9AC33C91774%27;%20drop%20table%20users;-- => array(1) { [0]=> string(166) "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 'drop table users;--'' at line 1" }

• http://localhost/api/view.php?id=62FD23D8-B6C0-03F1-D45A-C9AC33C91774%27;select%20*%20from%20invitations;%27 => array(1) { [0]=> string(179) "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 'select * from invitations;''' at line 1" }

---

These following lines are the entirety of the code that makes use of $id:

// ...
if(isset($_GET['id'])) {
 $id = $_GET['id'];
 $db->select('invitations','Replied, Response, Registered',null,"Id = '".$id."'");
 $res = $db->getResult();
// ...
}

• pala_: ah no. i see it now. try ?id=1'; drop table users;--
• deceze: 1) In order to find exploits, it's pretty much a necessity to have access to the running code on a production system. Just having a code dump we cannot execute pretty much just forces us to guess. 2) mysql is not insecure per se, if applied properly it's fine. However, it's hard to know whether you're applying it properly or not without seeing the entire codebase. 3) Even if '; DROP TABLE .. doesn't work, just being able to perhaps add a condition and slightly change the meaning of the query may be enough for an attacker to glean useful information.
• β.εηοιτ.βε: I don't think you are going to make ; drop anything work as @deceze pointed out... just because mysql_query does not allow multiple queries with ; to be executed
• pala_: i'd say if your user can simply break it and cause a syntax error, then its broken.
• deceze: The fact that you can make the query misbehave (err out) is a sign that an attacker has some leverage on the system. How much leverage exactly and what can be levered with it in practice is a different topic, but it certainly doesn't bode well at all. Pay a pentester for an hour and you'll know exactly how much leverage there is.

Answer 1

Just the fact alone that some specific input results in a MySQL syntax error is a proof that the user input has some unintended influence on the SQL syntax, which means SQL injection is possible.

But the exploitation of this SQL injection is a different chapter. And MySQL, or more precisely PHP’s MySQL extension, does not support the execution of multiple statements with mysql_query by default:

mysql_query() sends a unique query (multiple queries are not supported) [...]

So the classic example of dropping a table won’t work and you’re limited to the capabilities of the current statement and the allowed syntax elements from the injection point on.

In your specific example, the SELECT limits you to

• reading arbitrary data from the database that the MySQL user has access to
• reading files using the LOAD_FILE function
• writing files using the INTO ... syntax

For reading arbitrary data, a UNION would be the best choice as the results seem to get reflected back to the user. All you have to ensure is that the resulting SQL is valid, which means your UNION must to have three columns (i. e., Replied, Response, and Registered from the existing SELECT):

' UNION SELECT '1','2','3

The resulting statement would then look like:

SELECT Replied, Response, Registered FROM invitations WHERE Id = '' UNION SELECT '1','2','3'

You now can replace the additionally selected values by your own expressions (including LOAD_FILE) or sub-queries.