PHP: SQL injection

SQL injection or SQLi is a code injection technique that exploits a security vulnerability. An injection occurs at the database level of an application (like queries). The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and unexpectedly executed. Using well designed query language interpreters can prevent SQL injections. In the examples below $userid can be substituted with $userid = $_GET[‘userid’] or $userid = $_POST[‘userid’].

Example 1:

// User input from $_GET or $_POST
$userid = "4 OR 1";
$query = "SELECT * FROM users WHERE userid=$userid";

Will give SELECT * FROM users WHERE userid=4 OR 1
The result will be all users.

Example 2:

// User input from $_GET or $_POST
$userid = "4 OR 1";
$query = "SELECT * FROM users WHERE userid='$userid'";

Will give SELECT * FROM users WHERE userid=’4 OR 1′
The result will be one user.

Example 3:

// User input from $_GET or $_POST
$userid = "4' OR '1";
$query = "SELECT * FROM users WHERE userid='$userid'";

Will give SELECT * FROM users WHERE userid=’4′ OR ‘1’
The result will be all users.

Example 4:

This example will use a comment to block the rest of the query. There are three types of SQL comments: , ({ and /*.

// User input from $_GET or $_POST
$userid = "4 OR 1 --";
$query = "SELECT * FROM users WHERE userid=$userid LIMIT 10";

Will give SELECT * FROM users WHERE userid=4 OR 1 — LIMIT 10
The result will be all users because the LIMIT 10 is only a comment in the query and will be ignored. LIMIT 10 means that the result will be limited to fetch the first 10 rows.

How to avoid?

A straightforward way to prevent injections is to escape characters that have a special meaning in SQL. For instance, every occurrence of a single quote (‘) in a parameter must be replaced by two single quotes (”) or escaped to form a valid SQL string literal. In PHP it is usual to escape parameters using the function mysql_real_escape_string(). Be sure to always use single quotes in your query.

// User input from $_GET or $_POST
$userid = "4' OR '1";
$userid = mysql_real_escape_string($userid);
$query = "SELECT * FROM users WHERE userid='$userid'";

Will give SELECT * FROM users WHERE userid=’4\’ OR \’1′
The result will be empty because this is an invalid SQL statement.

The following code can be used to build a simple query class to query the database.

$sql = "SELECT *
        FROM users
	WHERE user_id = '[user_id]'
        AND user_status = 1";
 
$query = new query();
$query->user_id = $_GET['user_id'];
$query->query($sql);
 
class Query {
  var $params = array();
 
  public function query($sql) {
    if (!empty($this->params)) {
      foreach ($this->params as $key => $value) {
        $value = get_magic_quotes_gpc() ? stripslashes($value) : $value;
        $value = mysql_real_escape_string($value, $this->connection);
        $sql = str_replace("[{$key}]", $value, $sql);
      }
    }
 
    public function __set($name, $value) {
      $this->params[$name] = $value;
    }
 
  }
 
  $this->resource = mysql_query($sql, $this->connection);
}

Leave a Reply