Is mysqli_real_escape_string safe?

Question

I´m new in PHP and I´ve realised that my database connection, using a php form (with user and pass text inputs) was totally unsafe:

This was working, but was unsafe:

<?php
$link=mysqli_connect('localhost','xx','xx','xx');
$sql='  SELECT * FROM usuarios 
        WHERE username="'.$_POST['usuario'].'" 
        AND pass="'.$_POST['usuario'].'"
     ';
$rs=mysqli_query($link,$sql);
mysqli_close($link);
?>

So, I´ve read about mysqli_real_escape_string, and decided to try it out:

<?php    
$link=mysqli_connect('localhost','xx','xx','xx');
$usuario=mysqli_real_escape_string($link, $_POST["usuario"]);
$clave=mysqli_real_escape_string($link, $_POST["clave"]);
$sql='  SELECT * FROM usuarios 
        WHERE username="'.$usuario.'" 
        AND pass="'.$clave.'"
     ';
$rs=mysqli_query($link,$sql);
mysqli_close($link);
?>

Is this correct? Is this a good example of how to use mysqli_real_escape_string?

if you're using mysqli, then you shouldn't be escaping values yourself. use placeholders and let the database do all the work for you. But otherwise, yes, you're using it "correctly", inasmuch using an incorrect/obsolete coding method can be considered "correct" — Marc B, Mar 10 '14 at 15:52
Prepared/parameterized queries are the way to go. I wouldn't call this correct. — Brad, Mar 10 '14 at 15:55
FYI, having passwords in the database without encryption is about as bad as not escaping input. And I don't mean md5 -- use a proper one-way encryption with salt. — Ingo Bürk, Jul 07 '14 at 05:09
Please give me example before `mysqli_real_escape_string` and after the string which gets generated. As it casuse confusion and no example is given. — Pratik Joshi, Dec 12 '15 at 14:08

Your Common Sense · Accepted Answer · 2023-05-08T06:25:43.707

Is this correct?

Yes. This isolated handpicked example is safe. It doesn't mean, however, that mysqli_real_escape_string should be viewed as a function that's purpose is to prevent SQL injections. Because in this example it protects you only by accident. As silly example as one provided below can demonstrate that:

$id = mysqli_real_escape_string($link, $_POST["id"]);
$sql = 'SELECT * FROM usuarios WHERE id = $id';
$rs = mysqli_query($link,$sql);

Here, almost any SQL can be added to $_POST["id"] and wreck havok in your database.

Is this a good example of how to use mysqli_real_escape_string?

Not at all

This function should be abandoned in favor of using parameters in the query. This function will fail you with any query part other than a string literal. And can be even simply overlooked.

A placeholder, also called a parameter, have to be used instead, to represent the data in your query:

$sql = 'SELECT * FROM usuarios WHERE username=?';
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $_POST['usuario']);
$stmt->execute();
$rs = $stmt->get_result();

See other examples in my article on the correct use of mysqli

If ever used, this function MUST be encapsulated into another function that does both escaping AND adding quotes, just like PDO::quote() does. Only this way it will be safe.

Please give me example before `mysqli_real_escape_string` and after the string which gets generated. As it casuse confusion and no example is given. — Pratik Joshi, Dec 12 '15 at 14:08
could be `mysqli_real_escape_string` used together with a stored procedure? for example: `$code = mysqli_real_escape_string($link, filter_var($_POST["code"], FILTER_SANITIZE_STRING)); mysqli_query($link, "CALL sp_updatetable('$code')")` ? - please note: `code` isn't user input. — deblocker, Oct 22 '17 at 22:10
@deblocker anything said in the answer is applicable to stored procedures. Simply because calling a procedure is just an SQL query, like any other. — Your Common Sense, May 19 '22 at 09:18

score 5 · Answer 2 · answered Mar 10 '14 at 15:58

5

The use of mysqli() functions should only be reserved for framework developers and others who are aware of all the safety issues it can bring. For everyone else, there's PDO. It's just as easy to use as mysqli(), and far safer.

answered Mar 10 '14 at 15:58

Ermir

1,391
11
25

7

"*It's just as easy to use as mysqli(), and **far safer.***" - [citation needed] – Amal Murali Mar 10 '14 at 16:01
2

Well, the link I posted explains why PDO is safer than mysqli() and string concatenation. If you are still not convinced, there's [this](http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection) discussion that's from 2008, and still relevant today. Immunity against a large part of SQL attacks definitely qualifies as "far safer" in my opinion. – Ermir Mar 10 '14 at 16:05
7

I have read that article before and I don't see where it explains "PDO is *safer*". – Amal Murali Mar 10 '14 at 16:07
1

The article does not have to use the words "safer" to imply it. "Even if you're only going to use it once, using prepared statements will help protect you from SQL injection attacks." – Ermir Mar 10 '14 at 16:10
7

In case you didn't know that before, PDO doesn't hold the monopoly for the prepared statements. – Your Common Sense Mar 10 '14 at 16:13
1

I do like the syntax of PDO's prepared statements better, but mysqli can utilize prepared statements: http://php.net/manual/en/mysqli.prepare.php. Also, for the record, if you use PDO::query and PDO::execute, it can be just as unsafe as the old-school mysql_query if you don't sanitize (`quote()`) your input. So, there is no "immunity against" sql attacks. – Kevin Nelson Mar 11 '15 at 22:29

score 5 · Answer 3 · answered Mar 10 '14 at 15:59

5

Yes you will use it save now.

The nice thing about using mysqli is that it is Object oriented. So you can use it like this:

<?php

$mysqli = new mysqli("host", "user", "password", "database");

$usuario = $mysqli->real_escape_string($_POST["usuario"]);
$clave = $mysqli->real_escape_string($_POST["clave"]);

$sql='  SELECT * FROM usuarios 
        WHERE username="'.$usuario.'" 
        AND pass="'.$clave.'"
     ';

$mysqli->query($sql);

$mysqli->close();
?>

Or you can use PDO.

answered Mar 10 '14 at 15:59

Aartsie

143
2

2

Please dont use non prepared statements. Seriously, just dont/. Theres a couple of decades worth of exploits that abuse unicode and other edge cases to get around mysql_real_escape and real_escape . Just put it in a prepared statement and let the driver handle it. – Shayne May 19 '22 at 08:19

Is mysqli_real_escape_string safe?

3 Answers3

Not at all

Linked

Related