11

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.

Rubens
  • 14,478
  • 11
  • 63
  • 92
Drake
  • 433
  • 2
  • 7
  • 17
  • 2
    Are you **sure** there's an active connection? How did you test? Also, turn on error reporting if you haven't already. – Brad Apr 26 '12 at 18:37
  • 4
    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 – Explosion Pills Apr 26 '12 at 18:37
  • 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. – Drake Apr 26 '12 at 18:40
  • 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. – hakre Apr 26 '12 at 18:51
  • i put those 2 lines at the very beginning of the php element, and nothing has changed. no errors. – Drake Apr 26 '12 at 20:04
  • `$field = $db_connection->real_escape_string($_GET['value']);` – craniumonempty Apr 26 '12 at 20:33
  • I hope you are using this function on purpose, not for whatever "security and sanitization" for which it is almost useless – Your Common Sense Apr 27 '12 at 10:09
  • This question is off-topic because they will only allow us to close typo/syntax related questions as such – random Jul 08 '13 at 23:01
  • This question appears to be off-topic because it was solved after fixing a minor typo – jcsanyi Jul 09 '13 at 01:03

6 Answers6

18

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

jeroen
  • 91,079
  • 21
  • 114
  • 132
  • 1
    lol, its always stupid things like this that get me. i'm going to be working on prepared statements next... – Drake Apr 26 '12 at 20:34
  • 2
    @Panique yes, because you shouldn't mix mysql_ with mysqli. – Ja͢ck Apr 23 '13 at 15:36
  • 3
    @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. – jeroen Apr 23 '13 at 15:50
  • @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 ;-) – PJ Brunet Jul 08 '13 at 19:55
  • 1
    @PJBrunet Don't use either function or "mimic" functions, use prepared statements without any of them (except for your regular data validation of course). – jeroen Jul 08 '13 at 20:55
  • @jeroen 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. – PJ Brunet Jul 08 '13 at 22:03
  • 1
    @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... – jeroen Jul 08 '13 at 22:11
8

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);
Baba
  • 94,024
  • 28
  • 166
  • 217
  • 1
    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? – Drake Apr 26 '12 at 20:05
  • Lol .. i Guess as much ... you need to connect to database using `mysql_connect` or do you want a replacement ??? – Baba Apr 26 '12 at 20:19
  • Just saw your updated code .. what you need is `mysqli_real_escape_string` as jeroen stated – Baba Apr 26 '12 at 20:40
  • 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. – Drake Apr 26 '12 at 20:48
  • OMG, "partial replacement". Someone have no clue. – Your Common Sense Apr 27 '12 at 09:25
  • lol @Your Common Sense `mysql_real_escape_string !== htmlspecialchars` – Baba Apr 27 '12 at 09:36
7

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);
mumush
  • 650
  • 6
  • 14
  • The OP is using the object oriented style which only takes one parameter `$connection->real_escape_string($field);`. See [this](http://php.net/manual/en/mysqli.real-escape-string.php#example-1679) example from the PHP documentation. – War10ck Dec 05 '13 at 20:58
  • Thanks!! This is the only answer that helped me. – Jake Feb 04 '20 at 16:39
  • Yup, this was it for me. – James M. Lay Jan 04 '23 at 04:14
2

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

PJ Brunet
  • 3,615
  • 40
  • 37
0

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.

Shadaksharayya H A
  • 119
  • 5
0

@ 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

scylla
  • 124
  • 9