mysqli_real_escape_string Prevent SQL Injection?

Question

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

So:

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

References:

Mysqli Real Escape String

Filter Input

Answer 1

There is a possibility of SQL Injection even when using the function mysqli_real_escape_string()?

Not typically, unless used strange charset. Suppose you use UTF8MB4 and always use the " (or ') in the query, this will suffice. Ideally charset also used, using the mysqli_set_charset.

Including, the Cure53, a security company claims to use addslashes is safe as long as you always use a safe charset (and by default it will be used), and always include the value on " ... " in the query.

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

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

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

Apply other filters or cleanings before of mysqli_real_escape_string will not leave Mysql Injection vulnerable, but can create other problems.

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

None. Only use functions that have been done for the desired purpose. Use FILTER_SANITIZE_STRING, for example, you can create more problems and will not solve SQL Injection. The reason is simple, this filter was not made for SQL Injection.

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

Answer 2

Of course solve friend, do the test, create a table with a login field and password, and do the following test (OBS: I am posting the example I did, I use a class for connection to the bank but the process is the same without the class what matters is the 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 searching the information in the database correctly. Theoretically as something returned from the bank the system would only take this information and automatically log in the guy ex:

<?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 process the data sent for 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
        )

)

Note that it returned all users of the bank, and as the first user is usually the system administrator at the time this Array() go through the login script to the person who placed the SQL Injection will be logged in as administrator (or with the first user of your table depends on how the programmer made his login rule).

Now if we put that darling 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>";
?>

That way the return will be VAZIO, then the person who tries to put any SQL Injection in the login and password fields will be automatically escaped and unrecognized (as long as you are not storing SCAPED STRINGS in the database ex: [ login field = Rgio/marques or password field = edu'02"/3 ] to avoid this kind of headache always use the md5 or sha1 to encrypt data/passwords saved in the database that require scape characters, so you make your system safer 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, in the internet you find several interesting functions).