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.

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... 

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?

 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?

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";
?>

Results:

TEST STRING LENGTH: 51
RETURNED STRING LENGTH: 100

The resulting dump for the table once i run the script.

/*
MySQL Data Transfer
Source Host: localhost
Source Database: phpneoform
Target Host: localhost
Target Database: phpneoform
Date: 7/24/2008 10:22:31 AM
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for test_table
-- ----------------------------
CREATE TABLE `test_table` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` binary(100) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `test_table` VALUES ('1', 'AAAAAAAAAAAAAAAAAAAAAAAAA');

Experienced the same thing when storing files in mysql. Many files end up corrupt since the file ends before it was supposed to.

Hi, this is not a bug. Seems like, but it is a feature of MySQL's BINARY data type. http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html  reads :
---------------------------
 When BINARY values are stored, they are right-padded with the pad value to the specified length. The pad value and how it is handled is version specific:

- As of MySQL 5.0.15, the pad value is 0x00 (the zero byte). Values are right-padded with 0x00 on insert, and no trailing bytes are removed on select. All bytes are significant in comparisons, including ORDER BY and DISTINCT operations. 0x00 bytes and spaces are different in comparisons, with 0x00 < space.
Example: For a BINARY(3) column, 'a ' becomes 'a \0' when inserted. 'a\0' becomes 'a\0\0' when inserted. Both inserted values remain unchanged when selected.

-Before MySQL 5.0.15, the pad value is space. Values are right-padded with space on insert, and trailing spaces are removed on select. Trailing spaces are ignored in comparisons, including ORDER BY and DISTINCT operations. 0x00 bytes and spaces are different in comparisons, with 0x00 < space.
Example: For a BINARY(3) column, 'a ' becomes 'a  ' when inserted and 'a' when selected. 'a\0' becomes 'a\0 ' when inserted and 'a\0' when selected.
-------------
The \0 byte in the between is not lost, I see it from the network dump, but \0 has no visual representation when dumped to screen, thus we see nothing inbetween.

But this can't be expected behavior, my string is being truncated improperly.

If I manually run the mysql query in the cli, I get proper results:

mysql> INSERT INTO `test_table` VALUES ('2', 'AAAAAAAAAAAAAAAAAAAAAAAAA\0BBBBBBBBBBBBBBBBBBB');

mysql> select * from test_table;
+----+------------------------------------------------------------------------------------------------------+
| id | name                                                                                                 |
+----+------------------------------------------------------------------------------------------------------+
|  1 | AAAAAAAAAAAAAAAAAAAAAAAAA                                                                            |
|  2 | AAAAAAAAAAAAAAAAAAAAAAAAA BBBBBBBBBBBBBBBBBBB                                                        |
+----+------------------------------------------------------------------------------------------------------+

If I do the exact same insert query in PHP, I lose half my string. Clearly this is not due to mysql.