MySQL Injection Problem

I've been coding my website in PHP lately and I was pretty proud of myself for my good practices of sanitizing my input before I used it in a query. It was all going great until my friend said I need to 开发者_如何学编程sanitize my input. When I tried to explain to him that it was sanitized, he showed me that he had found everything in 'users' table in my database. I didn't know how, so I thought I would post what I was doing wrong that made my sanitizing not work. Here is the PHP code he was exploiting:

start_mysql(); // Starts the databases stuff, etc.

$id = mysql_real_escape_string($_GET['id']);
$game = mysql_query("SELECT * FROM `games` WHERE `id` = $id LIMIT 0, 1");

All he was doing was changing the id parameter, making him able to use SQL injection on my database. I thought mysql_real_escape_string escaped all characters like that, but apparently I was wrong. I did some tests with a normal string to see what would happen, and this is what it said

URL: /game.php?id=' OR '' = '

echo($_GET['id']); // This echo'd: \' OR \'\' = \'
echo(mysql_real_escape_string($_GET['id'])); // This echo'd: \\\' OR \\\'\\\' = \\\'

So, my simple question is, what am I doing wrong?

You need to put the escaped string in single quotes:

WHERE `id` = '$id' 

Since id was an integer parameter and you did not surround it in single-quotes in your SQL, the value of $id is sent directly into your query. If you were expecting an integer id, then you should verify that the value of $_GET['id'] is a valid integer.

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

Matt,

mysql_real_escape_string() will only filter for certain characters, if you truly want to prevent injection attacks check out this other Stack Overflow article that suggests you use Prepared statements:

Prepared Statements

PHP Manual entry on Prepared statements

Edit: Also check out Slaks and Michael's postings about wrapping your variable in single quotes.

Good luck!

H

Cast ID. If it is a string it will cast as 0. $id = (int)$_GET['id'];

Also, MySQL support quotes around both string and numbers in the query.

$game = mysql_query("SELECT * FROM `games` WHERE `id` = '$id' LIMIT 0, 1");

You need to use the parameter binding api. The problem is in this piece of code:

WHERE `id` = $id

You are directly interpolating user input into your SQL statement. That's the open barn door for SQL injection attacks.

You're not using parameterized queries.

MDB2 allows this, though that library may be falling out of favor.

It's very likely that your configuration has magic_quote_gpc, an ancien attempt in PHP to make scripts secure magically. It proved to have multiple flaws and was since deprecated and was scheduled to be completely removed in 5.4 the last time I heard of it.

If you have access to your php.ini configuration, you should disable it. Otherwise, you can modify your script to take it into account and sanitize your input.

All of this is documented here: http://www.php.net/manual/en/security.magicquotes.disabling.php

Otherwise, there is nothing wrong with mysqli_real_escape_string().

You can't prevent SQL injections using mysql_real_escape_string(). It is used for escaping special characters like single quotes ('), double quotes ("), etc.

To prevent SQL injections you have to use PDO statements and filter functions in PHP for sanitizing the user data.