SQL injection that gets around mysql_real_escape_string()

Php Mysql Sql Security Sql Injection

SQL injection is a common security vulnerability where an attacker can manipulate SQL queries by inserting malicious code. It can allow unauthorized access to a database, modify data, or even delete the entire database. One commonly used method to prevent SQL injection is to use the mysql_real_escape_string() function in PHP.

How does mysql_real_escape_string() work?

The mysql_real_escape_string() function in PHP is designed to escape special characters in a string before it is used in an SQL query. It adds a backslash (\) before any character that has special meaning in SQL, such as quotes or semicolons. This ensures that the special characters are treated as literal characters and not part of the SQL syntax.

Example:

$login = mysql_real_escape_string(GetFromPost('login'));
$password = mysql_real_escape_string(GetFromPost('password'));

$sql = "SELECT * FROM table WHERE login='$login' AND password='$password'";

Possible SQL injection exploit:

While mysql_real_escape_string() is useful in preventing most SQL injection attacks, there are certain scenarios where it can be bypassed. One such scenario is when multibyte character sets are used, such as UTF-8.

Example:

$login = mysql_real_escape_string(GetFromPost('login'));
$password = mysql_real_escape_string(GetFromPost('password'));

$sql = "SELECT * FROM table WHERE login='$login' AND password='$password' COLLATE utf8_general_ci";

In this example, the COLLATE utf8_general_ci clause is added to the SQL query. This changes the character set and comparison rules for the query, which can defeat the escaping mechanism of mysql_real_escape_string().

How to prevent SQL injection:

While mysql_real_escape_string() is a good first line of defense against SQL injection, it is not foolproof. Here are some additional measures you can take to further enhance the security of your application:

    • Use prepared statements: Prepared statements, also known as parameterized queries, separate the SQL code from the user input. The input is treated as data, not as part of the SQL code. This ensures that even if an attacker tries to inject malicious code, it will be treated as data and not executed as SQL code. Here's an example of using prepared statements in PHP:
$stmt = $mysqli->prepare("SELECT * FROM table WHERE login=? AND password=?");
$stmt->bind_param("ss", $login, $password);
$stmt->execute();
  • Validate and sanitize user input: Before using user input in an SQL query, make sure to validate and sanitize it. This can involve checking for expected data types, length restrictions, and using functions like filter_var() or htmlspecialchars() to sanitize the input.
  • Limit user privileges: When connecting to a database, use a user account with minimal privileges. This reduces the potential damage that can be caused by a successful SQL injection attack.

Conclusion:

While mysql_real_escape_string() can provide a level of protection against SQL injection, it is not infallible. It is important to understand its limitations and use additional security measures to fully protect your application against SQL injection attacks.

Related Questions