SQL Injection that Gets Around mysql_real_escape_string() Function

SQL injection is a serious security vulnerability that occurs when an attacker can manipulate SQL queries executed in a database. In an attempt to prevent SQL injection, developers often use functions like mysql_real_escape_string() in PHP to escape special characters.

However, it's crucial to understand that reliance on this function alone may not provide foolproof protection. This guide demonstrates SQL injection scenarios that bypass mysql_real_escape_string() and explores the importance of adopting additional security measures.

What is mysql_real_escape_string() Function

mysql_real_escape_string() is a PHP function designed to escape special characters in a string before inserting them in MySQL queries, ensuring safe MySQL queries are run in the database. However, it may not protect all forms of SQL injection attacks. Attackers can leverage certain techniques to evade the effects of this function and execute malicious SQL statements.

Note: This extension was removed in PHP 7.0.0. Some better alternatives to this functions are: mysqli_real_escape_string() and PDO::quote().

Syntax

// Usage of mysql_real_escape_string()

$escaped_value = mysql_real_escape_string($user_input);

This function escapes special characters, making the input safe for inclusion in SQL queries.

Bypassing mysql_real_escape_string() with SQL Injection Example

Let's look at some examples and understand how SQL injection attacks bypass the mysql_real_escape_string() function.

Classic SQL Injection Example

Consider a login scenario where the application uses mysql_real_escape_string() to sanitize user input. The attacker provides a crafted input to bypass the function.

-- Create users Table
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(50)
);

-- Insert Sample Data
INSERT INTO users (username, password) VALUES
('john_doe', 'password123'),
('admin', 'adminpassword');

PHP Code Using mysql_real_escape_string()

<?php
// Assume the usage of mysql_real_escape_string()
$_POST['username'] = "admin' OR '1'='1'; -- ";

// Login Query Using mysql_real_escape_string()
$username = mysql_real_escape_string($_POST['username']);
$password = mysql_real_escape_string($_POST['password']);
$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";
// Execute the query and process the result
?>

Output:

The provided PHP code uses deprecated mysql_real_escape_string() for input sanitization and attempts to prevent SQL injection. However, it is vulnerable.

Injected SQL Query:

SELECT * FROM users WHERE username='admin' OR '1'='1'; -- ' AND password='';

The malicious input admin' OR '1'='1'; -- can manipulate the SQL query, potentially leading to unauthorized access as it injects a true condition.

The injected query allows unauthorized access by matching any password when the injected condition is true. This query always evaluates to true, allowing unauthorized access.

Using Prepared Statements for Secure Query Execution Example

To address SQL injection vulnerabilities effectively, developers should adopt prepared statements.

This example modifies the login query to use a prepared statement.

-- Create users Table
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(50)
);

-- Insert Sample Data
INSERT INTO users (username, password) VALUES
('john_doe', 'password123'),
('admin', 'adminpassword');

PHP Code Using Prepared Statements

-- PHP Code Using Prepared Statements
<?php
// Establish MySQL Connection
$mysqli = new mysqli("localhost", "username", "password", "database");

// Login Query Using Prepared Statement
$stmt = $mysqli->prepare("SELECT * FROM users WHERE username=? AND password=?");
$stmt->bind_param("ss", $username, $password);

// Set Values Safely
$username = $_POST['username'];
$password = $_POST['password'];

// Execute the Query
$stmt->execute();
$result = $stmt->get_result();

// Process the Result
while ($row = $result->fetch_assoc()) {
    // Process the result
}
?>

Output:

This PHP code uses a prepared statement to securely retrieve user data from a MySQL database. It establishes a connection, prepares a SELECT query with placeholders, binds user inputs, executes the query, and processes the result. However, the result processing section is incomplete; it should include logic for handling successful or unsuccessful logins.

Conclusion

So, Overall, while mysql_real_escape_string() provides a basic level of protection, it is not foolproof against all SQL injection attacks. Developers must stay vigilant and adopt more robust measures, such as prepared statements, to ensure the security of their applications.

Utilizing the latest security practices and regularly updating code to address emerging threats is essential in the ongoing battle against SQL injection vulnerabilities.