Is “mysqli_real_escape_string” enough to avoid SQL

问题:

This is my code:

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

Could someone tell me if it is secure or if it is vulnerable to the SQL Injection attack or other SQL attacks ?

回答1:

Could someone tell me if it is secure or if it is vulnerable to the SQL Injection attack or other SQL attacks ?

As uri2x says, see SQL injection that gets around mysql_real_escape_string().

The best way to prevent SQL injection is to use prepared statements. They separate the data (your parameters) from the instructions (the SQL query string) and doesn't leave any room for the data to contaminate the structure of your query. Prepared statements solve one of the fundamental problems of application security.

For situation where you cannot use prepared statements (e.g. LIMIT), using a very strict whitelist for each specific purpose is the only way to guarantee security.

// This is a string literal whitelist
switch ($sortby) {
    case 'column_b':
    case 'col_c':
        // If it literally matches here, it's safe to use
        break;
    default:
        $sortby = 'rowid';
}

// Only numeric characters will pass through this part of the code thanks to type casting
$start = (int) $start;
$howmany = (int) $howmany;
if ($start < 0) {
    $start = 0;
}
if ($howmany < 1) {
    $howmany = 1;
}

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

I posit that the above code is immune to SQL injection, even in obscure edge cases. If you're using MySQL, make sure you turn emulated prepares off.

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