PHP Manual
Prev |  | Next

# 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])

unescaped_string

  The string to escape

link_identifier (optional)

  The mysql connection resource

This function will escape 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 you wish to insert binary data you must use this function.

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

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

You must always (with few exceptions) use this function to make your data safe before sending a query to MySQL. If you have magic_quotes_gpc enabled, and you are working with data from user input, you must first stripslashes() your data. If your data are form other sources and you have magic_quotes_runtime enabled, you also have to stripslashes() your data. If you don't do so, you leave yourself open to SQL Injection Attacks. Here's an example:

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 1=1";
// 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 1=1

This would allow anyone to log in without a valid password! Using mysql_real_escape_string() around each variable prevents this.

`<?php
/**
* Quote a variable to make it safe
*/
function quote_smart($value)
{
// Stripslashes if we need to
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
// Quote it if it's not an integer
if (!is_int($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 Injection attacks will no longer work.

> 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, and the magic_quotes_runtime directive.

Prev | Home | Next
mysql_query | Up | mysql_result