PHP 手册
后退前进


mysql_escape_string

(PHP 4 >= 4.0.3, PHP 5)

mysql_escape_string --  转义一个字符串用于 mysql_query

说明

string mysql_escape_string ( string unescaped_string )

本函数将 unescaped_string 转义，使之可以安全用于 mysql_query()。

注: mysql_escape_string() 并不转义 % 和 _。

本函数和 mysql_real_escape_string() 完全一样，除了 mysql_real_escape_string() 接受的是一个连接句柄并根据当前字符集转移字符串之外。mysql_escape_string() 并不接受连接参数，也不管当前字符集设定。

例子 1. mysql_escape_string() 例子

<?php
    $item = "Zak's Laptop";
    $escaped_item = mysql_escape_string($item);
    printf ("Escaped string: %s\n", $escaped_item);
?>

以上例子将产生如下输出： 

Escaped string: Zak\'s Laptop

参见 mysql_real_escape_string()，addslashes() 和 magic_quotes_gpc 指令。


add a note add a note User Contributed Notes
eth at ethaniel dot com
20-Aug-2006 03:26
a way to clean _POST _GET and _COOKIE along with their registered global variables.

<?php
function cleanmysql($arr,$arr2) {
foreach ($arr as $gt1=>$gt2) {
if (is_array($arr[$gt1])) {
list($arr[$gt1],$arr2[$gt1])=cleanmysql($arr[$gt1],$arr2[$gt1]);
} else {
$gt2=mysql_escape_string($gt2);
$arr[$gt1]=$arr2[$gt1]=$gt2;
}
}
return array($arr,$arr2);
}

list($_GET,$GLOBALS) = cleanmysql($_GET,$GLOBALS);
list($_POST,$GLOBALS) = cleanmysql($_POST,$GLOBALS);
list($_COOKIE,$GLOBALS) = cleanmysql($_COOKIE,$GLOBALS);
?>
info at globalissa dot com
18-Aug-2006 06:37
We need to distribute code compatible with as many servers as possible. We use this little function on user submitted data:

// http://php.net/mysql_escape_string used for wider isp host support - start

if(!function_exists('escape_smart')){
// Quote variable to make safe
function escape_smart($value)
{
   // Stripslashes
   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }
   // Quote if not integer
   if (!is_numeric($value)) {
       $value = mysql_escape_string($value);
   }
   return $value;
}
}
mbr at arlsoft dot com
31-Jul-2005 10:52
steve at tequilasolutions dot com suggests base64_encode() as a
better choice than bin2hex().  Unfortunately, it's not that
simple.  Since serialize() can be used with either of them, I
won't consider it in this argument.  But it's important to
consider not only what happens when you store the data with an
INSERT or UPDATE, but also what happens when you later fetch the
data with a SELECT.

Storing data using INSERT or UPDATE:
   Size of the string in the PHP variable:
       base64_encode(): approx. 1.33 * length of the data
       bin2hex(): 2 * length of the data
   Size of data transmitted to the MySQL server:
       base64_encode(): approx. 1.33 * length of the data
       bin2hex(): 2 * length of the data
   Size of data stored in the MySQL server:
       base64_encode():
           The SQL parser will see the argument "..." and store
           whatever bytes it finds inside the quotes.  So the
           disk space required to store this is approx. 1.33
           times the length of the data.
       bin2hex():
           The SQL parser will see the argument x'...',
           recognize it as indicating that the argument is
           encoded as hexadecimal, convert it back a string half
           the length of the hex representation, and store the
           result.  So the disk space required to store this is
           the same as the original length of the data.

Fetching data using SELECT:
   Size of data transmitted from the MySQL server:
       base64_encode():
           The same as what's stored in the database -
           approx. 1.33 times the length of the original data.
       bin2hex():
           The same as what's stored in the database - the
           length of the original data

I've summarized the comparison below:

Storing data:
   processing time on the PHP side
       unknown whether base64_encode() or bin2hex() runs faster.
   bandwidth
       base64_encode() transmits fewer bytes across the link.
   processing time on MySQL side
       unknown whether decoding hex inside x'' (the bin2hex()
       case) is notably slower than searching inside "" for (and
       not finding any) characters escaped by a backslash (the
       base64_encode() case).
   data storage in MySQL
       bin2hex() is better - base64_encode() inflates data by
       about 33%, whereas the data encoded by bin2hex() is
       placed inside quotes that cause it to be deflated back to
       its original size before it is stored.

Fetching data:
   bandwidth
       bin2hex() is better - data encoded by base64_encode()
       before storage will have been inflated by about 33%.
   processing time on the PHP side
       bin2hex() is better - since the data was already decoded
       by the SQL parser before it was stored, bin2hex()
       requires no decoding, whereas data encoded by
       base64_encode() before storage must be decoded by
       base64_decode() whenever it is fetched.

Summary:

   The bin2hex() approach seems better for something like a
   store catalog, which changes infrequently, but which is
   viewed frequently (the store owners hope :-) ).

   The base64_encode() approach seems better for something like
   a network management system which constantly logs alarms from
   network devices, but which is queried infrequently.
steve at tequilasolutions dot com
24-May-2005 04:57
Also... (see previous) I don't bother because my blobs are always multimedia files so don't compress well but if you were storing word docs etc adding compression into the mix saves space and will often be smaller than the original data.

e.g, - off the top of my head this, not syntax checked

$x = enc($mydata);
mysql_query("insert into table values('$x')");

function enc($x){
  return base64_encode(bzcompress(serialize($x),9));
}

function dec($x){
  return unserialize(bzdecompress(base64_decode($x)));
}
steve at tequilasolutions dot com
23-May-2005 10:39
Rather than bin2hex use a combination of serialise and base64.  Data gets 33% bigger than source but with bin2hex data gets 90% bigger.

I have two functions for encoding and decoding data before use in sql.  Using serialse preserves the datatypes so that you can insert any data you like, arrays etc.

function enc($x){
  return base64_encode(serialize($x));
}
  
function dec($x){
  return unserialize(base64_decode($x));
}
codeslinger at compsalot dot com
06-Feb-2005 04:49
er um...  version_compare did not exist prior to 4.1.0

in any case, adding slashes and dealing with magic quotes etc.  is a sure recipe for major headaches.

You can avoid a whole slew of problems by converting your strings to hex (bin2hex) before passing them to mySQL

mySQL will accept any value in the form of
0xFEAC1234...

It will then automagically convert it back to a string for storage and retrieval while avoiding all of the zillions of problems with special characters.

I have done this with some very large records and never had a problem.
04-Feb-2005 04:03
Here's the solution I came up with for unescaping.  I'm not a "real" programmer so there's probably some huge problem with this.  I've been using it for a while and it seems to work okay though.

function escape_string($string) {

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

function unescape_string($string) {
  stripslashes($string);
  $string = str_replace('<br />', Chr(13), $string);
  return $string;
  }
boris-pieper AT t-online DOT de
16-Jan-2005 07:07
Using a function like escape_string make sure you allways use optimal escape function...

Note: You should use mysql_real_escape_string() (http://php.net/mysql_real_escape_string) if possible (PHP => 4.3.0) instead of mysql_escape_string().

<?php

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

?>
add a note add a note


后退起点前进
mysql_error上一级mysql_fetch_array