The topic of writing secure applications in PHP covers more than just writing good PHP code. Most applications make use of a database of some kind. Many times, vulnerabilities that affect the entire application, are introduced when building the SQL code. Today's Tip of the Day deals with one easy solution developers can implement.
When dealing with numbers in a SQL query, always cast.Even if you are filtering your input, a good and easy to implement safety measure is to cast all numeric values in the SQL statement. Take for example the following code.
$myId = filter_var($_GET['id'],FILTER_VALIDATE_INT);
$sql = 'SELECT * FROM table WHERE id = '.$myId;
Even though you are applying the native PHP filters built into PHP 5.2, there is something additional you can do. Try this instead.
$myId = filter_var($_GET['id'],FILTER_VALIDATE_INT );
$sql = 'SELECT * FROM table WHERE id = '.(int)$myId;
This final cast of the variable to an int removes any doubt about what will be passed to MySQL. The example above is purposefully simplified. In real-life situations, the code would be more complex and the chance for error much greater. By applying the final cast to in building the select statement, you are adding one more level of safety into your application.
Comments
$myId = filter_var($_GET['id'],FILTER_VALIDATE_INT );
$sql = 'SELECT * FROM table WHERE id = '.$myId.'::integer';
Of course you'll have to use the specific cast to what you set for that field such as smallint, integer or bigint but it basically does the same thing as using (int) in PHP except that the Database does the work rather than the PHP code itself in checking for data integrity constraints.
Doing the type casting in the SQL (as indicated in the previous comment) is too late and still leaves you vulnerable to exploit.
$sql = 'SELECT * FROM table WHERE id = '.$myId.'::integer';
$myId could be something like: '5 or 1=1'
Do the type casting in PHP as the original article indicates.