CODEWITHSUNDEEP
phpmysqlsecuritysql-injection
markzzz
markzzz

Reputation: 47947

Why mysql_real_escape_string() shouldn't avoid any SQL Injection?

I've heard people say (in relation to C#/SQL Server, but also in relation to PHP/MySql): Don't escape strings manually - use stored procedures instead.

Ok, I can accept this suggestion, but why? Many say (including on SO) mysql_real_escape_string() is quite enough, mysql_real_escape_string() is good, mysql_real_escape_string() is the first way of protection.

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

Upvotes: 8

Views: 2186

Answers (5)

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

Just for info: mysql_real_escape_string() does not escape % and _. These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE.

Upvotes: 1

Your Common Sense
Your Common Sense

Reputation: 157828

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

The former does not have a slightest relation to the latter.

Although it sounds paradoxical, nothing can be more true.

Here are 2 statements proving it

  1. You have to escape quoted strings only, as this function helps nothing else.
  2. In fact, you have to escape every string you are adding to the query, even safiest one. just because it may contain some special character and thus break the query (just as an accident, not malicious plot).

Thus, when applicable, this function have to be used anyway, despite of whatever dangers or fears. And in any other case it will help nothing.

The only thing I have to add is that prepared statements do not provide full protection either. Here is the explanation and recommendations: https://stackoverflow.com/a/8255054/285587

Upvotes: 0

Moz Morris
Moz Morris

Reputation: 6751

When mysql_real_escape_string FAIL:

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

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

The result: All rows are returned.

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

The function is only designed to be used when inside single quotes.

Upvotes: 9

Jon
Jon

Reputation: 437326

There are two things that can go wrong with mysql_real_escape_string:

  • You can forget to use it
  • If you are using some specific multibyte connection encodings, and you have set these encodings with SET NAMES instead of mysql_set_charset as is proper, it can still leave you vulnerable

Update:

  • You are safe with UTF-8 (although this does not mean that you should continue using SET NAMES!)
  • For an explanation, see here

Upvotes: 3

N.B.
N.B.

Reputation: 14060

mysql_real_escape_string() can fail to clean the input. Since mysql_real_esacpe_string() takes character set into account while cleaning strings. There's the problem. You can change character via mysql_query function sending the query to change connection's character set. However, mysql_real_escape_string() is oblivious to the set you're using and it will escape some characters improperly.

The other thing is constantly invoking it manually. Even wrapping it in a function is a P.I.T.A. because it implies you have to create some sort of database wrapper / database abstraction layer of your own in order to be able to automate calls to mysql_real_escape_string().

That's why we have PDO in PHP which helps us alleviate all of the above and you get to use parametrized prepared statements which are preferable way of dealing with repeating queries that alter the data.

Prepare the statement, bind input variables and it will clean the input according to the database driver being used and connection's character set. It's less code and completely safe.

Upvotes: 0

Related Questions