Protect from SQL injection

Question

I am trying to learn php and want to use a function to protect form agains SQL injection! But somehow form record my db every data which contains any special chars like '"=)/()/*/

My filter function:

function filter($data) {
    $data = trim(htmlentities(strip_tags($data)));

    if (get_magic_quotes_gpc())
        $data = stripslashes($data);

    $data = mysql_real_escape_string($data);

    return $data;
}

Register Page to get POST datas:

foreach($_POST as $key => $value) {
    $data[$key] = filter($value);
}

Then i am trying special characters and form save them! What i an doing wrong?

Answer 1

If you want to protect against SQL injection, the best approach is to use PDO and prepared queries, where all user-provided data is passed in via execute(), like this:

$stmt = $pdo->prepare("INSERT INTO foo (a_column, b_column) VALUES (:a, :b)");
$stmt->execute(array(':a' => $a, ':b' => $b));

You do not have to perform any manipulation on $a or $b; PDO will bind the parameters the right way, no matter which database you are using.

Answer 2

Erm... the point of preventing SQL injection is to continue to allow the user to type whatever they like, without it putting the server or other users at risk. htmlspecialchars is a good place to start, as it takes things that look like HTML tags and renders them inoccuous. The stripslashes you used is good, although the latest version of PHP removed magic quotes. mysql_real_escape_string allows you to insert anything in the database in the form of a string with reasonable safety.

So your filter function should look like:

function filter($data) {
    if( get_magic_quotes_gpc()) $data = stripslashes($data);
    return trim(mysql_real_escape_string(htmlspecialchars($data));
}

Now, if you actually want a filter, as in one that only allows certain characters, use a regex function such as preg_match.