PHP
downloads | documentation | faq | getting help | mailing lists | licenses | wiki | reporting bugs | php.net sites | links | conferences | my php.net

search for in the

mysql_result> <mysql_query
Last updated: Fri, 02 Jan 2009

view this page in

mysql_real_escape_string

(PHP 4 >= 4.3.0, PHP 5)

mysql_real_escape_string — SQL 文中で用いる文字列の特殊文字をエスケープする

説明

string mysql_real_escape_string ( string $unescaped_string [, resource $link_identifier ] )

現在の接続の文字セットで unescaped_string の特殊文字をエスケープし、 mysql_query() で安全に利用できる形式に変換します。バイナリデータを挿入しようとしている場合、 必ずこの関数を利用しなければなりません。

mysql_real_escape_string() は、MySQL のライブラリ関数 mysql_real_escape_string をコールしています。 これは以下の文字について先頭にバックスラッシュを付加します。 \x00, \n, \r, \, ', " そして \x1a.

データの安全性を確保するため、MySQL へクエリを送信する場合には （わずかな例外を除いて）常にこの関数を用いなければなりません。

パラメータ

unescaped_string

エスケープされる文字列。

link_identifier

MySQL 接続。 指定されない場合、mysql_connect() により直近にオープンされたリンクが 指定されたと仮定されます。そのようなリンクがない場合、引数を指定せずに mysql_connect() がコールした時と同様にリンクを確立します。 リンクが見付からない、または、確立できない場合、 E_WARNING レベルのエラーが生成されます。

返り値

成功した場合にエスケープ後の文字列、失敗した場合に FALSE を返します。

例

例1 単純な mysql_real_escape_string() の例

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

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

例2 SQL インジェクション攻撃の例

<?php
// データベース上のユーザに一致するかどうかを調べる
$query = "SELECT * FROM users WHERE user='{$_POST['username']}' AND password='{$_POST['password']}'";
mysql_query($query);

// $_POST['password'] をチェックしなければ、このような例でユーザに望みどおりの情報を取得されてしまう
$_POST['username'] = 'aidan';
$_POST['password'] = "' OR ''='";

// MySQL に送信されたクエリは、
echo $query;
?>

MySQL に送信されたクエリは次のとおり: 

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

これでは、パスワードを知らなくても誰でもログインできてしまいます。

例3 "うまいやり方" のクエリ

それぞれの変数に mysql_real_escape_string() を適用し、 SQL インジェクションを防ぎます。この例では、 データベースにクエリを送信する場合の "うまいやり方" を示します。これは、 マジッククオート の設定に依存しません。

<?php

if (isset($_POST['product_name']) && isset($_POST['product_description']) && isset($_POST['user_id'])) {
    // 接続します

    $link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password');

    if(!is_resource($link)) {

        echo "サーバへの接続に失敗しました\n";
        // ... エラーを適切にログ出力します

    } else {
        
        // magic_quotes_gpc/magic_quotes_sybase が ON になっている場合に、その処理内容を元に戻します

        if(get_magic_quotes_gpc()) {
            $product_name        = stripslashes($_POST['product_name']);
            $product_description = stripslashes($_POST['product_description']);
        } else {
            $product_name        = $_POST['product_name'];
            $product_description = $_POST['product_description'];
        }

        // 安全なクエリを作成します
        $query = sprintf("INSERT INTO products (`name`, `description`, `user_id`) VALUES ('%s', '%s', %d)",
                    mysql_real_escape_string($product_name, $link),
                    mysql_real_escape_string($product_description, $link),
                    $_POST['user_id']);

        mysql_query($query, $link);

        if (mysql_affected_rows($link) > 0) {
            echo "製品を追加しました\n";
        }
    }
} else {
    echo "フォームの内容を適切に入力してください\n";
}
?>

これでクエリは正しく実行され、SQL インジェクション攻撃が機能しなくなります。

注意

注意: mysql_real_escape_string() を利用する前に、MySQL 接続が確立されている必要があります。もし存在しなければ、 E_WARNING レベルのエラーが生成され、FALSE が返されます。link_identifier が指定されなかった場合は、 直近の MySQL 接続が用いられます。

注意: magic_quotes_gpc が有効な場合は、 まず最初に stripslashes() を適用します。そうしないと、 すでにエスケープされているデータに対してさらにエスケープ処理を してしまうことになります。

注意: この関数を用いてデータをエスケープしなければ、クエリは SQL インジェクション攻撃 に対しての脆弱性を持ったものになります。

注意: mysql_real_escape_string() は % や _ をエスケープしません。 MySQL では、これらの文字を LIKE, GRANT, または REVOKE とともに用いることで、 ワイルドカードを表現します。

参考



mysql_result> <mysql_query
Last updated: Fri, 02 Jan 2009
 
add a note add a note User Contributed Notes
mysql_real_escape_string
alan at binarystars dot com
03-Jan-2009 02:29
Here is another way to escape2sql (as a string or array type) using both methods of 'mysql' and 'mysqli' by reference or returned. Modify to your liking.

<?php
public function RealEscape2Sql(&$mixedin=NULL,$return=false) { // recursive callback
    # IN: mixed variable
    # OUT: (default) reference or returned, if set.
    if ($return)
        $mixed = $mixedin;
    else
        $mixed = &$mixedin;
    if (!isset($mixed)) { // if it is NULL
        return ($return?$mixed:'');
    }
    if (is_array($mixed)) {
        foreach ($mixed as &$value) {
            $this->RealEscape2Sql($value);
        } unset($value);
    } else if (is_string($mixed)) {
        if ($this->connAs['mysqli']) {
            $mixed = mysqli_real_escape_string($this->conn,$mixed);
        }
        if ($this->connAs['mysql']) {
            $mixed = mysql_real_escape_string($mixed,$this->conn);
        }
    }
    return ($return?$mixed:'');
}
?>

Example:
# Escape data
$db->RealEscape2Sql($report); // is a string, call by reference
$decision_counters_esc = $db->RealEscape2Sql($decision_counters,true); // will return and not modify $decision_counters
jeswanth at gmail
07-Dec-2008 03:12
for all those who would like to use a different solution other than the mysql_real_escape_string or related functions. PHP provides yet another powerful function. I always use this

<?php
if(!preg_match_all("/^[a-zA-Z0-9]{4,20}+$/", $_COOKIE['cookie'], $message)){
echo "<p><font color=red size=4>Username is Illegal</font></p>";
require 'index.php';
die;
}
else {
$cookie = $_COOKIE['cookie'];
}
?>
You can use any user input in place of cookie for example post, get etc. Hope it comes useful for someone.

Thank you.
JonathanFeller at gmx dot ch
27-Oct-2008 02:37
a little addition to the example of Anonymous, 29-May-2008 01:10

<?php
        $magic_quotes_active = get_magic_quotes_gpc();
     $new_enough_php = function_exists("mysql_real_escape_string");
    // i.e PHP >= v4.3.0
    if ($new_enough_php) {
        //undo any magic quote effects so mysql_real_escape_string can do the work
        if ($magic_quotes_active) {
            $string = stripslashes($string);
        }
        $new_string = mysql_real_escape_string($string, $mysql_connection_resource);
        if (empty($new_string) && !empty($string)) {
            die("mysql_real_escape_string failed."); //insert your error handling here
        }
        $string = $new_string;
    } else { // before PHP v4.3.0
        // if magic quotes aren't already on this add slashes manually
        if (!$magic_quotes_active) {
            $string = addslashes($string);
        } //if magic quotes are active, then the slashes already exist
    }
    return $string;
?>

Please note that the function mysql_real_escape_string needs a valid mysql resource to succeed. I just spent some time to find such bug in my code. So I added a bit of code to test if the function succeeded or not.
Tony
20-Sep-2008 09:10
@ Dave

"Your specification of parameters appears to be reversed; in my install of  PHP 5.2.3, link_identifier comes before unescaped_string:

mysqli_real_escape_string($link, "Unescaped string");"

mysqli_real_escape_string is not mysql_real_escape_string. I don't know why they take the parameters in different orders but they do.
@nders AT runsson DOT info
01-Sep-2008 06:53
Just got bitten while uploading binary image data to a database - don't forget to strip slashes before using mysql_real_escape_string regardless of magic quotes settings.
Dave
20-Aug-2008 07:19
Your specification of parameters appears to be reversed; in my install of  PHP 5.2.3, link_identifier comes before unescaped_string:

mysqli_real_escape_string($link, "Unescaped string");
tony
17-Aug-2008 12:16
One of the situations mentioned at:

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

is if you have hashed a string using md5() or sha1().

e.g.

<?php

$str = md5("Some random string"); // Does not need to be escaped

?>
Anonymous
29-May-2008 07:10
<?php

function mysql_prep($value){
    $magic_quotes_active = get_magic_quotes_gpc();
    $new_enough_php = function_exists("mysql_real_escape_string");
    // i.e PHP >= v4.3.0
    if($new_enough_php){
    //undo any magic quote effects so mysql_real_escape_string can do the work
    if($magic_quotes_active){
        $value = stripslashes($value);
    }
    $value = mysql_real_escape_string($value);
    }else{ // before PHP v4.3.0
        // if magic quotes aren't already on this add slashes manually
        if(!$magic_quotes_active){
            $value = addslashes($value);
        } //if magic quotes are avtive, then the slashes already exist
    }
    return $value;
}

?>
drlove21 at gmail dot com
28-Mar-2008 05:46
I had an escape function very similar to Anonomyous 2-3 posts down, so I "borrowed" some of his ideas and incorporated it into mine.  Mine is included in a database interface/wrapper class I use for every database action, and by default it attempts to clean every variable while creating the queries. 
<?php
    //make a variable safe for querying (called by default on all query functions)
    //      $array = $db->sanitize($_GET) -- sanitize all GET variables
    public function sanitize($var, $quotes = true) {
        if (is_array($var)) {   //run each array item through this function (by reference)       
            foreach ($var as &$val) {
                $val = $this->sanitize($val);
            }
        }
        else if (is_string($var)) { //clean strings
            $var = mysql_real_escape_string($var);
            if ($quotes) {
                $var = "'". $var ."'";
            }
        }
        else if (is_null($var)) {   //convert null variables to SQL NULL
            $var = "NULL";
        }
        else if (is_bool($var)) {   //convert boolean variables to binary boolean
            $var = ($var) ? 1 : 0;
        }
        return $var;
    }
?>
Then, for example, in my insert function, I use it like this.
<?php
    //insert values into an database
    //  $rows = column names
    //  $vals = ARRAY of values;
    //  pass a 3rd arg as `true` to prevent sanitization
    //      $db->insert("name, email", array($name, $email))
    public function insert($rows, $vals, $safe = false) {
        $vals = ($safe) ? $vals : $this->sanitize($vals);
        $this->sql = "INSERT INTO {$this->table} ( $rows ) VALUES (";
        foreach ($vals as $key => $val) {
            $this->sql .= ($key > 0) ? ", $val" : "$val";
        }
        $this->sql .= ")";
        return $this->query();
    }
?>
This way, every call is cleaned unless you explicitly tell it not to.  Even if you just write a shortcut function for generic mysql queries, I would do it similar to this.  Always best to error on the safe side.  If anyone wants to see the rest of the class, send me an email.
Bastiaan Welmers
25-Mar-2008 03:46
This function won't help you when inserting binary data, to me it will get mallformed into the database. Probably UTF-8 combinations will be translated by this function or somewhere else when inserting data when running mysql in UTF-8 mode.

A better way to insert binary data is to transfer it to hexadecimal notation like this example:

<?php
$string = $_REQUEST['string'];
$binary = file_get_contents($_FILE['file']['tmp_name']);

$string = mysql_real_escape_string($string);
$binary_hex = bin2hex($binary);

$query = "INSERT INTO `table` (`key`, `string`, `binary`, `other`) VALUES (NULL, '$string', 0x$binary_hex, '$other')";

?>
singh dot prabhat at yahoo dot com
06-Mar-2008 04:37
One solution that I found for the quote problem is to replace quote by equivalent html number. 
     That is, quote symbol ' can be replaced by &#39;
Similarly backslash can be replaced by &#92;
   This would solve the problem of SQL injection and also would not cause any issues on HTML forms as the HTML numbers would be displayed as equivalent symbol to the user.
   Also, you need not escape intended quotes and backslash before inserting into MySQL database.
Anonymous
03-Mar-2008 02:57
My escape function:

Automatically adds quotes (unless $quotes is false), but only for strings. Null values are converted to mysql keyword "null", booleans are converted to 1 or 0, and numbers are left alone. Also can escape a single variable or recursively escape an array of unlimited depth.

function db_escape($values, $quotes = true) {
    if (is_array($values)) {
        foreach ($values as $key => $value) {
            $values[$key] = db_escape($value, $quotes);
        }
    }
    else if ($values === null) {
        $values = 'NULL';
    }
    else if (is_bool($values)) {
        $values = $values ? 1 : 0;
    }
    else if (!is_numeric($values)) {
        $values = mysql_real_escape_string($values);
        if ($quotes) {
            $values = '"' . $values . '"';
        }
    }
    return $values;
}
gth
27-Feb-2008 05:09
Example #3's IF statement could be improved -

<?php
if (isset($_POST['product_name']) && isset($_POST['product_description']) && isset($_POST['user_id'])) {
  ...
// should instead be

if (isset($_POST['product_name'], $_POST['product_description'], $_POST['user_id'])) {
  ...
?>

as per http://au2.php.net/manual/en/function.isset.php
Mister
06-Feb-2008 02:50
Don't forget to escape the escape characters in a LIKE clause!
Basically, you want to first escape the \ character, then use the mysql escape function, then escape the % and _ special characters.

For example:
<?
    $param = preg_replace( '/\\\\/u', '\\\\\\\\', $param );
    $param = mysql_real_escape_string($param);
    $param = preg_replace( '/([%_])/u', '\\\\$1', $param );
?>
(this example assumes you are working with Unicode, hence the 'u' parameters to the regular expressions)
matthijs at yourmediafactory dot com
27-Dec-2007 05:49
In response to Michael D - DigitalGemstones.com:

Check the example again: sprintf(%d) already does the int conversion for you, so it's both perfectly save as well as more elegant than manually casting.
Michael D - DigitalGemstones.com
26-Dec-2007 03:24
Unless I'm mistaken, the third example is a /very/ poor example.  The code:

        // Make a safe query
        $query = sprintf("INSERT INTO products (`name`, `description`, `user_id`) VALUES ('%s', '%s', %d)",
                    mysql_real_escape_string($product_name, $link),
                    mysql_real_escape_string($product_description, $link),
                    $_POST['user_id']);

does not in any way secure $_POST['user_id'] from a malicious attack.  It's very simple to secure against this, simple append (int) to $_POST['user_id'] (since it's a numerical value, anything other than a number will be converted to zero, and thus keep the system secure).  That said, it would probably be wise to either check for a valid user before this query, or not using $_POST values to get the current user, since a 0 in your database for a user ID will create errors in your database, even if there aren't any security flaws.
Vijay
04-Dec-2007 09:30
<?php
$try_pass=md5($_POST['u_pass']);
?>

I would also suggest to add a salt to the MD5 function. So that hackers are able to retreive all the data because of some application bug, they can't be able to get the pain text passwords using a code as follows:

<?php

define('SALT_LENGTH', 9);

function generateHash($plainText, $salt = null)
{
    if ($salt === null)
    {
        $salt = substr(md5(uniqid(rand(), true)), 0, SALT_LENGTH);
    }
    else
    {
        $salt = substr($salt, 0, SALT_LENGTH);
    }

    return $salt . sha1($salt . $plainText);
}

?>

Code from: http://phpsec.org/articles/2005/password-hashing.html
Tim Furry
01-Nov-2007 06:26
Another variant on quote_smart. I figured the problem with other versions (and I *do* use them) is that they're trying to *not* quote numbers; so I turned it around to check for the input value being a string instead.  Keep in mind that all incoming values via GET, POST, etc. are already strings.

This version provides correct quoting for leading zeroes and single-digit numeric zeroes (a fault of most other versions), and places the burden of type-checking on the caller where it belongs (the caller implicitly controls the quoting via the type of value submitted to the function).  All strings are quoted even if castable to a numeric type.  Any non-string values are examined for being numeric and returned unchanged if so; otherwise an error is returned.  I couldn't think of any real world scenarios where handling arrays, objects or booleans made sense (MySQL doesn't support any of these natively).

This version also has two optional parameters that accept a boolean for "nullify" (returns NULL for nulls/empty strings/unset values if true instead of a quoted empty string) and "conn" (database connection resource for the mysql_real_escape_string function if desired).  To skip the nullify parameter but use the conn parameter, send null for the second parameter in the call.

Hope it'll help someone else out.

<?php
function quote_smart($value = "", $nullify = false, $conn = null) {
  //reset default if second parameter is skipped
  $nullify = ($nullify === null) ? (false) : ($nullify);
  //undo slashes for poorly configured servers
  $value = (get_magic_quotes_gpc()) ? (stripslashes($value)) : ($value);

  //check for null/unset/empty strings (takes advantage of short-circuit evals to avoid a warning)
  if ((!isset($value)) || (is_null($value)) || ($value === "")) {
    $value = ($nullify) ? ("NULL") : ("''");
  }
  else {
    if (is_string($value)) {
      //value is a string and should be quoted; determine best method based on available extensions
      if (function_exists('mysql_real_escape_string')) {
        $value = "'" . (((isset($conn)) && (is_resource($conn))) ? (mysql_real_escape_string($value, $conn)) : (mysql_real_escape_string($value))) . "'";
      }
      else {
        $value = "'" . mysql_escape_string($value) . "'";
      }
    }
    else {
      //value is not a string; if not numeric, bail with error
      $value = (is_numeric($value)) ? ($value) : ("'ERROR: unhandled datatype in quote_smart'");
    }
  }
  return $value;
}
?>
Anonymous
26-Oct-2007 07:09
// this doesn't do adding the quotes, checking for null, etc that the quote smart does, it simply sanitizes input and handles arrays recursively

<?php
function sanitize($input){
    if(is_array($input)){
        foreach($input as $k=>$i){
            $output[$k]=sanitize($i);
        }
    }
    else{
        if(get_magic_quotes_gpc()){
            $input=stripslashes($input);
        }       
        $output=mysql_real_escape_string($input);
    }   
   
    return $output;
}
?>

// use: if you are going to use $_POST, $_GET, $_COOKIE or $_REQUEST, include the appropriate line first.

$_POST=sanitize($_POST);
$_GET=sanitize($_GET);
$_COOKIE=sanitize($_COOKIE);
$_REQUEST=sanitize($_REQUEST);
user at NOSPAM dot example dot com
28-Aug-2007 08:16
if you're doing a mysql wildcard query with
LIKE, GRANT, or REVOKE
you may use addcslashes to escape the string:
<?
$param = mysql_real_escape_string($param);
$param = addcslashes($param, '%_');
?>
soundefined at gmail dot com
23-Jun-2007 10:32
An update on Example 1428. A "Best Practice" query
if (isset($_POST['product_name']) && isset($_POST['product_description']) && isset($_POST['user_id'])) {
...........
}
else {
echo "Fill the form properly\n";
}

The condition above will still put empty data into a database even though forms are empty. The condition should be corrected to code below. It checks empty fields in a form and handle errors.

if (!empty($_POST['product_name']) && !empty($_POST['product_description']) && !empty($_POST['user_id'])) {
...........
}
else {
echo "Fill the form properly\n";
}
Piti
17-May-2007 11:22
The function quote_smart provided will fail on the following kind of string

2E345668

The is_numeric will return true and will treat it as number. When it's passed to mysql, mysql'll think that it's a double and will try to parse it. But this number is much larger than the maximum allowed double in mysql. Thus, mysql will complain.

The fix is to add is_finite check when checking if the argument is a number.
brandonlira AT gmail DOT com
06-May-2007 06:09
Quick update to example 1402.A, in the example no database is selected, and therefore the query won't work right. Here is a updated version with the database selected. If anyone has any thoughts on how to improve my selection method, please let me know.

<?php

if (isset($_POST['product_name']) && isset($_POST['product_description']) && isset($_POST['user_id'])) {
    // Connect

    $link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password');
   
    $select = mysql_select_db('mysql_name', $link);

    if(!is_resource($link)) {

        echo "Failed to connect to the server";

    } elseif(!$select) {
       
        echo "Failed to select database";

    } else {
      
        // Reverse magic_quotes_gpc/magic_quotes_sybase effects on those vars if ON.

        if(get_magic_quotes_gpc()) {
            if(ini_get('magic_quotes_sybase')) {
                $product_name        = str_replace("''", "'", $_POST['product_name']);
                $product_description = str_replace("''", "'", $_POST['product_description']);
            } else {
                $product_name        = stripslashes($_POST['product_name']);
                $product_description = stripslashes($_POST['product_description']);
            }
        } else {
            $product_name        = $_POST['product_name'];
            $product_description = $_POST['product_description'];
        }

        // Make a safe query
        $query = sprintf("INSERT INTO products (`name`, `description`, `user_id`) VALUES ('%s', '%s', %d)",
                    mysql_real_escape_string($product_name, $link),
                    mysql_real_escape_string($product_description, $link),
                    $_POST['user_id']);

        mysql_query($query, $link);

        if (mysql_affected_rows($link) > 0) {
            echo "Product inserted\n";
        }
    }
} else {
    echo "Fill the form properly\n";
}
?>

Enjoy!
dbrucas
22-Nov-2006 02:44
Of course, all of this is moot if you use the new (pdp 5.1) PDO class and PDO::prepare($sql) with bound variables that protects against sql injection.
admin of archmagesofvanadei.net
16-Nov-2006 01:35
The quote_smart function in the example above has no issues with losing leading zeros.

If the variable passed to it is numeric in nature then the variable is returned unchanged. This is fine as a number cannot be the source of an insertion attack.

If the variable passed to it is a numbic string (that is to say a string whose format is that of a number per the rules of is_numeric) then the variable is is also returned UNCHANGED.  This is also fine because even though the variable is a string, we've confirmed that it contains only numbers, with a possible '.', '+', '-', 'e' (for exponential notation), or 'x' if it's a number in hex.  None of these characters is a risk.

If the variable is a string and does not appear to be a number it is properly escaped.
PhatFingers
21-Oct-2006 05:40
I concur with icydee.  It's important that SQL Injection is prevented on the server side.  A person (or software) doing SQL Injection on your site will often post to your page from one of his own making.  For example, you may present a page like the following on http://examplesite.com/ that sanitizes all data before submitting.

<html>
<head><script type="text/javascript" src="validation.js" /></head>
<body>
  <form method="POST" action="/login.php">
  Name: <input type="text" name="the_acount" value="" /><br/>
  Password: <input type="password" name="pwd" value="" /><br/>
  <input type="submit" name="action" value="Submit" onClick="Sanitize()"/>
</body>
</html>

Your attacker views the source code and makes his own login page on his local hard drive.  It doesn't have to look like or even load from your page as long as he posts the form data to your site using your expected parameters.

<html><body>
  <form method="POST" action="http://examplesite.com/login.php">
  <input type="hidden" name="the_acount" value="1'; drop table users; select 1 where name='" /><br/>
  Password: <input type="hidden" name="pwd" value="Oh, was I supposed to be hashed?" /><br/>
  <input type="submit" name="action" value="Submit" />
</body></html>
johniskew at web-imagineer dot com
20-Oct-2006 10:06
I think instead of just using the quote_smart function to quote whatever the user gives you, you should ideally specify whether you are looking for a string or a number.  For example if you have a user script and are using a sql statement like this, where username is a char or varchar field:
$sql="SELECT id FROM user_table WHERE user_name=".quote_smart($_POST['userName']);

if the user submits 12345 for example, you get:
SELECT id FROM user_table WHERE user_name=12345

Your DB 1) Must convert the 12345 to a string (not a huge deal) 2) Probably cannot use any index you have on that column (could be a bigger deal with a sizeable table)

So i think something more along the lines of:
function quote_smart($val,$valType) and handle the input based on $valType (being either string or numeric)
vbaspcppguy at gmail dot com
17-Oct-2006 08:26
After reading through all the different versions I came up with this:

function quote_smart($value)
{
    if( is_array($value) ) {
        return array_map("quote_smart", $value);
    } else {
        if( get_magic_quotes_gpc() ) {
            $value = stripslashes($value);
        }
        if( $value == '' ) {
            $value = 'NULL';
        } if( !is_numeric($value) || $value[0] == '0' ) {
            $value = "'".mysql_real_escape_string($value)."'";
        }
        return $value;
    }
}

This version is a little slower but it will recursively handle arrays.
IVIaniac
03-Oct-2006 11:37
To aide in the simplicity of things, I try and validate the information before it is even queried to the MySQL server.

For example. With usernames and passwords, I check for spaces. And ; signs. If either of these are in there, then the login request is ignored, since I do not allow either during registration.

So a simple attack like:
1 or 1=1

Isn't even sent. Instead a "Please check username / password and try again" message is displayed. I display the same message when the query is ignored as I do when an invalid login is parsed. As to not "challenge" the attacker. Make them think simply it was an invalide username/password, rather than a big deal like

die("Hacking attempt!!!");

You could very easily implement logging to keep track of malicious IP's and what-not as well.
10-Sep-2006 01:22
Would this be better?

functions quote_smart($value) {
    if(is_array($value)) {
        if(get_magic_quotes_gpc()) {
            $value=array_map("stripslashes",$value);
            }
        if(!array_map("is_number",$value)) {
            $value=array_map("mysql_real_escape_string",$value);
            }
        }
    else {
        if(get_magic_quotes_gpc()) {
            $value=stripslashes($value);
            }
        if(!is_number($value)) {
            $value="'" . mysql_real_escape_string($value) . "'";
            }
        }
    return $value;
    }
brian dot folts at gmail dot com
07-Sep-2006 12:25
mysql_real_escape_string is a bit annoying when you need to do it over an array.

function mysql_real_escape_array($t){
    return array_map("mysql_real_escape_string",$t);
}

this one just mysql_real_escape's the whole array.

ex) $_POST=mysql_real_escape_array($_POST);

and then you dont have to worry about forgetting to do this.
FPawlak2 at gmail dot com
04-Sep-2006 09:31
To: eddypearson at gmail dot com

If thing that, this is better:

<?
$_POST  = array_map('mysql_real_escape_string', $_POST);
?>

or

<?
$_SESSION  = array_map('mysql_real_escape_string', $_SESSION);
?>
tomfmason at spammerssuck dot com
05-Aug-2006 07:36
<?php
include ('includes/db.php');
array_pop($_POST);
if ( get_magic_quotes_gpc() ) {
    $_POST= array_map('stripslashes', $_POST);
}
$username= mysql_real_escape_string(trim($_POST['username']));
$password= mysql_real_escape_string(trim($_POST['password']));
$mdpwd= md5($password);

$sql= sprintf("SELECT COUNT(*) AS login_match FROM `users` WHERE `username` = '%s' AND `password`= '%s'", $username, $mdpwd);
$res= mysql_query($sql) or die(mysql_error());
$login_match= mysql_result($res, 0, 'login_match');

if ( $login_match == 1 ) {
    //logged in
} else {
    // not logged in
}
?>
eddypearson at gmail dot com
28-Jul-2006 10:28
A quick function to mysql_real_escape_string every value in array (Think $_SESSION and $_POST guys).
This may be simple, and to some bloody obvious, but its useful little function, and I could find one on this page so:

function CleanArray($array) {
foreach ($array as $key => $value) {
$array[$key] = mysql_real_escape_string($value);
}
return $array;
}
djogopatrao at gmail dot com
21-Jul-2006 03:39
According to the newsforge refered to by Picky, it is adviseable to run PREPAREd statements in order to avoid injection. Plus, I created a table that stores pre-defined prepared statements and a procedure that runs that query. That way it's possible to control more tighly what's to be queried.

Example in mysql5 lingo:

create sys_queries (
      name varchar(100), # query ID
      mysql_statement text, #
      primary key( name )
);

insert into sys_queries set name='search_users', 'SELECT * FROM users WHERE concat( username, name, email ) REGEXP ?';

delim $$
create sp_perform_query( _name varchar(100), _search_term varchar(255) )
begin
        DECLARE _stat TEXT DEFAULT NULL;
        SELECT mysql_statement INTO _stat FROM sys_queries WHERE name = _name;
        IF _stat IS NOT NULL THEN
                SET @a = _stat;
                SET @b = _search_term;
                PREPARE s FROM @a;
                EXECUTE s USING @b;
        END IF;
end;
$$
delim ;

in PHP5, use mysqli to call the procedure, first arg is the query name, second, the search arg. Still don't know a good way to pass multiple arguments to mysql, so please let me know.
kael dot shipman at DONTSPAMIT! dot gmail dot com
19-Jul-2006 04:19
It seems to me that you could avoid many hassels by loading valid database values into an array at the beginning of the script, then instead of using user input to query the database directly, use it to query the array you've created. For example:

<?php
//you still have to query safely, so always use cleanup functions like eric256's
$categories = sql_query("select catName from categories where pageID = ?",$_GET['pageID']);
while ($cts = @mysql_fetch_row($categories)) {
 //making $cts both the name and the value of the array variable makes it easier to check for in the future.
 //obviously, this naming system wouldn't work for a multidimensional array
 $cat_ar[$cts[0]] = $cts[0];
}
...

//user selects sorting criteria
//this would be from a query string like '?cats[]=cha&cats[]=fah&cats[]=lah&cats[]=badValue...', etc.
$cats = $_GET['cats'];

//verify that values exist in database before building sorting query
foreach($cats as $c) {
 if ($cat_ar[$c]) { //instead of in_array(); maybe I'm just lazy... (see above note)
  $cats1[] = "'".mysql_real_escape_string($c)."'";
 }
}
$cats = $cats1;
//$cats now contains the filtered and escaped values of the query string

$cat_query = '&& (category_name = \''.implode(' || category_name = \'',$cats).'\')';
//build a sql query insert
//$cat_query is now "&& (category_name = 'cha' || category_name = 'fah' || category_name = 'lah')" - badValue has been removed
//since all values have already been verified and escaped, you can simply use them in a query
//however, since $pageID hasn't been cleaned for this query, you still have to use your cleaning function
$items = sql_query("SELECT * FROM items i, categories c WHERE i.catID = c.catID && pageID = ? $cat_query", $pageID);
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 add a note

mysql_result> <mysql_query
Last updated: Fri, 02 Jan 2009
 
 
show source | credits | sitemap | contact | advertising | mirror sites