Features

Protecting against SQL injection and XSS attacks in PHP

Learn how to write PHP code that is protected against SQL and XSS attacks.

If you take a look at Google News and do a quick search for SQL Injection in the news results, you may be surprised to see dozens of stories on high profile sites who have had data stolen or their sites compromised in SQL Injection attacks.

Apparently, many of these are automated these days and they are often connected with organised crime. It only takes one little flaw in the code on your website, or on another website that shares your server, for your site and its data to be compromised, and the effects of this are devastating.

Attackers could not only read data in your database, but also potentially download it, steal it, or even gain access to the server itself by installing a backdoor - all via a small flaw in some of your PHP and SQL code.

If you want to protect yourself, and the other sites that share your server, from potential attacks, you'll want to read up on how to ensure that any code you write is safe from attacks.

mysql_real_escape_string()
The mysql_real_escape_string() function was added in PHP 4.3.0 and it escapes special characters - such as x00, n, r, , ', " and x1a - in a string of data that you are passing to a SQL statement.

Since PHP 4.3, it's been recommended that you use this function at all times to sanitise data that you're passing to a SQL statement to protect against SQL injection attacks.

It replaces mysql_escape_string() which has just been deprecated in PHP 5.3.0 and is going to be removed from PHP 6.0. It does exactly the same thing as mysql_real_escape_string() but it will soon start throwing notice errors, so you'll need to upgrade your code if you've used it in the past.

In its simplest form you can use mysql_real_escape_string() like this:

$q = mysql_real_escape_string("eggs");
$sql = "SELECT price FROM shopping WHERE product = '$eggs'";
mysql_query($sql);

However, the recommended way of using it is now to do so in combination with sprintf(), like this:

$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",

mysql_real_escape_string($user),

mysql_real_escape_string($password));

The order of the mysql_real_escape_string() functions containing the data to be added to the statement via sprintf() is obviously critical here.

The other really important thing is that all variables passed to SQL must be placed inside quotes.

Quick fixes to big problems
If you're trying to repair an old site that does not yet use mysql_real_escape_string() and it is simply not feasible to hand-edit hundreds or thousands of individual pages, you can also apply mysql_real_escape_string() directly to all GET or POST vars.

foreach ($_POST as $key => $value) {
$_POST[$key] = mysql_real_escape_string($value);
}

foreach ($_GET as $key => $value) {
$_GET[$key] = mysql_real_escape_string($value);
}

The code above gets all of the POST and GET vars and then runs mysql_real_escape_string() against each value. Providing this is run before any other code on your site, for example by adding it to a header file, it should protect your old code without the need to modify the whole lot.

Create a sanitisation function
One useful tip would be to create your own function for sanitising any data from GET or POST vars. This means that you can have a single function and also protect your site against XSS (Cross Site Scripting) attacks too.

Here's the one I use. If, at any point, I need to change this to protect against something else, I can simply edit this single function rather than needing to modify code used across a massive site.

function sanitise($string){
$string = strip_tags($string); // Remove HTML
$string = htmlspecialchars($string); // Convert characters
$string = trim(rtrim(ltrim($string))); // Remove spaces
$string = mysql_real_escape_string($string); // Prevent SQL Injection
return $string;
}

Using MySQLi
A relatively recent introduction to PHP, MySQLi (short for MySQL improved), is a class designed to make it easier, safer and more effective to connect to MySQL databases and manipulate the data they contain. You will only be able to use MySQLi functions if it has been compiled into PHP and you'll only be able to make use of it if you're running MySQL version 4.1.13 or newer.

In a nutshell, MySQLi provides an object oriented way of accessing your database. It provides features not present in the old procedural system and it's also meant to be more secure. This is the currently recommended way of accessing MySQL via PHP, so if you're writing new code, you really ought to be using this rather than the old way, ext/mysql.

$mysqli = new mysqli(HOSTNAME,USERNAME,PASSWORD,DATABASE);

if($statement = $mysqli->prepare("SELECT title, sid

FROM sometable WHERE author =? AND title LIKE?")){

   $statement->bind_param("ss",$author,$title);
   $statement->execute();
   $statement->bind_results($result);
   $statement->fetch();

   while($row = $result->fetch_object()){
       $page1_title = stripslashes($row->title);
       $sid = stripslashes($row->sid);
       echo "$sid: $title";
   }

   $statement->close();

}

The code above uses a prepared statement, but rather than using the variable names in the statement, as you would in an old-style PHP/SQL query, it uses question marks. We then use the bind_param() function to bind our input variables to the statement in the order than they are used.

Why is this more secure? Well, the bind_param() function uses special parameter identifiers, such as the two s characters used in the example above, to define what type of data we're expecting.

The s character means the parameter will be a string, the b character means it will be a boolean, i means it will be an integer, d means it's a double, b means it's a blob. The number of variables and the length of the string types need to match the parameters in the statement.