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.

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", [], []);
->();
 = ->();

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  login   

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'], , ) ? [] : ;

 = ;

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:

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

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'] . ;

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.