PDO :: quote () for SQL Server incorrectly quotes strings containing ASCII NUL

Question

I am trying to insert ASCII NUL characters ( \0aka U+0000) into a SQL Server database with PHP using pdo_sqlsrv. This is a requirement for handling serialized PHP strings that contain NUL characters to represent private / protected variables.

However, there is something about PDO :: quote () that breaks the lines.

Code to play (replace DBNAME, USERNAMEand PASSWORDwith the appropriate values):

<?php

try {
    $dsn = 'sqlsrv:Server=.\SQLEXPRESS;Database=DBNAME';
    $user = 'USERNAME';
    $pass = 'PASSWORD';

    $connection = new PDO($dsn, $user, $pass);
} catch (PDOException $e) {
    die("Connection error: " . $e->getMessage());
}

$str = "XX\0XX";

header("Content-Type: text/plain");

print("Original: " . str_replace("\0", "{NUL}", $str) . "\n");
$str = $connection->quote($str);
print("Quoted:   " . str_replace("\0", "{NUL}", $str) . "\n");

?>

Expected Result:

Original: XX{NUL}XX
Quoted:   'XX{NUL}XX'

Actual output:

Original: XX{NUL}XX
Quoted:   'XX'{NUL}{NUL}a

Can someone explain this strange behavior and, more importantly, explain how to solve it?

UPDATE

, , e. , . . , pdo_sqlsrv?

Answer 1

- ...

, pdo_sqlsrv (, , PDO sqlsrv).

( # 538), , , .

... - - , ?

, , - NUL:   

try {
    $dsn = 'sqlsrv:Server=.\SQLEXPRESS;Database=DBNAME';
    $user = 'USERNAME';
    $pass = 'PASSWORD';

    $connection = new PDO($dsn, $user, $pass);
} catch (PDOException $e) {
    die("Connection error: " . $e->getMessage());
}

$str = "XX\0XX";

header("Content-Type: text/plain");

print("Original: " . str_replace("\0", "{NUL}", $str) . "\n");
$str = safeQuote($str, $connection);
print("Quoted:   " . str_replace("\0", "{NUL}", $str) . "\n");

function safeQuote($str, $connection) {
// Special handling of ASCII NUL characters, which cause breakages.
// This appears to be due to a bug in the pdo_sqlsrv driver.
// For performance reasons, we only do this for strings that contain \0.
    if (is_string($str) && strpos($str, "\0") !== false) {
        $arrParts = explode("\0", $str);
        foreach ($arrParts as $Key => $Part) {
            $arrParts[$Key] = $connection->quote($Part);
        }
        $str = implode(" + CHAR(0) + ", $arrParts);
    }
// Otherwise, prepare the quoted string in the standard way.
    else {
        $str = $connection->quote($str);
    }

    return $str;
}

?>

, , , SQL Server:

Original: XX{NUL}XX
Quoted:   'XX' + CHAR(0) + 'XX'

: 2017

​​ SQLSRV/5.2.0.

Answer 2

• \ LIKE , , .

• PDO::quote , PDO::quote.

SQL, PDO:: prepare() SQL PDO:: quote() SQL, , , SQL-, , , .

Sandbox

<?php
$pdo = new \PDO('sqlite::memory:', null, null);

$str = "XX\0XX";

print($pdo->quote(addSlashes($str))); <----> 'XX\0XX'

?>

Answer 3

32- SQLSRV/3.2 PHP/5.6.21 :

$str = "XX\0XX";
for($i=0; $i<5; $i++){
    $connection = new PDO($dsn, $user, $pass);
    printf("0x%s -> 0x%s\n", bin2hex($str), bin2hex($connection->quote($str)));
    printf("0x%s -> 0x%s\n", bin2hex($str), bin2hex($connection->quote($str)));
}

0x5858005858 -> 0x27585827000005
0x5858005858 -> 0x27585827000005
0x5858005858 -> 0x2758582700b72b
0x5858005858 -> 0x2758582700b72b
0x5858005858 -> 0x27585827000306
0x5858005858 -> 0x27585827000306
0x5858005858 -> 0x2758582700b72b
0x5858005858 -> 0x2758582700b72b
0x5858005858 -> 0x27585827000005
0x5858005858 -> 0x27585827000005

.

:

• ,
• CharacterSet ( )
• github , .

, . (v4.3.0 32- PHP/7.1.9), (, , , ).

, , PDO::quote() SQLSRV. , , , ( ) ,

INSERT INTO foo (bar) VALUES (0x5858005858);