search for in the  
<mysql_querymysql_result>
Last updated: Thu, 19 May 2005

mysql_real_escape_string

(PHP 4 >= 4.3.0, PHP 5)

mysql_real_escape_string -- Escapes special characters in a string for use in a SQL statement

Description

string mysql_real_escape_string ( string unescaped_string [, resource link_identifier] )

Escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query(). If binary data is to be inserted, this function must be used.

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.

Parameters

unescaped_string

The string that is to be escaped.

link_identifier

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 by chance no connection is found or established, an E_WARNING level warning is generated.

Return Values

Returns the escaped string, or FALSE on error.

Examples

Example 1. Simple mysql_real_escape_string() example

<?php
// Connect
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
   OR die(mysql_error());

// Query
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
           mysql_real_escape_string($user),
           mysql_real_escape_string($password));
?>

Example 2. An example SQL Injection Attack

<?php
// Query database to check if there are any matching users
$query = "SELECT * FROM users WHERE user='{$_POST['username']}' AND password='{$_POST['password']}'";
mysql_query($query);

// We didn't check $_POST['password'], it could be anything the user wanted! For example:
$_POST['username'] = 'aidan';
$_POST['password'] = "' OR ''='";

// This means the query sent to MySQL would be:
echo $query;
?>

The query sent to MySQL: 

SELECT * FROM users WHERE name='aidan' AND password='' OR ''=''

This would allow anyone to log in without a valid password.

Example 3. A "Best Practice" query

Using mysql_real_escape_string() around each variable prevents SQL Injection. This example demonstrates the "best practice" method for querying a database, independent of the Magic Quotes setting.

<?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 = "'" . mysql_real_escape_string($value) . "'";
   }
   return $value;
}

// Connect
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
   OR die(mysql_error());

// Make a safe query
$query = sprintf("SELECT * FROM users WHERE user=%s AND password=%s",
           quote_smart($_POST['username']),
           quote_smart($_POST['password']));

mysql_query($query);
?>

The query will now execute correctly, and SQL Injection attacks will not work.

Notes

Note: A MySQL connection is required before using mysql_real_escape_string() otherwise an error of level E_WARNING is generated, and FALSE is returned. If link_identifier isn't defined, the last MySQL connection is used.

Note: If magic_quotes_gpc is enabled, first apply stripslashes() to the data. Using this function on data which has already been escaped will escape the data twice.

Note: If this function is not used to escape data, the query is vulnerable to SQL Injection Attacks.

Note: mysql_real_escape_string() does not escape % and _. These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE.

See Also

mysql_client_encoding()
addslashes()
stripslashes()
The magic_quotes_gpc directive
The magic_quotes_runtime directive



User Contributed Notes
mysql_real_escape_string
mels at xs4all dot nl
13-May-2005 05:41
@ludvig dot ericson at gmail dot com:
I don't see why you wouldn't *especially* escape a password. It should be either *essential* or *useless* to escape a password. Depends on the way you allow NEW registrations. When you escape all NEW passwords too before putting them in the DB, you NEED to escape before calculating the md5 to compare to the md5 in the database. When you filter for any "illegal" characers (which is WAY better IMO) in any NEW passwords before storing them, you are sure you don't have to escape to get a correct match. It is preferrable to ban he chars this function would escape from any new passwords, to make password rememberability greater and overall it's just handier to work with data you are already sure of not having to escape (may sound dangerous, but if your registration script is solid, the data is, period.). It also helps users feel like "hey, there's been thought about this" (although it may annoy at first, they'll appreciate) when their password is refused. Be sure to give them a good explanation (bad rememberability, coder's laziness, etc... :))
Pilgrim
22-Apr-2005 12:15
In mysql-server
Is there record with host='localhost',user='',password='' in table mysql.user?
If not, add this record!
--------------
Use MYSQL;
INSERT INTO user VALUES ('localhost', '', '', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0);
--------------
Then this function will return not empty string!
Bye!
david
07-Apr-2005 10: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.
phpcoder at r4f dot org
29-Mar-2005 11:19
Thanks boris-pieper AT t-online DOT de, but I had a pb with "PHP Version 4.1.2".

I was forced to explicitely "return" from my function, which now becomes :

function escape_string($string,$dbcon=false) {
       if(version_compare(phpversion(),"4.3.0")== -1) {
               return(mysql_escape_string($string));
       } elseif ($dbcon) {
               return(mysql_real_escape_string($string,$dbcon));
       } else { return false; }
}
dotwho at nospan dot mac dot com
14-Feb-2005 06:18
As Loopy points out below... this function can be finiky about returning results when no dbLink resource is given.

Even though the function works without the dblink at times, I would put in the link with every call.

For me, the function was returning an empty string without the dbLink, and returned the correct string with.
S. W.
11-Feb-2005 12: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.
Loopy
01-Feb-2005 06:44
If You are using the function without the optional parameter for DB-link and get error messages, just add a new user in MySQL with blanc name and password with no access to anything (and don't forget to "flush privileges;"). This user should be there by default, but if this is not the case, You need to create it in order to allow the function to connect as nobody with no password.
Cheers
boris-pieper AT t-online DOT de
21-Jan-2005 04: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; }
}

?>
boris-pieper AT t-online DOT de
15-Jan-2005 05:03
For compatibality u could use a function like

<?php

function escape_string ($string) {
   if(version_compare(phpversion(),"4.3.0")=="-1") {
     mysql_escape_string($string);
   } else {
     mysql_real_escape_string($string);
   }
}

?>
manderson at dsrglobal dot com
15-Jan-2005 11: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 08: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

<mysql_querymysql_result>
 Last updated: Thu, 19 May 2005
Copyright © 2001-2005 The PHP Group
All rights reserved.		 This unofficial mirror is operated at: The Server Pages
Last updated: Thu May 19 17:35:34 2005 CDT