Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg



PHP : Function Reference : MySQL Functions : mysql_escape_string

mysql_escape_string

Escapes a string for use in a mysql_query (PHP 4 >= 4.0.3, PHP 5, PECL mysql:1.0)
string mysql_escape_string ( string unescaped_string )

Example 1425. mysql_escape_string() example

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

The above example will output:

Escaped string: Zak\'s Laptop

Code Examples / Notes » mysql_escape_string

boris-pieper

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


mbr

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

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));
}


04-feb-2005 08: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;
 }


codeslinger

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.


steve

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)));
}


Change Language


Follow Navioo On Twitter
mysql_affected_rows
mysql_change_user
mysql_client_encoding
mysql_close
mysql_connect
mysql_create_db
mysql_data_seek
mysql_db_name
mysql_db_query
mysql_drop_db
mysql_errno
mysql_error
mysql_escape_string
mysql_fetch_array
mysql_fetch_assoc
mysql_fetch_field
mysql_fetch_lengths
mysql_fetch_object
mysql_fetch_row
mysql_field_flags
mysql_field_len
mysql_field_name
mysql_field_seek
mysql_field_table
mysql_field_type
mysql_free_result
mysql_get_client_info
mysql_get_host_info
mysql_get_proto_info
mysql_get_server_info
mysql_info
mysql_insert_id
mysql_list_dbs
mysql_list_fields
mysql_list_processes
mysql_list_tables
mysql_num_fields
mysql_num_rows
mysql_pconnect
mysql_ping
mysql_query
mysql_real_escape_string
mysql_result
mysql_select_db
mysql_set_charset
mysql_stat
mysql_tablename
mysql_thread_id
mysql_unbuffered_query
eXTReMe Tracker