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

[NattyNarwhal]

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

[NattyNarwhal]

Two ideas:

1. The quoter could escape things like nulls and other characters that may cause trouble in a string literal. I don't know what needs to be escaped, how to escape them, or if it's i.e. locale sensitive. It might also blow the string escaping complexity logic up.
2. If the user has bound the data as a LOB (or perhaps say a PARAM_BINARY type), presume that it's binary data, and instead of escaping it as a string, escape it as a binary literal (0x and the hex bytes). This works because the parsing in PDO defers quoting to the driver and plops that right in where the string would have gone instead. I've included a diff for a very simple implementation of this approach which seems to resolve the issue for me. If it makes sense, I can create a PR and clean up the code.

For both changes, I'm not sure what the impact would be.

diff --git a/ext/pdo_dblib/dblib_driver.c b/ext/pdo_dblib/dblib_driver.c
index 02ec2466a0..956b5a27c5 100644
--- a/ext/pdo_dblib/dblib_driver.c
+++ b/ext/pdo_dblib/dblib_driver.c
@@ -145,7 +145,7 @@ static zend_long dblib_handle_doer(pdo_dbh_t *dbh, const zend_string *sql)
 static zend_string* dblib_handle_quoter(pdo_dbh_t *dbh, const zend_string *unquoted, enum pdo_param_type paramtype)
 {
 	pdo_dblib_db_handle *H = (pdo_dblib_db_handle *)dbh->driver_data;
-	bool use_national_character_set = 0;
+	bool use_national_character_set = 0, is_binary = false;
 	size_t i;
 	char *q;
 	size_t quotedlen = 0;
@@ -160,6 +160,29 @@ static zend_string* dblib_handle_quoter(pdo_dbh_t *dbh, const zend_string *unquo
 	if ((paramtype & PDO_PARAM_STR_CHAR) == PDO_PARAM_STR_CHAR) {
 		use_national_character_set = 0;
 	}
+	/*
+	 * A user could be passing a binary (i.e. an image file) in a query.
+	 * It's fragile trying to escape it as a string, so encode it as a
+	 * binary literal instead.
+	 */
+	if (paramtype == PDO_PARAM_LOB) {
+		is_binary = true;
+	}
+
+	if (is_binary) {
+		/* 1 char = 2 chars in hex, plus 0x */
+		quotedlen = ZSTR_LEN(unquoted) * 2; /* XXX: Overflow? */
+		quotedlen += 2;
+
+		quoted_str = zend_string_alloc(quotedlen, 0);
+		q = ZSTR_VAL(quoted_str);
+		*q++ = '0';
+		*q++ = 'x';
+		for (i = 0; i < ZSTR_LEN(unquoted); i++) {
+			q += sprintf(q, "%02X", (unsigned char)ZSTR_VAL(unquoted)[i]);
+		}
+		return quoted_str;
+	}
 
 	/* Detect quoted length, adding extra char for doubled single quotes */
 	for (i = 0; i < ZSTR_LEN(unquoted); i++) {

[NattyNarwhal]

cc @cmb69 since he probably knows the most about SQL Server here

[adambaratz]

This has been a long-standing behavior of this extension. It's been a while since I looked at this part of the code, but I think it might be a FreeTDS limitation. I'd suggest trying an ODBC driver and extension if you need this functionality. Microsoft has their own Linux driver which I'd recommend checking out, been available for a number of years now.

[cmb69]

cc @cmb69 since he probably knows the most about SQL Server here

Well, maybe, but I've never used (or had a closer look at) FreeTDS or pdo_dblib.

Microsoft has their own Linux driver which I'd recommend checking out, been available for a number of years now.

Right. Maybe we should amend the existing documentation to not only recommend that driver on Windows:

On Windows, you should use SqlSrv, an alternative driver for MS SQL is available from Microsoft: » http://msdn.microsoft.com/en-us/sqlserver/ff657782.aspx .

Anyhow, would that be an option for that user?

2. If it makes sense, I can create a PR and clean up the code.

I think that this makes sense, unless we classify this as WONTFIX (in which case we should improve the documentation).

[NattyNarwhal]

ODBC, SqlSrv

The platform the user is running on (IBM i) doesn't have Microsoft's ODBC driver or PHP extension available, so FreeTDS is the only option.

I think that this makes sense, unless we classify this as WONTFIX (in which case we should improve the documentation).

Makes sense. It does change the behaviour, but the previous behaviour seemed pretty broken for the context anyways.