mysql_real_escape

add a note User Contributed Notes

nicolas
31-May-2006 04:38


Note that mysql_real_escape_string doesn't prepend backslashes to \x00, \n, \r, and and \x1a as mentionned in the documentation, but actually replaces the character with a MySQL acceptable representation for queries (e.g. \n is replaced with the '\n' litteral). (\, ', and " are escaped as documented) This doesn't change how you should use this function, but I think it's good to know.

Aidan Kehoe <php-manual at parhasard dot net>
20-Apr-2006 05:52


eric256 at gmail dot com: the PEAR DB API provides exactly that. See http://pear.php.net/manual/en/package.database.db.intro-query.php

eric256 at gmail dot com
09-Apr-2006 11:27


Hey,

Since SQL injection is such a fear it is beyond me as to why this hasn't been included yet.   I set this function up to take a query with ?'s in it and replace those with the correctly quoted values.  I don't do the "smart quoteing" i quote it in the SQL because i like to see the quotes there.



<?php

function db_query($query) {

  $args  = func_get_args();

  $query = array_shift($args);

  $query = str_replace("?", "%s", $query);

  $args  = array_map('mysql_real_escape_string', $args);

  array_unshift($args,$query);

  $query = call_user_func_array('sprintf',$args);

  $result = mysql_query($query) or die('Query failed: ' . mysql_error());

  return $result;

}

?>



call it like



<?php

$results = db_query("SELECT * FROM users WHERE username='?' AND password = PASSWORD('?');", $username,$password);

?>

This automagicaly quotes them and executes the query for you and also dies if there is an error in the query.

php at iain dot nl
14-Mar-2006 10:45


@ keith dot lawrence at jpmh dot co dot uk:



But an empty string is not per defenition NULL... usually you look for NULLable columns in your database on special occasions, so I think it's best to keep that apart...

keith dot lawrence at jpmh dot co dot uk
13-Mar-2006 07:23


Here's my version, this replaces empty strings with NULLs instead of using a quoted empty string which was causing a sql error when adding empty values to an integer column which allows nulls. Your mileage may vary.



<?php

function quote_smart($value)    {

    if (get_magic_quotes_gpc()) $value = stripslashes($value);

    if($value == '') $value = 'NULL';

    else if (!is_numeric($value) || $value[0] == '0') $value = "'" . mysql_real_escape_string($value) . "'"; //Quote if not integer

    return $value;

}

?>

php at iain dot nl
12-Mar-2006 05:59


It's quite easy to stop the problem of 0666 returning as 666 in the example of quote_smart. Look at this:



<?php

// Quote variable to make safe

function quote_smart($value) {

   // Stripslashes

   if (get_magic_quotes_gpc()) {

       $value = stripslashes($value);

   }

   // Quote if not integer

   if (!is_numeric($value) || $value[0] == '0') {

       $value = "'" . mysql_real_escape_string($value) . "'";

   }

   return $value;

}

?>



It now checks if there is a leading zero, and then it should be treated als a text, instead as a numerical value. Just an easy workaround.

huuanito at hotmail dot com
28-Feb-2006 03:36


just tried on php 4.4.2  the quote_smart example works just fine with 0666 it comes back as 0666 and 00 comes back as 00.  a single 0 gets lost however but I don't see that as a problem, unless of course you want that as your password. quote_smart works for me.

cedric over blog com
07-Feb-2006 12:20


as already said : 

the example is wrong !

If your password is 0666 quote_smart will return : 666

23-Jan-2006 11:03


Here's why pmioni is wrong:



http://shiflett.org/archive/184

matthew at exanimo dot com
13-Aug-2005 10:34


Note that to use quote_smart(), you have to connect to the database via mysql_connect().  Otherwise, mysql_real_escape_string() will have no way of knowing what database you want to use.



If you want to use the OO approach or mysqli_connect(), you're going to have to rewrite quote_smart() to accept a MySQL connection, which it would then pass to mysql_real_escape_string().



This is a pretty lackluster solution, though, for anyone concerned with abstraction.

david
07-Apr-2005 11:25


The problem with this function returning an empty string instead of an escaped string seems to be related to the mysql lib versions installed on the server. On 3 servers, each with php 4.3.10, I had no problems on 2 of them, but got the empty string on the 3rd. The 2 that worked had versions 4.1.x and 4.0.x of mysql. The 3rd that did not work had 3.23.x



The failure also only occurred when I did not have a mysql connection set up before running the escape function. If I did DB::connect (using the PEAR DB object) before running the function, it worked, even if I didn't pass in the connection id. If I ran the function before DB::connect, then I got an empty string.



So, if you are getting an empty string, check your mysql lib version and check where you are connecting to the db in relation to calling the escape function in your code.

S. W.
11-Feb-2005 02:48


For a "best practices" approach to handling user input, one should always include enforcement of input length limitations.  This will avoid potential attacks based on *very* large values being inserted, some of which may not be foiled just by escaping a string.  (Length limits imposed via your form may be bypassed by submitting from a page or tool created by the attacker.)  As a cursory example:



<?php

$maxNameLen = 25;

$limitedName = substr($_POST['username'],0,$maxNameLen);

$safeUsername = mysql_real_escape_string($limitedName);

?>



Note that you'll need to take care to truncate the correct (raw) value and not something that has already been processed.  Otherwise, you're exposing the potential attack data to more potential points of failure, plus valid input may grow in length with escape processing and be incorrectly truncated.

boris-pieper AT t-online DOT de
22-Jan-2005 06:36


well, smth like that



<?php



function escape_string ($string,$dbcon=false) {

   if(version_compare(phpversion(),"4.3.0")=="-1") {

     mysql_escape_string($string);

   } elseif ($dbcon) {

     mysql_real_escape_string($string,$dbcon);

   } else { return false; }

}



?>

manderson at dsrglobal dot com
16-Jan-2005 01:36


The quote_smart() function in the "Best Practice" section does not quote any value which is all numeric. This will drop all zero's from the the lefthand side of a string. While this is intended to simplify the query string when storing numeric values this has a negative effect when trying to store strings which just so happen to be all digits. For instance some zip codes have one or more zero's on the lefthand side, and an MD5 hash may contain all numeric characters and the lefthand characters can be zero's. In these cases the lefthand zero's will be dropped.



I would simply drop the is_numeric() check. There's no reason to not quote numeric values.



<?php



// bad query created using the quote_smart() function

$qs = "UPDATE tbl SET zipcode=" . quote_smart('01234');

// UPDATE tbl SET zipcode=1234



// acceptable query created after dropping the is_numeric() check

$qs = "UPDATE tbl SET number=" . quote_smart('01234');

// UPDATE tbl SET number='01234'



?>

ludvig dot ericson at gmail dot com
01-Jan-2005 10:18


A case where you do not need to escape is when you are about to compare the UI (User Input) with a database through MD5 hashes, infact if you do, the password stored in the database will not match the one in the request.

I had a living  hell trying to solve this in my earlier days, so I just wanted to enligthen any other newbies,



<?php

$try_pass=md5($_POST['u_pass']);

?>



is sufficient escaping.



Cheers

add a note

mysql_real_escape_string

说明