mysqli_real_escape_string Prevents SQL Injection?

3

According to the documentation, the function: Exits special characters in a string for use in a SQL statement, taking into account the current character set of the connection.

Then:

  • Is there a possibility of SQL Injection even when using the mysqli_real_escape_string() function?
  • If used in conjunction with filter_input when receiving data, could it improve the security level of the application?
  • What functions could be combined to make the application more secure in this scenario of SQL Injection Prevention ?
  

References:

     

Mysqli Real Escape String

     

Filter Input

    
asked by anonymous 17.11.2017 / 13:54

2 answers

2
  

Is there a possibility of SQL Injection even when using the mysqli_real_escape_string () function?

Typically not, unless you use strange charset . Assuming you use UTF8MB4 and always use " (or ' ) in the query, this will suffice. Ideally, charset is used too, using mysqli_set_charset .

Even Cure53 , a security company, says using addslashes is safe, as long as you always use a secure (and which will be used by default), and always include the value of " ... " in the query.

The reason why this is not recommended is simple: you can forget to treat a variable with mysqli_real_escape_string() or forget a simple " . Of course, only one mistake could take everything down.

  

If used in conjunction with a filter_input when receiving data, could it improve the security level of the application?

No. This may even be worse, after all you can create a new Wordpress and its myriad XSS vulnerabilities simply by "escaping" data and tricking them into the database.

Applying other filters or cleanups before from mysqli_real_escape_string will not leave you vulnerable to MySQL Injection, but may create other problems.

  

What functions could be combined to make the application more secure in this scenario of SQL Injection Prevention?

None. Only use functions that were made for the desired goal. Using FILTER_SANITIZE_STRING , for example, could create more problems and will not resolve SQL Injection. The reason is simple, this filter was not made for SQL Injection.

Use mysqli_real_escape_string or use Prepared Statement, these are the only "functions" that should be used for this purpose.

    
17.11.2017 / 17:32
1

Of course solve friend, take the test, create a table with a login field and password, and do the following test (OBS: I am posting the example I made, I use a class to connect to the bank but the process is the same without the class what matters is SQL ):

<?php

include_once 'Query.class.php';

$login = "guilherme";
$senha = "1234";

$sql = 'SELECT * FROM 'usuarios' WHERE login = "' . $login . '" AND senha = "' . $senha . '"';

echo "<pre>";
print_r(Query::Select($sql));
echo "</pre>";
?>

This code returned me a Array() with that user's data in the database:

Array
(
    [0] => Array
        (
            [id] => 2
            [email] => guilherme
            [senha] => 1234
        )

)

So it means that SQL is fetching the information in the database correctly. Theoretically as it returned something from the bank the system would only take this information and automatically log the ex guy:

<?php

... codigo imaginario anterior ...

$sql = 'SELECT * FROM 'usuarios' WHERE login = "' . $login . '" AND senha = "' . $senha . '"';
$dados = Query::Select($sql);

if($dados != ""){
    $SESSION["logado"] = $dados[0]; // Como só poderia retornar 1 conjunto de dados eu pego sempre o primeiro (não tem como existir 2 usuários iguais com senhas iguais)
}else{
    // não achou nenhum user que combina com a senha
}

?>

What happens if we do not handle the data sent to login? see the example:

<?php

include_once 'Query.class.php';

// SQL Injection
$login = '" or "1';
$senha = '" or "1';

$sql = 'SELECT * FROM 'usuarios' WHERE email = "' . $login . '" AND senha = "' . $senha . '"';

echo "<pre>";
print_r(Query::Select($sql));
echo "</pre>";
?>

The return of this will be as follows Array() :

Array
(
    [0] => Array
        (
            [id] => 1
            [login] => administrator
            [senha] => admin
        )

    [1] => Array
        (
            [id] => 2
            [login] => guilherme
            [senha] => 1234
        )

    [2] => Array
        (
            [id] => 3
            [login] => carlos
            [senha] => carlos
        )

    [3] => Array
        (
            [id] => 4
            [login] => luiz
            [senha] => luiz
        )

)

See that it has returned all users of the database, and since the first user is usually the system administrator at the time that Array() passes through the login script the person who placed SQL Injection will be logged in as administrator (or with the first user of your table depends on how the programmer made the login rule for it.)

Now if we put this dear function mysqli_real_escape_string() see the result:

<?php

include_once 'Query.class.php';

$login = '" or "1';
$senha = '" or "1';


/*
 * A função Query::AntiSqlInjection() nada mais é que isso:
 * 
 * static public function AntiSqlInjection($str) {
 *      self::AbreConexao();
 *      $str = mysqli_real_escape_string(self::$conn, $str);
 *      self::FechaConexao();
 *      return $sql;
 * }
 * 
 */
$sql = 'SELECT * FROM 'usuarios' WHERE login = "' . Query::AntiSqlInjection($login) . '" AND senha = "' . Query::AntiSqlInjection($senha) . '"';

echo "<pre>";
print_r(Query::Select($sql));
echo "</pre>";
?>

In this way the return will be VAZIO , so the person trying to put any SQL Injection in the login and password fields will be automatically escaped and not recognized (as long as you are not storing SCAPED STRINGS in the bank ex: [field login = sergio / marques or the password = edu'02 "/ 3] field to avoid this type of headache always use md5 or sha1 to encrypt bank-saved data / passwords that require scape characters, so you leave your system more secure and avoid problems with data comparison.This does not apply if you need to decrypt some data, in this case use some function that provides the encryption and decryption mode, you can find several interesting functions on the internet.)

    
17.11.2017 / 15:00