View Full Version : Question about MySQL injection protection

12-15-2011, 09:49 PM
Well I've a question about protection for MySQL injection, I often see stuff like: mysql_escape_string which should protect against it. But well could someone explain me if it does work or it doesn't. And could someone show me a (other) method of protecting. Since I am pretty confused now. I've heard different stories.

Old Pedant
12-15-2011, 10:48 PM
MySQL is not terribly prone to SQL Injection attacks. Much less so than SQL Server, for example. If, indeed, you use mysql_escape_string, along with making sure that numeric values *are* numeric, you should be completely protected.

In fact, though, if you are coding in PHP you are pretty safe no matter what. That's because the MySQL library for PHP (or at least the default one...you could install a custom one) only allows for one query at a time.

In general, SQL injection attacks take advantage of systems that allow multiple SQL statements in a single query. So they terminate the first query and then tack on a second (or 2nd, 3rd, etc.) query that does the real damage.

Example (using PHP notation, but remembering this won't work with PHP's MySQL library):

$id = $_GET["id"];
$sql = "SELECT * FROM users WHERE id = $id";

Okay, suppose the hacker hit your site with the URL

www.yoursite.com/yourpage.php?id=1;drop table users

If you code that naively, as shown, you will end up executing

SELECT * FROM users WHERE id = 1;drop table users


In this case, mysql_real_escape_string wouldn't help you. What you really should be doing is ensuring that the variable $id contains *ONLY* an integer number--a valid id.

Maybe, though, this is one reason that you see so many PHP programs that do

$id = mysql_real_escape_string($_GET["id"]);
$sql = "SELECT * FROM users WHERE id = '$id' "; // notice the apostrophes!

By putting the ID value into the apostrophes, there is no possible way for the result to be taken as two separate queries.

But once again, with the standard PHP library this isn't an issue, as PHP should only allow the first statement to execute.

12-15-2011, 10:58 PM
so if i am correct i don't take a risk when i put my own made register system online. Since me is told the escape string did make sense i used it so i could post a part of the code tomorrow when i am back on my pc. To see if it does make sense