Is `mysqli_real_escape_string` Sufficient to Prevent SQL Injection?

Is mysqli_real_escape_string Enough to Prevent SQL Injection?

Question:

In the provided PHP code, is the use of mysqli_real_escape_string sufficient to prevent SQL injection attacks?

  $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."')";

Answer:

No, relying solely on mysqli_real_escape_string is not enough to prevent SQL injection and other SQL attacks.

Prepared statements provide a more robust solution for preventing SQL injection. They separate data and instructions, ensuring that user-provided input does not interfere with the structure of the query.

For situations where prepared statements cannot be used, implementing a strict whitelist for specific purposes can offer some protection. This involves defining a predetermined list of acceptable values for each parameter to prevent malicious input.

Example using a whitelist and type casting:

switch ($sortby) {
    case 'column_b':
    case 'col_c':
        // Safe to use
        break;
    default:
        $sortby = 'rowid';
}

$start = (int) $start;
$howmany = (int) $howmany;
if ($start < 0) {
    $start = 0;
}
if ($howmany < 1) {
    $howmany = 1;
}

// Execute the query using prepared statements
$stmt = $db->prepare(
    "SELECT * FROM table WHERE col = ? ORDER BY {$sortby} ASC LIMIT {$start}, {$howmany}"
);
$stmt->execute(['value']);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

It's essential to ensure that prepared statements are used with emulated prepares turned off, particularly when using MySQL.

$db->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);

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