MENU


Validating Input

Simple preventive measures against SQL injection involve converting the input to numbers when numbers are expected, and escaping the single quotes when strings are expected. Three functions are useful here:

1. settype(&$m, $s) sets the type of variable $m to type $s. $s can be ‘bool’, ‘boolean’, ‘int’, ‘integer’, ‘float’, ‘double’, ‘string’, ‘array’, ‘object’, ‘null’.
2. mysqli_real_escape_string($l, $s) encodes NUL, \n, \r, \, ‘, “, and Control-Z to be used in an SQL statement.
3. str_replace($s1, $s2, $s3 [,$i]) returns a copy of $s3 with all $s1 replaced by $s2. This can be used to convert the single quotes forth and back. 

<!DOCTYPE html><html><head></head><body><pre>
<?php

// Example 1
$input = "99 OR TRUE";
settype($input,"int");
echo $input."\n\n";

// Example 2
$input = "' OR TRUE --";
$link=mysqli_connect("localhost","root","passwd","testDB");
echo mysqli_real_escape_string($link,$input)."\n\n";

// Example 3
$size = "'
UNION 
SELECT '1', CONCAT(uname,'-',passwd) AS NAME, 
'2000-01-01', '0' FROM users -- ";
$size = str_replace("'","--SQ--",$size);
echo $size."\n\n";
$size = str_replace("--SQ--","'",$size);
echo $size;

?>
</pre></body></html>

99

\' OR TRUE --

--SQ--
UNION 
SELECT --SQ--1--SQ--, CONCAT(uname,--SQ-----SQ--,passwd) AS NAME, 
--SQ--2000-01-01--SQ--, --SQ--0--SQ-- FROM users -- 

'
UNION 
SELECT '1', CONCAT(uname,'-',passwd) AS NAME, 
'2000-01-01', '0' FROM users --