|
|
|
|
Bookmark this on Delicious
Share on Facebook
 Slashdot It!
Digg  mysql_escape_string
Escapes a string for use in a mysql_query
(PHP 4 >= 4.0.3, PHP 5, PECL mysql:1.0)
Example 1425. mysql_escape_string() example<?phpThe above example will output: Escaped string: Zak\'s LaptopCode Examples / Notes » mysql_escape_stringboris-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
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 |
