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

[djneoform at gmail dot com]

 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] [EMAIL PROTECTED]

 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] [EMAIL PROTECTED]

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