Is “mysqli_real_escape_string” enough to avoid SQL injection or other SQL attacks?

by

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

No. 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);

More Related Contents:

  1. How can I prevent SQL injection in PHP?
  2. SQL injection that gets around mysql_real_escape_string()
  3. Reference: What is a perfect code sample using the MySQL extension? [closed]
  4. SQL injections in ADOdb and general website security
  5. Why is using a mysql prepared statement more secure than using the common escape functions?
  6. how safe are PDO prepared statements
  7. How Secure Is This Login System? (Using Cookies In PHP)
  8. Are PDO prepared statements sufficient to prevent SQL injection?
  9. How to create a secure mysql prepared statement in php?
  10. Do htmlspecialchars and mysql_real_escape_string keep my PHP code safe from injection?
  11. When should I use prepared statements?
  12. How do I use password hashing with PDO to make my code more secure? [closed]
  13. Examples of SQL Injections through addslashes()?
  14. What does it mean to escape a string?
  15. PHP MySQLI Prevent SQL Injection [duplicate]
  16. Is mysql_real_escape_string() broken?
  17. Are mysql_real_escape_string() and mysql_escape_string() sufficient for app security?
  18. How to best store user information and user login and password
  19. Does mysql_real_escape_string() FULLY protect against SQL injection?
  20. Shortcomings of mysql_real_escape_string?
  21. Do I have to guard against SQL injection if I used a dropdown?
  22. “slash before every quote” problem [duplicate]
  23. Hiding true database object ID in url’s
  24. MySQL Prepared Statements
  25. a better approach than storing mysql password in plain text in config file?
  26. Is htmlspecialchars enough to prevent an SQL injection on a variable enclosed in single quotes?
  27. What does mysql_real_escape_string() do that addslashes() doesn’t?
  28. Is mysql_real_escape_string enough to Anti SQL Injection?
  29. Improve password hashing with a random salt
  30. What is the PDO equivalent of function mysql_real_escape_string?

Leave a Comment