Geek Answers Handbook

Why should mysql_real_escape_string () not have to avoid SQL injection?

I heard people say (regarding C # / SQL Server, but also regarding PHP / MySql): Do not avoid strings manually - use stored procedures instead .

Well, I can accept this offer, but why? A lot of people say (including SO) mysql_real_escape_string() is quite enough, mysql_real_escape_string() is good, mysql_real_escape_string() is the first way to protect it.

Why? Is there a case where mysql_real_escape_string() can fail? At least one ... I don't need much :)

+8
security php mysql sql-injection
source share
5 answers

When mysql_real_escape_string FAIL :

 $sql = "SELECT * FROM users WHERE id=" + mysql_real_escape_string($_GET['id']);

If $_GET['user_id'] set to 1 OR 1 = 1 , there are no special characters and it is not filtered.

Result: All rows are returned.

Is getting worse. How about this ... what if $_GET['user_id'] set to 1 OR is_admin = 1 ?

The function is intended for use in single quotes only.

+9
source share

In mysql_real_escape_string , two problems can occur:

  • You may forget to use it
  • If you use certain encodings of multibyte connections and you set these encodings with SET NAMES instead of mysql_set_charset , as it is correct, it can still leave you vulnerable

Update:

  • You can use UTF-8 (although this does not mean that you should continue to use SET NAMES !)
  • See here for clarification.
+3
source share

For information only: mysql_real_escape_string() does not exit % and _ . These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE .

+1
source share

mysql_real_escape_string() may fail to clear input. Since mysql_real_esacpe_string() takes into account the character set when clearing strings. There is a problem. You can change the character using the mysql_query function by sending a query to change the character set of the connection. However, mysql_real_escape_string() does not pay attention to the set you are using, and it avoids some characters improperly.

Another thing is to constantly refer to it manually. Even wrapping its function is PITA because it means that you need to create your own database / database abstraction layer in order to be able to automate calls to mysql_real_escape_string() .

This is why we have a PDO in PHP that helps us to make things easier, and you can use parameterized prepared statements, which are the preferred way to solve repeated queries that change data.

Prepare the instruction, bind the input variables and clear the input according to the database driver used and the character set of the connection. It is less code and completely safe.

0
source share

There is only one thing about mysql_real_escape_string () and SQL Injection -

The former has nothing to do with the latter.

Although this sounds paradoxical, nothing could be more true.

Here are 2 statements confirming it

  • You need to avoid quoted strings as this function helps nothing.
  • In fact, you need to avoid every line that you add to the query, even the safest one. simply because it may contain some special character and thus break up the request (just like an accident, not a malicious plot).

Thus, when applicable, this function must be used in any case, despite all the dangers or concerns. And in any other case, nothing will help.

The only thing I have to add is that the prepared statements also do not provide complete protection. Here is an explanation and recommendations: stack overflow

0
source share

More articles:


All Articles