Character strings formatting in web applications

HTML forms

	function format_input ($s)	
	{
		if (function_exists('get_magic_quotes_gpc') && get_magic_quotes_gpc())
		{
			$r = stripslashes($s);
			return $r;
		}
		else return $s;
	}

	function post ($champ)
	{
		return format_input($_POST[$champ]);
	}

	$field = post('field');

	function format_value ($s)
	{
		$r = str_replace('"','"',$s);
		return $r;
	}

Databases

For most databases, character strings are delimited by simple quotes, so this character must be escaped by doubling it. For some databases, the backslash must also be doubled. More precisely :

• For all databases, replace ' by ''
• For MySQL and PostgreSQL, also replace \ by \\

For SQLite :

	function format_query ($s)
	{
		$s1 = str_replace('\'', '\'\'', $s);
		return $s1;
	}

For MySQL and PostgreSQL :

	function format_query ($s)
	{
		$s1 = str_replace('\'', '\'\'', $s);
		$s1 = str_replace('\\', '\\\\', $s1);
		return $s1;
	}

Numbers must also be protected from SQL injection.

Example :

	$i = $_GET['i'];
	$query = "SELECT myfield FROM mytable WHERE i=$i";

Normally the URL is something like "http://mysite.com/index.php?i=123". But a hacker could modify it, for example : "http://mysite.com/index.php?i=123 AND 1=2 UNION SELECT password AS myfield FROM users WHERE name = 'admin'" which gives the query : "SELECT myfield FROM mytable WHERE i=123 AND 1=2 UNION SELECT password AS myfield FROM users WHERE name = 'admin'".

	function check_number ($s)
	{
		if (strval(intval($s)) == $s)
			return $s;
		else
		{
			echo "<h2><font color=darkred>PLEASE DON'T TRY TO HACK THIS SITE !!!</font></h2>";
			exit();
		}
	}

	$i = check_number($_GET['i']);
	$query = "SELECT myfield FROM mytable WHERE i=$i";