How to change from mysql_real_escape_string to mysqli_real_escape_string();?

Question

I am creating a simple php login page based on in this video. However, after doing exactly as the video describes, I learned that my PHP version has updated and it is no longer possible to use the command mysql_connect.

<?php
// Get values passe from form in login.php file
$username = $_POST['user'];
$password = $_POST['pass'];

//to prevent mysql injection
$username = stripcslashes($username);
$password = stripcslashes($password);
$username = mysql_real_escape_string($username);
$password = mysql_real_escape_string($password);

//connect to the server and select database
mysql_connect("localhost", "root", "");
mysql_select_db("projeto_rc");

//Query for database user
$result = mysql_query("SELECT * FROM utilizadores WHERE username = '$username' and password = '$password'")
        or die("Failed to query database ".mysql_error());

$row = mysql_fetch_array($result);
if ($row['username'] == $username && $row ['password'] == $password){
echo "Login success!!! Welcome ".$row['username'];
}
else {
    echo "Failed to login";
}

Based on this code, how can I change it to support the minimum version of PHP 7? I know I should start by changing from mysql_real_escape_string for mysqli_real_escape_string as indicated by this error:

Fatal error: Uncaught Error: Call to Undefined Function mysql_real_escape_string() in C: xampp htdocs login process.php:9 Stack trace: #0 {main} thrown in C: xampp htdocs login process.php on line 9

However, you would need to use 2 parameters between parentheses and just use one. What are some of the possible solutions?

Answer 1

The mysql_real_escape_string no longer works in PHP 7.

You need to go back to PHP 5 and also pass your "connection" as parameter.

Example with mysql_real_escape_string:

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');

$item = "Zak's and Derick's Laptop";
$escaped_item = mysql_real_escape_string($item, $link);

In your case:

$con = mysql_connect("localhost", "root", "");

$username = mysql_real_escape_string($username, $con);
$password = mysql_real_escape_string($password, $con);

---

Or, in PHP 7, using mysqli_real_escape_string (attention, is reversed):

$link = mysqli_connect("localhost", "user", "password", "db");

/* checa a conexão */
if (mysqli_connect_errno()) {
    printf("Erro : %s\n", mysqli_connect_error());
    exit();
}

$var = mysqli_real_escape_string($link, $var);

---

Useful:

Documentation - mysql_real_escape_string

Documentation - mysqli_real_escape_string

Answer 2

As of PHP 5.5, the Mysql library is considered obsolete, but still works. However, it is recommended not to use it anymore as it was removed from PHP in version 7.

What are some of the possible solutions?

• One of the possible solutions is to use Prepared Statments
  • The prepared statments already works as a set of filters that work and solve well the problem mysql Injection.

What are Prepared statements?

They are nothing more than "pre-prepared" consultations... The difference is that instead of the variables you place a placeholder (placeholder) and at the time of the query tells the order of the variables to be replaced.

It’s easier to explain with an example!

// a interrogação vai no lugar da variável
$query = "SELECT * FROM tabela WHERE username = ?";

// para fazer com vários parametros é a mesma coisa
$query = "SELECT * FROM tabela WHERE username = ? AND password = ?";

Then, just let us know what is going in place of the respective ː?' and the consultation will be protected!

With prepared statements, the query is divided into two parts, the sql command and the markings(?) that will be replaced by the values, the first is executed already the second even containing a valid sql statement will be treated as plain text.

Below prepared code for your case.

if (isset($_POST['user'], $_POST['pass'])) {
    // Obtém valores do formulário do arquivo login.php
    $username = $_POST['user'];
    $password= $_POST['pass'];
    //verifica se alguma variavel é uma variável vazia
    if (empty($username) || empty($password)) {
        echo "Todos os campos são obrigatórios!";
    } else {
        //conexão 
        $pdo = new PDO('mysql:host=localhost;dbname=nome+_DB', 'USUARIO', 'SENHA');
        $query = $pdo->prepare("SELECT * FROM utilizadores WHERE username = ? AND password = ?");
        $query->bindValue(1, $username);
        $query->bindValue(2, $password);

        $query->execute();

        $num = $query->rowCount();
        if ($num == 1) {
            echo "Login success!!! Welcome ".$row['username'];
            exit();

        } else {
            echo "Failed to login";
        }
    }

}

In PHP, the Mysqli extension also supports prepared statements, but it is recommended to always use the PDO as it facilitates migration to other banks as well as providing a concise API between them.

PDO is a database abstraction layer. It is a generic interface for several Dbms, that is, your code written with PDO will work with Mysql, Postgresql, Sqlite and several other Dbms.