note 55342 added to function.mysql-escape-string

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. ---- Submitter IP: 64.246.30.37 ---- Manual Page -- http://www.php.net/manual/en/function.mysql-escape-string.php Edit -- http://master.php.net/manage/user-notes.php?action=edit+55342 Delete: added to the manual -- http://master.php.net/manage/user-notes.php?action=delete+55342&report=yes&reason=added+to+the+manual Delete: bad code -- http://master.php.net/manage/user-notes.php?action=delete+55342&report=yes&reason=bad+code Delete: spam -- http://master.php.net/manage/user-notes.php?action=delete+55342&report=yes&reason=spam Delete: useless -- http://master.php.net/manage/user-notes.php?action=delete+55342&report=yes&reason=useless Delete: non-english -- http://master.php.net/manage/user-notes.php?action=delete+55342&report=yes&reason=non-english Delete: other reasons -- http://master.php.net/manage/user-notes.php?action=delete+55342&report=yes Reject -- http://master.php.net/manage/user-notes.php?action=reject+55342&report=yes Search -- http://master.php.net/manage/user-notes.php