PHP mysql_real_escape_string returns empty string

Question

I'm trying to work a bit of security and sanitization into my databases application (for a class). to start off with, i'm trying to use mysql_real_escape_string, but whenever i use it, it always returns an empty string!

Here's the connection code:

include_once ("./connect.php");
$db_connection = new mysqli($SERVER, $USERNAME, $PASSWORD, $DATABASE);
if (mysqli_connect_errno()) {
    echo("Can't connect to MySQL Server. Error code: " . mysqli_connect_error());
    return null;
}
$field = mysql_real_escape_string($_GET['value']);
$upc = $_GET['upc'];
$type = $_GET['field_type'];
echo $field;
echo $upc;
echo $type;

When the php actually gets executed, the $upc and $type gets printed, but NOTHING for $field. Ive tried using an intermediate string, but i get the same result. I'm seriously at a loss as to what it is thats going wrong here.

Also, I've done a var_dump on $field, and it claims mysql_real_escape_string returns FALSE, which is supposed to happen when there isn't a connection(?), but there is one.

Answer 1

You are connecting using mysqli, not mysql, so you need mysqli_real_escape_string

Although you could easily use prepared statements in mysqli and then you wouldn't need either...

Answer 2

See PHP Documentation http://php.net/manual/en/function.mysql-real-escape-string.php

The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.

Which Means you need an active connection to use this function

If you are not seeing any error try

error_reporting(E_ALL);
ini_set('display_errors','On');

mysql_real_escape_string alternative

Use

  $escaped = htmlspecialchars($_GET['value'], ENT_QUOTES, "ISO-8859-1");

OR

   $escaped = filter_var($_GET['value'], FILTER_SANITIZE_STRING);     

Answer 3

You're getting any empty string because the function takes two parameters; the actual connection to the database and then the string. Use this:

$sanitizedField = mysqli_real_escape_string($connection, $field);

Answer 4

"This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used." from http://php.net/manual/en/function.mysql-real-escape-string.php

Simply adding "i" to the function name is not the solution. Notice the link identifier parameter is no longer optional http://www.php.net/manual/en/mysqli.real-escape-string.php

PS: As far as the downvotes, I stand by my answer. The function in the question is going to be removed from PHP, that's a big deal and I felt it needed to be pointed out since nobody else mentioned it. Do you disagree? The specifics of the original question are important but many people (including myself) came here looking for information on mysql_real_escape_string() and the first thing you should realize when looking for information about mysql_real_escape_string() is that it's deprecated. Sysadmins will have to upgrade PHP sooner or later and no doubt a ton of applications are still using this deprecated function.

Answer 5

We also faced same problem. mysql_real_escape_string was working on our development Linux system but not working on test bed. We were wondering why it works on our system and not on test bed even though all php rpms are of same version. After referring PHP documentation, found that it is mandatory to have active connection to MySQL before calling mysql_real_escape_string. If there is no active connection then PHP internally tries to connect to MySQL with default parameters. When PHP internally tried connecting to database on our development machine it worked but it failed on our test bed and resulted in empty response for call to mysql_real_escape_string. After adding call to mysql_connect() before mysql_real_escape_string() our problem got solved.

Below was the error when we run mysql command and this error was not seen on our development machine and command successfully connected to database server. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Conclusion is that we have to explicitly connect to mysql before calling mysql_real_escape_string. Internal attempt by php to connect to database can work on some system and fail on some other systems depending on security levels or login credentials.

Answer 6

@ jeroen seems to explain it. Although it should work without connecting to database on local server(many things work this way) but it would not on web server except you connect to database before mysql_rea..... I just tried it and it worked. Thats why he earned my vote