SQL injection protection mechanism in php

Question

So typically you would collect data from the form and insert it into the database.

I know that mysql_real_escape_string() is used to eliminate the threat, but I would like to come with a mechanism that takes $_POST and processed it making it safe for database usage.

What would be the ideal and elegant way to achieve this? Is simply leafing through the post array enough to make the array safe?

PDO PDO PDO argh! http://php.net/manual/en/ref.pdo-mysql.php The `mysql_` is deprecated. As someone might mention at least once here. — ficuscr, Oct 23 '12 at 18:39
I don't really get the question. Other than using the mysqli version, I think you're on the right track with real_escape_string.. — Joost, Oct 23 '12 at 18:39
Using PHP's standard PDO library will help that. Here's a SO article that covers it: http://stackoverflow.com/questions/4364686/how-do-i-sanitize-input-with-pdo — Kai Qing, Oct 23 '12 at 18:40
Take a look at prepared statements using PDO, they add a bit more complication, but they give you that fuzzy, secure feeling inside. http://php.net/manual/en/pdo.prepared-statements.php — Brian Duncan, Oct 23 '12 at 18:41

score 3 · Answer 1 · answered Oct 23 '12 at 18:46

Sometimes you know something has to be a string, sometimes you know it has to be an integer. Sometimes you know it has to be an email address, sometimes you know it has to be a 4 character long string.

The mysql extension is of course deprecated, and has been for a while. The PHP docs say:

This extension is not recommended for writing new code. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.

PDO is the 'right' way to use MYSQL in PHP. If that's not Ok for some reason, use mysqli. But, just use PDO.

From the docs:

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindValue(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR);
$sth->execute();
?>

Note that calories is an int, and colour is a string. This is a much much better way to deal with things.

This in conjunction with filter_var can ensure that things are safe.

http://www.php.net/manual/en/filter.filters.sanitize.php and http://www.php.net/manual/en/filter.filters.validate.php describe the different filters avaliable.

score 2 · Answer 2 · answered Oct 23 '12 at 18:39

2

use mysqli_real_escape_string and array_map

$_POST = array_map('mysqli_real_escape_string', $_POST);

and if you want to do the same to all querystring variables do:

$_GET = array_map('mysqli_real_escape_string', $_GET);

answered Oct 23 '12 at 18:39

DiverseAndRemote.com

19,314
10
61
70

score -1 · Answer 3 · answered Oct 23 '12 at 18:41

-1

I use this 2 functions:

function getMySQLString($string) {
    global $link;

    /* Si esta activaldo el magic quotes, saco los caracteres de escape */
    if (get_magic_quotes_gpc()) {
        $string = stripslashes($string);
    }

    /* Armo el string. */
    return mysqli_real_escape_string($link, $string);
}


function toSecureArray($array) {
    $result = Array();

    /* Recorro todas las llaves del campo */
    $keys = array_keys($array);
    foreach ($keys as $key_id => $value) {
        if (!is_array($array[$value]))
            $result[$value] = getMySQLString($array[$value]);
    }

    return $result;
}

"toSecureArray" is not recursive, you can make it better. :)

answered Oct 23 '12 at 18:41

Nahuelistico

42
2

Personally I don't like to see examples that are written around PHP < 5.2 versions. This code may have been suitable 8 years ago. In my opinion it is not any longer. – ficuscr Oct 23 '12 at 18:45
Can you explain why? I dont use PDO because of particular reasons, i think that this code is valid even for PHP > 5.2 versions. – Nahuelistico Oct 23 '12 at 19:31
1

Sure. Your code is fine in terms of syntax and in truth almost reads like the php.net example for `get_magic_quotes_gpc`. But it is dated `"This feature has been DEPRECATED as of PHP 5.3.0 and REMOVED as of PHP 5.4.0."`. I think at some point you have to stop writing code around things that went away two major versions ago. Really `get_magic_quotes_gpc` should _always_ return false unless you are versions behind. [This](http://stackoverflow.com/questions/1742066/why-is-pdo-better-for-escaping-mysql-queries-querystrings-than-mysql-real-escape) explains advantages of PDO over mysqli_. – ficuscr Oct 23 '12 at 19:44

SQL injection protection mechanism in php

3 Answers3