Is `mysqli_real_escape_string` Enough to Prevent SQL Injection?

Is mysqli_real_escape_string Sufficient to Mitigate SQL Injection and Attacks?

Despite its widespread use, mysqli_real_escape_string is not an impenetrable defense against SQL injection. As illustrated in the example provided:

$email = mysqli_real_escape_string($db_con, $_POST['email']);
$psw = mysqli_real_escape_string($db_con, $_POST['psw']);

$query = "INSERT INTO `users` (`email`, `psw`) VALUES ('$email', '$psw')";

This code attempts to safeguard against malicious input by escaping single quotes using mysqli_real_escape_string. However, this approach remains vulnerable to SQL injection, as demonstrated by this attack:

myEmail = 'user@example.com' OR 0 = 1

In this instance, the injection exploits the fact that mysqli_real_escape_string only escapes single quotes. By injecting the string myEmail = 'user@example.com' OR 0 = 1 into the email field, the attacker can bypass authentication and gain unauthorized access.

To effectively prevent SQL injection, consider utilizing prepared statements or parameterized queries. These mechanisms rigorously separate data from instructions, eliminating the possibility of input contamination.

Prepared statements execute inquiries with user-supplied parameters. These values are safely inserted into the query without altering its structure. For instance:

$stmt = $mysqli->prepare("INSERT INTO `users` (`email`, `psw`) VALUES (?, ?)");
$stmt->bind_param("ss", $email, $psw);

In situations where prepared statements are not feasible, implement a strict whitelist to restrict permissible input. This ensures that only safe values are processed.

In conclusion, while mysqli_real_escape_string provides some protection against SQL injection, it is not foolproof. Prepared statements or parameterized queries along with whitelisting offer more robust defense mechanisms against these attacks.

The above is the detailed content of Is `mysqli_real_escape_string` Enough to Prevent SQL Injection?. For more information, please follow other related articles on the PHP Chinese website!