2

Possible Duplicate:
Best way to prevent SQL Injection in PHP

I am wondering about sql injection and want to know when to use mysqli_real_escape_string.

Do I need to use it every time I have a WHERE clause in my query?

Community
  • 1
  • 1
jaypabs
  • 1,519
  • 10
  • 43
  • 62
  • You need to escape all parameters in SQL queries that could potentially contain harmful characters. This is especially true of user input. – nickb Jul 25 '12 at 15:23
  • You should almost always be using bound arguments, not mysqli_real_escape_string – Quentin Jul 25 '12 at 15:24

3 Answers3

3

You should use mysqli_real_escape_string for any data that comes from the user or can't be trusted.

Stefan
  • 2,961
  • 1
  • 22
  • 34
0

You have to use it when you include $_REQUEST "vars" in your query eg.

 $query = "UPDATE foo set bar = '{$_GET['var']}' ..."

 $query = "INSERT INTO foo ('{$_POST['var']}',) ..."

 $query = "SELECT bla from foo WHERE bar = '{$_COOKIE['var']}'"

each of this querys must be mysqli_real_escape_string to provide injections ...

donald123
  • 5,638
  • 3
  • 26
  • 23
  • you mean i have to use mysqli_real_escape_string even in the value of the update statement like: `$query = "UPDATE foo set bar = mysqli_real_escape_string($_GET['var']) ..."` and also in WHERE clause like: `$query = "SELECT bla from foo WHERE bar = mysqli_real_escape_string($_COOKIE['var'])"`? – jaypabs Jul 26 '12 at 01:27
0

You use mysqli_real_escape_string or any variation of it to make sure data form any user input field is properly escaped.

For example, you have a form with a few inputs. You click submit and the data is sent as a request to your PHP script. In your script you insert into a database the values the user posted.

If a user typed the following into an input field on user login for example:

' WHERE `username` = {x} AND password > 1;

There is potential for that person to have all of the data from the table you are working with. Using:

mysqli_real_escape_string($input)

Would yield the above as:

\' WHERE `username` = {x} AND password > 1;

The escape string on the ' would stop a possible SQL injection attack from working.

That said there is no reason you should be using mysqli_real_escape_string() as the PDO and bound parameters are far superior at deflecting a host of different SQL attacks / injection methods. Learn it and use it!

Edit due to comments

% and _ will also need special treatment above and beyond mysqli_real_escape_string()

function escape_extra_chars ( $string )
{
    $string = preg_replace("/\%/", "\%", $string);
    $string = preg_replace("/\_/", "\_", $string);

    return $string;
}

Along those lines anyways (that func is untested)

David Barker
  • 14,484
  • 3
  • 48
  • 77
  • hi, i am actually using prepared statement on my other script but since i have ported most of my script from mysql to mysqli i have a lot of sql statement that is using a traditional programming. so instead of using prepared statement, i decided to use mysqli_query instead of modifying all my code to use prepared statement. so for the meantime is it safe to use code like: `mysqli_real_escape_string($_POST['username'])`? – jaypabs Jul 26 '12 at 01:20
  • @jaypabs yes you can use it like that, and it will provide a level of security, however it doesn't escape strings that contain special chars: `%` and `_` that are used in LIKE clauses. As they are usually contained within double quotes in the query they are still a risk. (see ammended info in my answer) – David Barker Jul 26 '12 at 08:35
  • does this mean that we really have to use prepared statement? what happened to `mysqli_query`? is `mysqli_query` already useless? – jaypabs Jul 26 '12 at 08:38
  • Prepared statements provide you with a much more robust defense than mysqli or mysql could ever do out of the box. Managing all user inputted data in the fashion described above gets very tedious indeed. And, I wouldn't say `mysqli` was dead, but it is certainly the least preferred choice. – David Barker Jul 26 '12 at 08:51
  • Worth mentioning that whether you're using PDO or not you should also always use 'htmlspecialchars()` as well (http://www.php.net/manual/en/function.htmlspecialchars.php) when inputting into a db – David Barker Jul 26 '12 at 08:53