PDO inserts NULL byte at position 254 on SQL Server ntext column

[danielmarschall]

Description

The following code:

<?php
try {
    $pdo = new PDO('odbc:DRIVER={SQL Server};SERVER=SHS\HS2017,49011;DATABASE=OIDDB', '', '');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $query = 'SELECT name, description, protected, visible, value FROM oidplus_config';
    $stmt = $pdo->query($query);
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($results as $row) {
        if (($p = strpos($row['value'],"\0")) !== false) echo "PROBLEM: NULL BYTE FOUND AT ".$row['name']." AT POSITION $p\n";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

Resulted in this output:

PROBLEM: NULL BYTE FOUND AT oidplus_private_key AT POSITION 254
PROBLEM: NULL BYTE FOUND AT oidplus_public_key AT POSITION 254

But I expected this output instead:

(Nothing)

I have verified that in the database the NULL byte is not there. In the database, the field has the correct length, in PHP strlen() is 1 byte too much, because of the inserted NULL byte

This seems to be very similar to the bug described at https://bugs.php.net/bug.php?id=74021

The SQL table is created as follows:

USE [OIDDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[oidplus_config](
    [name] [nvarchar](50) NOT NULL,
    [value] [ntext] NOT NULL,
    [description] [nvarchar](255) NULL,
    [protected] [bit] NOT NULL,
    [visible] [bit] NOT NULL,
 CONSTRAINT [PK_oidplus_config] PRIMARY KEY CLUSTERED 
(
    [name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[oidplus_config] ADD  DEFAULT ('0') FOR [protected]
GO

ALTER TABLE [dbo].[oidplus_config] ADD  DEFAULT ('0') FOR [visible]
GO

PHP Version

8.4.1 (also tested with 8.3.6)

Operating System

Windows 10

[cmb69]

Likely related to #16450 and the whole bunch of related issues. :)

[cmb69]

Hmm, I tried, but couldn't reproduce. Can you please provide an ODBC trace (make sure that you don't publish sensitive data)?

[danielmarschall]

Thank you for your quick reply!

Attached you find the trace: SQL.LOG (Don't worry, the PKI keys are non-productive)

I don't have knowledge about these ODBC traces, but I found a very interesting spot:

scrap           1dd8-694    EXIT  SQLGetData  with return code 1 (SQL_SUCCESS_WITH_INFO)
        HSTMT               0x10239BE8
        UWORD                        5 
        SWORD                        1 <SQL_C_CHAR>
        PTR                 0x10666300 [     256] "-----BEGIN PUBLIC KEY-----\ d\ aMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCq2GmZ+5kdv1dJ2CP2lkLOk9hp\ d\ a0i3KNzQHsGS2fUIZv9uO1HfGksNJnOXh8IEwqrnHF8EzfMcG3lNDTjUqXQtimgMm\ d\ aSbAMkZWAr73F8QxMqtsFUNlSaIRaZd6dGJlmj0p2/I6UGc7nhpOmQSl4+06AmwUC\ d\ aGGY+MHF1B4Jed2VSCwIDAQAB\ d\ a--\ 0\ 0"
        SQLLEN                   256
        SQLLEN *            0x10670568 (298)

        DIAG [01004] [Microsoft][ODBC SQL Server Driver]Die Zeichenfolgedaten wurden rechts abgeschnitten (0) 

scrap           1dd8-694    ENTER SQLGetData 
        HSTMT               0x10239BE8
        UWORD                        5 
        SWORD                        1 <SQL_C_CHAR>
        PTR                 0x10679300 
        SQLLEN                   256
        SQLLEN *            0x10670568

scrap           1dd8-694    EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
        HSTMT               0x10239BE8
        UWORD                        5 
        SWORD                        1 <SQL_C_CHAR>
        PTR                 0x10679300 [      22] "---END PUBLIC KEY-----"
        SQLLEN                   256
        SQLLEN *            0x10670568 (22)

The wrong output I experienced was indeed at exactly this spot: --\0---END PUBLIC KEY-----

So, it looks to me like the data is read in chunks, and a NULL byte (which might be the C-style string terminating byte of the chunk data?) is copied over.

Also, note that Die Zeichenfolgedaten wurden rechts abgeschnitten is the German error message when you try to insert something into a column that is too small...