The problem with embedding in MySQL

Question

Recently, I encoded my site in PHP, and I was very proud of myself for the good methods of disinfecting my input before I used it in the request. Everything was great until my friend said that I needed to misinform my contribution. When I tried to explain to him that he was disinfected, he showed me that he found everything in the user table in my database. I did not know how, so I thought I would publish what I was doing wrong, which is why my disinfection does not work. Here is the PHP code that he used:

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 did was change the id parameter, allowing him to use SQL injection in my database. I thought mysql_real_escape_string escaped all such characters, but apparently I was wrong. I did some tests with a normal line to find out what would happen, and here's what I 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?

Answer 1

Matte

mysql_real_escape_string () will only be filtered for specific characters, if you really want to prevent injection attacks, check out this other stack overflow article, which suggests using prepared statements:

Prepared reports

PHP Manual Record in Prepared Reports

Edit: Also check Slaks and Michael's calculations for wrapping your variable in single quotes.

Good luck

N

Answer 2

You need to put the escaped string in single quotes:

 WHERE `id` = '$id' 

Answer 3

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

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

Answer 4

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

 WHERE `id` = $id 

You directly interpolate user input into your SQL statement. This is an open barn door for SQL injection.

Answer 5

Role identifier. If it is a string, it will be displayed as 0. $ id = (int) $ _ GET ['id'];

In addition, MySQL supports quotes around strings and numbers in a query.

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

Answer 6

You are not using parameterized queries.

MDB2 allows this, although this library may go out of fashion.

Answer 7

Most likely your configuration has magic_quote_gpc, an old attempt in PHP to make scripts safe. He proved to have several flaws and has since become outdated and was completely removed at 5.4 the last time I heard about 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 misinform your entry.

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

Otherwise, there is nothing wrong with mysqli_real_escape_string ().

Answer 8

You cannot prevent SQL injections with mysql_real_escape_string() . It is used to escape special characters such as single quotes ( ' ), double quotes ( " ), etc.

To prevent SQL injection, you should use the PDO statements and filtering functions in PHP to sanitize user data.