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.

Lampyrid

Are you sure there's an active connection? How did you test? Also, turn on error reporting if you haven't already. – Rennet

How do you know there's a connection? I would also suggest to avoid mysql_* entirely and use PDO, or at least some DB wrapper like MDB2 – Padlock

I know i'm connected to the database because of php not shown. without any sort of text cleaning, i can edit items in the database. (thats what needs to be done in this case, but i'm looking for general input sanitization) and, yes, i know the unsanitized value is filled. – Lampyrid

Please do this at the very beginning: error_reporting(~0); ini_set('display_errors', 1); - and then actually take care you don't see any warnings and notices any longer. If you still have the problem let us know. – Jolinejoliotcurie

i put those 2 lines at the very beginning of the php element, and nothing has changed. no errors. – Lampyrid

$field = $db_connection->real_escape_string($_GET['value']); – Kine

I hope you are using this function on purpose, not for whatever "security and sanitization" for which it is almost useless – Versus

This question is off-topic because they will only allow us to close typo/syntax related questions as such – Alesha

This question appears to be off-topic because it was solved after fixing a minor typo – Nagpur

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...

Tandratandy

lol, its always stupid things like this that get me. i'm going to be working on prepared statements next... – Lampyrid

@Panique yes, because you shouldn't mix mysql_ with mysqli. – Rostellum

@Panique Note that mysql_real_escape_string returns false on error (like when there is no mysql database connection...), which is the equivalent of an empty string and that is why the OP ended up with an empty field. – Tandratandy

@Panique I get the "empty string" with both mysqli_real_escape_string() and mysql_real_escape_string() PS: I've been using a "mimic" function for a long time that solves this problem but I'm here looking for a better solution ;-) – Goodloe

@PJBrunet Don't use either function or "mimic" functions, use prepared statements without any of them (except for your regular data validation of course). – Tandratandy

@Tandratandy You're entitled to your opinion. But why is your answer mysqli_real_escape_string() if you're telling me not to use mysqli_real_escape_string() ? Maybe that's good advice but you haven't posted a "prepared statements" solution. – Goodloe

@PJBrunet My answer refers to the problem the OP had. I would recommend prepared statements (so you don't need to worry about escaping any more), but this specific problem was caused by using the wrong function. There is nothing wrong with using mysqli_real_escape_string as long as you keep the warning on the manual page in mind. By the way, I did not even see your answer before but now that I have, I don't see how it answers the question at all, the OP is already using mysqli. That's probably the reason for the downvote... – Tandratandy

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);     

Reindeer

AHA! i get this as the output: Warning: mysql_real_escape_string(): Access denied for user 'www-data'@'localhost' (using password: NO) in /cslab/home/paw5k/public_html/cainedb/edit_execute_update_gn_query.php on line 70 Warning: mysql_real_escape_string(): A link to the server could not be established in /cslab/home/paw5k/public_html/cainedb/edit_execute_update_gn_query.php on line 70 XXseries change sucessful? – Lampyrid

Lol .. i Guess as much ... you need to connect to database using mysql_connect or do you want a replacement ??? – Reindeer

Just saw your updated code .. what you need is mysqli_real_escape_string as jeroen stated – Reindeer

i wish i could mark an answer as helpful even if it was strictly speaking "correct." you have been very helpful. hats of to you sir or madam. – Lampyrid

OMG, "partial replacement". Someone have no clue. – Versus

lol @Your Common Sense mysql_real_escape_string !== htmlspecialchars – Reindeer

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);

Violate

The OP is using the object oriented style which only takes one parameter $connection->real_escape_string($field);. See this example from the PHP documentation. – Mammilla

Thanks!! This is the only answer that helped me. – Lilith

Yup, this was it for me. – Reductase

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.

Goodloe

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.

Ammadas

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

Eellike