PDO_DBLIB encodes passed binary data as a string, which is fragile

Description

A user reported an issue to me where they're trying to insert binary data (a PNG file, bound as a PARAM_LOB, inserting into a varbinary column) into an SQL Server database, and unexpectedly get a syntax error back.

It looks like PDO_DBLIB doesn't actually implement binding (no param hook is implemented), so it relies on PDO to do substitution and send the fully substituted query string back to the server (I assume this is a FreeTDS, or TDS protocol limitation. It's been a long, long time since I touched SQL Server...), instead of sending the query and parameters separate. The PDO parsing defers to the driver's quoter to turn the value into a string, escaping things like ' and \. However, it seems this isn't sufficient with binary data that could have things like nulls in the middle of the string.

The following code:

<?php
try {
    $image1 = base64_decode("..."); // a PNG image in this case
    $pdo = new PDO("dblib:host=SQLTEST;dbname=test", "sa", "...");
    if ($pdo) {
        $sql = "insert into dbo.TicketImage (TicketID, Image1, Image2) VALUES (168, ?, NULL)";
        $stmt = $pdo->prepare($sql);
        $stmt->bindParam(1, $image1, PDO::PARAM_LOB);
        $stmt->execute();
    }
} catch (PDOException $e) {
    var_dump($e);
}

Resulted in this output:

object(PDOException)#3 (8) {
  ["message":protected]=>
  string(165) "SQLSTATE[HY000]: General error: 20018 Incorrect syntax near ''. [20018] (severity 15) [insert into dbo.TicketImage (TicketID, Image1, Image2) VALUES (168, '�PNG
�
]"
  ["string":"Exception":private]=>
  string(0) ""
  ["code":protected]=>
  string(5) "HY000"
  ["file":protected]=>
  string(59) "/home/calvin/src/sqlserver/sql server binary blob issue.php"
  ["line":protected]=>
  int(16)
  ["trace":"Exception":private]=>
  array(1) {
    [0]=>
    array(5) {
      ["file"]=>
      string(59) "/home/calvin/src/sqlserver/sql server binary blob issue.php"
      ["line"]=>
      int(16)
      ["function"]=>
      string(7) "execute"
      ["class"]=>
      string(12) "PDOStatement"
      ["type"]=>
      string(2) "->"
    }
  }
  ["previous":"Exception":private]=>
  NULL
  ["errorInfo"]=>
  array(5) {
    [0]=>
    string(5) "HY000"
    [1]=>
    int(20018)
    [2]=>
    string(127) "Incorrect syntax near ''. [20018] (severity 15) [insert into dbo.TicketImage (TicketID, Image1, Image2) VALUES (168, '�PNG
�
]"
    [3]=>
    int(-1)
    [4]=>
    int(15)
  }
}

But I expected this output instead:

// success...

Other details

SQL Server version:

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) 
	Mar 18 2018 09:11:49 
	Copyright (c) Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

FreeTDS info:

Compile-time settings (established with the "configure" script)
                            Version: freetds v1.3.3
             freetds.conf directory: /etc
     MS db-lib source compatibility: yes
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: auto
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes
                            OpenSSL: no
                             GnuTLS: yes
                               MARS: yes

The provided table:

CREATE TABLE [dbo].[TicketImage](
       [TicketID] [int] NOT NULL,
       [Image1] [varbinary](max) NULL,
       [Image2] [varbinary](max) NULL,
CONSTRAINT [PK_TicketImage] PRIMARY KEY CLUSTERED
(
       [TicketID] 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

PHP Version

PHP 8.1.13

Operating System

Fedora 37

Bắt đầu bằng cách lần theo việc xử lý tham số của PDO_DBLIB và đường dẫn thay thế PDO được mô tả trong báo cáo, bao gồm cách quoter của driver xử lý các giá trị PARAM_LOB. Kiểm tra xem giới hạn của FreeTDS/TDS có ảnh hưởng đến cách tiếp cận hiện có hay không. Công việc được xem là hoàn tất khi ví dụ PNG nhị phân được cung cấp chèn vào cột varbinary mà không có lỗi cú pháp và đã xác định được phạm vi bao phủ hồi quy phù hợp.