SQL Injection and mysql_real_escape_string() in PHP

By the end of this page, you will understand what mysql_real_escape_string() actually protects against, why it is not a complete SQL injection defense, and why prepared statements are the modern safe solution in PHP. You will also see the conditions under which the shown query may appear safe, and the important edge cases that can still make this approach dangerous.

mysql_real_escape_string() is an escaping function. Its job is to add backslashes before characters that would otherwise break out of a quoted SQL string, such as single quotes.

In the shown code:

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

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

both values are placed inside single quotes, so escaping helps prevent a user from prematurely ending the string and injecting SQL.

That means in this exact narrow case, if all of the following are true, classic string-based SQL injection is usually blocked:

• the values are really treated as strings
• the connection character set is set correctly
• mysql_real_escape_string() is called on the same active MySQL connection
• no other query parts are built from untrusted input

However, developers still say this approach is dangerous because escaping is fragile:

• It depends on the database connection and character set being correct.
• It only protects string values, not SQL identifiers like table names, column names, ORDER BY, or keywords.
• It is easy to forget escaping in one place.
• The old mysql_* extension is deprecated and removed in modern PHP.
• It encourages manual query construction, which is harder to audit.

Prepared statements solve the problem differently. Instead of trying to escape dangerous characters, they send the SQL structure separately from the data. The database then treats user input only as data, not as executable SQL.

So the key idea is:

• Escaping can be sufficient in limited cases
• Prepared statements are the recommended general solution because they are safer and more reliable

Think of SQL as a form with two parts:

• the fixed template: SELECT * FROM table WHERE login = ? AND password = ?
• the user-provided values: alice, secret123

mysql_real_escape_string() is like trying to wrap dangerous characters in bubble wrap before putting them into the form. That can work, but only if you always wrap them correctly and the form expects exactly that kind of wrapped value.

Prepared statements are better: they keep the template and the user data in separate compartments. The data never gets a chance to become part of the SQL instructions.

So escaping is careful manual protection, while prepared statements are built-in structural protection.

Escaping with mysql_real_escape_string()

Older PHP code often looked like this:

$login = mysql_real_escape_string($_POST['login']);
$password = mysql_real_escape_string($_POST['password']);

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

If the user enters:

aaa' OR 1=1 --

it becomes something like:

aaa\' OR 1=1 --

So the quote is escaped, and the payload stays inside the string value instead of becoming SQL syntax.

Why prepared statements are better

Using mysqli:

$stmt = $mysqli->prepare("SELECT * FROM users WHERE login = ? AND password = ?");
$stmt->bind_param("ss", $_POST['login'], $_POST['password']);
$stmt->execute();
$result = $stmt->get_result();

Using PDO:

$stmt = $pdo->prepare("SELECT * FROM users WHERE login = :login AND password = :password");
$stmt->execute([
    'login' => $_POST['login'],
    'password' => $_POST['password']
]);

In both cases:

• the SQL structure is fixed
• the user input is sent separately
• quotes inside input do not change the query logic

Important note about passwords

Real applications should not store plain-text passwords or compare them directly in SQL. Instead:

• store password hashes with password_hash()
• verify with password_verify()

Example:

$stmt = $pdo->prepare("SELECT id, password_hash FROM users WHERE login = :login");
$stmt->execute(['login' => $_POST['login']]);
$user = $stmt->fetch();

if ($user && password_verify($_POST['password'], $user['password_hash'])) {
    echo "Logged in";
} else {
    echo "Invalid credentials";
}

Consider this input:

login: aaa' OR 1=1 --
password: anything

And this PHP code:

$login = mysql_real_escape_string($_POST['login']);
$password = mysql_real_escape_string($_POST['password']);

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

Step 1: Read the raw input

$_POST['login'] = "aaa' OR 1=1 --";

Step 2: Escape special characters

mysql_real_escape_string() escapes the single quote:

$login = "aaa\' OR 1=1 --";

Step 3: Build the SQL string

The final query becomes:

SELECT * FROM users WHERE login='aaa\' OR 1=1 --' AND password='anything'

Conceptually, the injected quote is no longer closing the string in the intended way. The payload stays part of the string value.

Step 4: MySQL interprets it as data, not SQL logic

Instead of OR 1=1 changing the query condition, it is treated as text inside the login value.

Why this still does not make the pattern ideal

This worked because:

• the input was inserted into quoted string positions
• the escaping function matched the connection settings
• there was no unescaped user input elsewhere in the query

If any of those assumptions fail, the protection can fail too. That is why developers prefer parameterized queries.

Login forms

Usernames, emails, and passwords often come from forms. These values must be treated as data, not SQL code.

Search filters

Applications often search by name, category, or status:

SELECT * FROM products WHERE name = ?

Prepared statements protect these values safely.

API endpoints

An API may accept query parameters like userId, email, or status. Any value coming from requests must be separated from SQL syntax.

Admin dashboards

Filtering users, orders, logs, or reports often involves dynamic input. This is a common place where manual query building becomes risky.

Data import tools

CSV imports, form submissions, and synchronization scripts often pass external data into the database. Input may look harmless but still contain dangerous characters.

In real projects, developers usually avoid manual SQL string concatenation for user input.

Common patterns

Validation before querying

Validate format first:

$login = trim($_POST['login']);
if ($login === '') {
    throw new Exception('Login is required');
}

Prepared statements for data values

$stmt = $pdo->prepare("SELECT * FROM users WHERE login = :login");
$stmt->execute(['login' => $login]);

Allow-lists for identifiers

Prepared statements do not work for table names or column names. For those, developers use allow-lists:

$allowedSort = ['name', 'created_at'];
$sort = in_array($_GET['sort'], $allowedSort, true) ? $_GET['sort'] : 'name';

$sql = "SELECT * FROM users ORDER BY $sort";

Early rejection of invalid input

if (!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
    throw new Exception('Invalid email');
}

Password verification outside SQL

Developers usually fetch the user by login and then verify the password hash in PHP:

$stmt = $pdo->prepare("SELECT id, password_hash FROM users WHERE login = :login");
$stmt->execute(['login' => $_POST['login']]);

This keeps authentication logic clearer and more secure.

1. Assuming escaping is a universal defense

Broken idea:

$orderBy = mysql_real_escape_string($_GET['sort']);
$sql = "SELECT * FROM users ORDER BY $orderBy";

Why it is wrong:

• ORDER BY expects an identifier, not a quoted string value
• escaping string characters does not make arbitrary SQL fragments safe

Better:

$allowed = ['name', 'email', 'created_at'];
$orderBy = in_array($_GET['sort'], $allowed, true) ? $_GET['sort'] : 'name';
$sql = "SELECT * FROM users ORDER BY $orderBy";

2. Forgetting to set the correct character set

mysql_real_escape_string() depends on the connection character set. If it is wrong, escaping can become unreliable in some edge cases.

Better modern approach:

• use PDO or MySQLi
• set charset explicitly
• use prepared statements

3. Mixing escaped and unescaped input

Broken code:

$login = mysql_real_escape_string($_POST['login']);
$sql = "SELECT * FROM users WHERE login='$login' AND status='$_GET[status]'";

Even if one value is escaped, another untrusted value can still make the query unsafe.

4. Using old mysql_* functions

The old extension is obsolete and removed from modern PHP. New code should use:

• mysqli
• PDO

5. Storing plain-text passwords

Broken pattern:

SELECT * FROM users WHERE login='$login' AND password='$password'

Why it is bad:

• passwords should not be stored in plain text
• password checks should use hashed values

Better:

$passwordHash = password_hash($password, PASSWORD_DEFAULT);

and later:

password_verify($password, $storedHash)

Escaping vs prepared statements

• Escaping says: "Make this text safe enough to insert into SQL."
• Prepared statements say: "Do not insert this text into SQL structure at all."

Old mysql_* API vs modern APIs

Quoted values vs SQL identifiers

Quick rules

• mysql_real_escape_string() only helps when inserting data into SQL string literals.
• It is not a general solution for all dynamic SQL.
• It depends on the active MySQL connection and character set.
• The old mysql_* extension should not be used in new code.
• Prefer prepared statements with mysqli or PDO.

Safer modern pattern

$stmt = $pdo->prepare("SELECT * FROM users WHERE login = :login");
$stmt->execute(['login' => $login]);

Good habits

• Validate input format
• Use prepared statements for values
• Use allow-lists for column names and table names
• Hash passwords with password_hash()
• Verify passwords with password_verify()

Red flags

$sql = "SELECT * FROM users WHERE name='" . $_POST['name'] . "'";

$sql = "SELECT * FROM users ORDER BY " . $_GET['sort'];

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

Key takeaway

For the exact example in the question, classic injection is usually blocked if everything is configured correctly. But this is still not the recommended practice. Use prepared statements instead.

Is mysql_real_escape_string() enough to prevent SQL injection?

Not as a general rule. It may block many classic injections in quoted string values, but it is fragile and easy to misuse.

Is the exact query in the question injectable?

In that exact form, with proper charset and correct usage, classic string-based injection is generally prevented. The bigger issue is that the approach is outdated and not reliable as a general pattern.

Why do people still call it dangerous?

Because developers often apply escaping inconsistently, use it in the wrong places, or forget charset issues. Prepared statements are much safer.

Can prepared statements protect table names or column names?

No. Placeholders are for data values, not SQL identifiers. Use an allow-list for identifiers.

Should passwords be checked directly in SQL like in the example?

No. Store a password hash and verify it in PHP with password_verify().

What should I use instead of mysql_* functions?

Use mysqli or PDO.

Does input validation replace prepared statements?

No. Validation is helpful, but it is not a substitute for parameterized queries.

• Prepared statements — The modern standard for separating SQL code from user data.
• PDO — A common PHP database API that supports parameterized queries.
• MySQLi — Another modern PHP API for MySQL with prepared statement support.
• Input validation — Helps ensure data has the expected format before processing.
• Output escaping — Different from SQL escaping; used when rendering data in HTML or other outputs.
• Password hashing — Essential for secure login systems instead of storing plain-text passwords.
• Allow-list validation — Important when dynamic SQL includes identifiers like column names.
• Character sets and encoding — Relevant because escaping behavior depends on the database connection charset.