#44998 [Fbk->Opn]: mysqli_real_escape_string not properly escaping null characters

ID: 44998 User updated by: djneoform at gmail dot com Reported By: djneoform at gmail dot com -Status: Feedback +Status: Open Bug Type: MySQLi related Operating System: Win2003 Standard PHP Version: 5.2.6 Assigned To: andrey New Comment: http://phpneoform.com/error.php Here's this script running on a win2k3 server with PHP 5.2.6 and mysql 5.0.62 <?php $mysqli = new mysqli('localhost', 'xxx', 'xxx', 'xxx'); $mysqli->query("DROP TABLE IF EXISTS test_table"); $mysqli->query(" CREATE TABLE test_table ( id int(10) unsigned NOT NULL auto_increment, name binary(100) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; "); $str = str_repeat('A', 25).chr(0x0).str_repeat('B', 25); echo "TEST STRING LENGTH: ".strlen($str)."<br />\n"; $mysqli->query(" INSERT INTO test_table SET name = '".$mysqli->real_escape_string($str)."' "); $id = $mysqli->insert_id; $result = $mysqli->query(" SELECT name FROM test_table WHERE id = '".intval($id)."' "); $result = $result->fetch_object(); echo "RETURNED STRING LENGTH: ".strlen($result->name)."<br />\n"; ?> Previous Comments: ------------------------------------------------------------------------ [2008-07-24 13:37:53] andrey@php.net Hi, Do you still experience it? Can you reproduce it with a simple script? Can you provide a dump or just the data, index + frm, considering you are using MyISAM? ------------------------------------------------------------------------ [2008-07-14 18:16:27] djneoform at gmail dot com I was using v.5.0.51b 64bit (win2k3) at the time. Right now I'm using a compiled version of the enterprise code, 5.0.62 from apachelounge.com. When I do an insert a value: mysqli_real_escape_string('foo'.chr(0x0).'bar') all i see in the table after is "foo" maybe this is a windows only issue? ------------------------------------------------------------------------ [2008-07-14 17:58:38] uw@php.net What MySQL version are you using? Interesting find. The mysqli_real_escape_string test does play with \0: if ("foo\\0bar" !== ($tmp = mysqli_real_escape_string($link, "foo" . chr(0) . "bar"))) printf("[009] Expecting %s, got %s\n", "foo\\0bar", $tmp); But it does not try to insert/fetch such a string. Of course it works fine with PHP 5.3 and a recent MySQL and on Linux... ------------------------------------------------------------------------ [2008-05-15 16:52:47] djneoform at gmail dot com I'm able to get around this problem by replacing 0x0 chars with 0x1 prior to passing the string to mysqli_real_escape_string(), however this damages the integrity of the binary string. ------------------------------------------------------------------------ [2008-05-14 20:48:21] djneoform at gmail dot com Description: ------------ When dealing with binary strings that are being escaped and inserted into a mysql table that contains a BINARY(128) field, the data that ends up being inserted is incomplete if the string contains a null character within the string. Reproduce code: --------------- /* --TABLE DEF: CREATE TABLE system_users_stored_sessions ( session binary(128) NOT NULL, name char(32) NOT NULL, posted_on datetime NOT NULL, body varchar(65000) NOT NULL, PRIMARY KEY (session,name), KEY posted_on (posted_on) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; */ $hash_with_null_char = hash('whirlpool', '1210797760.8178', true); $mysqli->query(" INSERT INTO user_sessions SET session = '".$mysqli->real_escape_string($hash_with_null_char)."', posted_on = NOW(), name = '".$mysqli->real_escape_string($name)."', body = '".$mysqli->real_escape_string($body)."' "); Expected result: ---------------- Expecting a row containing the full binary hash. Actual result: -------------- Actual result: a binary string that is terminated by the null char in the string, not the actual ending. I am able to work around this by doing a addslashes() to the string before doing the mysqli_real_escape_string(), however all other chars are double escaped making the resulting string be longer than it wanted (128 binary chars). ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=44998&edit=1