Is there an SQL injection protection by default in PHP?

Question

PHP controls that with get_magic_quotes_gpc();, however my question is: Is any SQL injection protection enabled by default when installing PHP > 5.xxxx?

I guess it is since I can't recall if I have enabled/disabled any options when dealing with this issue. On a side note, MySQL doesn't seem to be doing anything, since I tried to execute some simple SQL injection in ASP.net/C# with MySQL (community...5 something...) And it worked.

However when I tried the same in PHP - it was escaped with . Also, that was attempted on Windows 7.

Answer 1

Magic quotes is NOT a solution to prevent SQL Injection. It is by far insufficient to do proper character escaping. Just disable it and use prepared SQL statements with bound parameters. See example using PDO:

$pdo = new PDO("mysql:dbname=my_database", $dbUser, $dbPassword);
$sql = "SELECT * FROM users WHERE login = :login AND password = :password";
$stmt = $pdo->prepare($sql);
$stmt->bindValue("login", $_POST["login"]);
$stmt->bindValue("password", md5($_POST["password"]));
$stmt->execute();
$rows = $stmt->fetchAll();

Or be sure to properly escape the inserted values:

$pdo = new PDO("mysql:dbname=my_database", $dbUser, $dbPassword);
$sql = "SELECT * FROM users WHERE login = " . $pdo->quote($_POST["login"]) . " AND password = " . md5($_POST["password"]);
$rows = $pdo->query($sql)->fetchAll();

Answer 2

I recommend using http://php.net/manual/en/function.mysql-real-escape-string.php

I always use that whenever I get an input from a user.

Answer 3

SQL Injection can not be prevented by the PL or the Platform or even the Framework if the programmer doesn't keep it in mind,

There are two general programmatic methods of SQLi prevention :

1. escape all dynamic strings and then concat them to the query (a little unsafe)
2. use prepared statements to separate data from query

To use the former try :

$cond = mysql_real_escape_string($cond);
mysql_query("SELECT * FROM table WHERE {$cond}");

To use the latter, you could use PDO in PHP, which has prepared statements supported in.

Answer 4

What all the other answers didn't metion, mysql_real_escape_string WORKS ONLY FOR STRINGS.
I've seen something like this at least over 9000 times.

$id=mysql_real_escape_string($_GET['id']);
mysql_query("SELECT foo FROM bar WHERE foobar=$id");

Keep in mind, that you should explicit cast to an int in this case.

$id=(int)$_GET['id'];

Answer 5

Use a database class which does basic sanitation in case you forgot to do it e.g. mysql_real_escape_string. I personally use something like this for text inputs (it is not recursive for arrays).

function escape($mixed){
    if(is_array($mixed)){
        foreach($mixed as $m => $value){
            $mixed[$m] = mysql_real_escape_string(htmlspecialchars($value, ENT_QUOTES, "UTF-8"));
        }
    }else{
        $mixed =  mysql_real_escape_string(htmlspecialchars($mixed, ENT_QUOTES, "UTF-8"));
    }
return $mixed;
}

But you should manually sanitize every input using preg_replace for example using this...

function replace($string, $type = "an", $custom = ""){
    switch($type){
        case "n": $regex = "0-9"; break;
        case "a": $regex = "a-zA-Z"; break;
        case "an": $regex = "a-zA-Z0-9"; break;
    }
    return preg_replace("#([^$regex$custom]+)#is", "", $string);
}


$_POST["phone"] = "+387 61 05 85 05";
$phone = replace($_POST["phone"], "n"); // 38761058505

There is no silver bullet for this.