Input sanitization and escaping for database and STDOUT using PHP

No user input should be used without prior sanitisation and/or validation. PHP 5.2 introduced Data Filtering.

Data filtering is categorised to sanitisation and validation. The sanitisation approach barely trims the data that does not belong to the desired string format.

filter_var('foo1bar', FILTER_SANITIZE_NUMBER_INT); // string(1) "1"
filter_var('<script>foo</script>bar', FILTER_SANITIZE_STRING); // string(6) "foobar"

The validation filters return the original value or FALSE if the format is not satisfied.

filter_var('g.kuizinas@anuary.com', FILTER_VALIDATE_EMAIL); // 'g.kuizinas@anuary.com'
filter_var('49.144.122.251', FILTER_VALIDATE_IP); // 'ip'
filter_var(123, ['options' => ['regexp' => '/[^0-9]+/']]); // FALSE

Read through the whole Data Filtering manual to learn about the other filters, the relevant options and flags.

These functions can be used to sanitise and validate any form data or parameters used in dispatcher logic or before being displayed in stdout. However, you still need to escape the data to use it in a query.

There is the infamous mysql_escape_string (deprecated) and mysql_real_escape_string. The latter takes into account the connection character set, therefore is generally safe to use, though there are exceptions:

SET NAMES is usually used to switch the encoding from what is default to what the application needs. This is done in a way that mysql_real_escape_string doesn’t know about this. This means if you switch to some multi byte encoding that allows backslash as 2nd 3rd 4th… byte you run into trouble, because mysql_real_escape_string doesn’t escape correctly. UTF-8 is safe…

Stefan Esser

The work around to the latter issue is to use mysql_set_charset instead of SET NAMES.

mysql_real_escape_string is not officially deprecated, though one day it will be deprecated and eventually removed from PHP. Therefore, you should be using either mysqli or PDO_MySQL.

There is also the addslashes, which is normally used when DBMS specific escape function doesn't exist. However, this post assumes that you are using MySQL (forget this function exists).

PHP Data Objects

PDO::quote is similar to mysql_real_escape_string. PHP manual discourages to use it in favour of the prepared statements.

If you are using this function to build SQL statements, you are strongly recommended to use PDO::prepare() to prepare SQL statements with bound parameters instead of using PDO::quote() to interpolate user input into an SQL statement. Prepared statements with bound parameters are not only more portable, more convenient, immune to SQL injection, but are often much faster to execute than interpolated queries, as both the server and client side can cache a compiled form of the query.

http://www.php.net/manual/en/pdo.quote.php

You should listen to what the PHP manual says, though don't take it word-by-word, e.g., faster to execute than interpolated queries – this is only the case if you re-use the same prepared statement more than a few times.

The other catch is that, if you are using PHP 5.4.5 or older (possibly even the more recent version), by default PDO will emulate MySQL queries. I've learned that the hard way. Though, to sum up, if PDO::ATTR_EMULATE_PREPARES is not explicitly set to FALSE the PDO will interpolate user input into an SQL statement.

Examples

You have either stumbled across this post by chance or (most likely) because someone noticed any of the following patterns in your code:

mysql_query("SELECT 1 FROM `foo` WHERE `bar` = {$_POST['id']};");
mysql_query("SELECT 1 FROM `foo` WHERE `bar` = '" . mysql_escape_string($_POST['id']) . "';");
mysql_query("SELECT 1 FROM `foo` WHERE `bar` = '" . mysql_real_escape_string($_POST['id']) . "';");
$pdo->query("SELECT 1 FROM `foo` WHERE `bar` = {$pdo->quote($_POST['id'], PDO::PARAM_INT)};"); // It will work, though the parameter will always be passed as a string. Not very efficient.

If you don't want to learn what is SQL injection or truncated data by experiencing the consequences of making mistakes, learn to use the prepared statements.

$id		= filter_input(INPUT_POST, FILTER_SANITIZE_NUMBER_INT);

if(!empty($id))
{
	$sth	= $db->prepare("SELECT 1 FROM `foo` WHERE `bar` = :id;");
	
	$sth->bindValue('id', $id, PDO::PARAM_INT);
	
	$sth->execute();
}

This code takes the user input passed through the HTTP request method POST, sanitises the input to make sure it is integer and performs the query with the bound value.

The filter_input is optional in the SELECT context. The worst that can happen is the query will not return any data. However, it is not optional in the INSERT or UPDATE context. You should always cast FILTER_SANITIZE_STRING (or one of the specific filter) on any input.

To sum up, stop reinventing the wheel and use the right tools for the job.