Msphpsql: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

[Beakerboy]

## PHP Driver version or file name
5.8.0
## SQL Server version
mssql-server-2019 xenial
## Client operating system
Linux and Windows
## PHP version
7.2
## Microsoft ODBC Driver version

## Table schema
CREATE TABLE $prefixed_table (id int NOT NULL PRIMARY KEY, langcode varchar(12), revision_id int, [path] nvarchar(255), [alias] nvarchar(255))
## Problem description
Executing a prepared statement produces the error:
PDOException: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
## Expected behavior and actual behavior
No error
## Repro code

public function testStraightPdo() {
  $prefix = 'test7472525';
  $prefixed_table = $prefix . 'path_alias';
  $create_sql = "CREATE TABLE $prefixed_table (id int NOT NULL PRIMARY KEY, langcode varchar(12), revision_id int, [path] nvarchar(255), [alias] nvarchar(255))";
  $dbh = new \PDO("sqlsrv:Server=localhost;Database=mydrupalsite", "sa", "Password12!");
  $dbh->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
  $sql = "SELECT TOP 1 base_table.revision_id AS revision_id, base_table.id AS id FROM $prefixed_table base_table INNER JOIN $prefixed_table path_alias ON path_alias.id = base_table.id WHERE ( (path_alias.[alias] LIKE :db_condition_placeholder_0 ESCAPE '\') AND (path_alias.langcode = :db_condition_placeholder_1) AND (path_alias.[path] NOT LIKE :db_condition_placeholder_2 ESCAPE '\') )";
  $args = [
    ':db_condition_placeholder_0' => '/kaspuchujawuphabropestistutrudewruphogudraguphespofrethafrubrumelibrathocrunelistemehiswucepherabradup',
    ':db_condition_placeholder_1' => 'zxx',
    ':db_condition_placeholder_2' => '/<front>',
  ];
  $dbh->exec($create_sql);
  $sth = $dbh->prepare($sql);
  try {
    $sth->execute($args);
  }
  catch(\Exception $e) {
    fwrite(STDOUT, $sql);
    fwrite(STDOUT, print_r($args, TRUE));
    throw $e;
  }
}

[yitam]

Hi @Beakerboy

Thanks for the report. We will investigate and get back to you on this. It would certainly help if you can tell us what typical data you have in the base_table.

[Beakerboy]

Do I need data in the table for the query to execute without an exception? This is from the Drupal Kernel Test Suite. In its case, the table has several other columns above the three I defined in this simplified demonstration case. I could inject a listener to grab data values as they are inserted into this table if necessary.

[yitam]

In order to reproduce or investigate it would help if we have an idea what data typically resides in the table, or is the table normally empty? We don't need all the columns, but only those you have used in your repro script.

[Beakerboy]

It looks like in both the “official” test and my proof of concept, the table is empty.

As far as typical values, here is the one value in my production database:
id = 4
revision_id = 4
langcode = 'en'
path = '/node/3'
alias = '/'

[yitam]

That's good enough for now! Thanks @Beakerboy

[yitam]

HI @Beakerboy

For your information, pdo_sqlsrv calls PDO's pdo_parse_params to parse the named parameters when preparing the statement, which supposed to replace all the named parameters with question marks.

However, this PDO function seemingly stops parsing the rest of the query when it first hits the keyword ESCAPE, thus only managed to find the first named parameter.

You can try modifying your query as below:

1. remove the ESCAPE clauses, or
2. keep the ESCAPE clauses but use positional parameters (?) instead

Meanwhile, I'll keep investigating whether the ESCAPE clauses were the cause

[Beakerboy]

Thanks.
This is a simplified version of a unit test from the Drupal CMS. Drupal uses named parameters throughout, and since mysql, postgresql, and sqlite all use a single slash as their escape character, I was able to fix a lot of test failures by using the SQL Server feature of specifying it in the clause.

I'll see If I can get around the escape character, but named parameters are a must. It certainly seems like a failure somewhere if the keyword "ESCAPE" halts parameter parsing in PDO.

[yitam]

Thanks @Beakerboy
As I said I would continue investigating but this section on online docs might give you some insights.

[Beakerboy]

For some additional context; The SQLite Database also appends ESCAPE '\' to its LIKE statements, yet it passes the Drupal version of this test while also relying on PDO.

[yitam]

@Beakerboy thanks for the additional input, and I just double checked, pdo_sqlite does not call PDO function pdo_parse_params like pdo_sqlsrv.

This PDO function relies on scan(), which seemingly skips the backslashes in your query.

To prove my point, try replacing your '\' with something like '!' or '[\]' in your t-sql statement

[Beakerboy]

I’m in the process of submitting a patch file to Drupal core to remove the requirements for backslash escapes. Regardless, this seems like a bug. Are you going to submit it to the PDO crew yourself, or should I? I imagine you have more clout than I do, but maybe you consider it a non-issue.

[yitam]

Yes @Beakerboy the _problem_ resides in the parsing code, which basically ignores everything from the first backslash to the next backslash. That is, in between the two ESCAPE clauses, the two named placeholders were ignored/skipped.

I'll do some more investigation however, to make sure this is actually a bug, not by design ;)

[yitam]

@Beakerboy FYI, I just filed a report for PHP PDO.

[yitam]

fyi, @Beakerboy, there's an update to the aforementioned bug report. Basically, it was meant to fix some bugs to support MySQL's proprietary backslash escaping, thus sacrificing some compatibility with standard SQL.

[Beakerboy]

@yitam feel free to close this issue if you are not planning on changing things to the same approach that sqlite took. I'll continue to watch the PDO bug tracker. I was able to get around this by intercepting any backslash-escaped LIKE parameters and using strtr() to convert them to sqlsrv-style bracket-enclosed escaping, all behind the scenes,

[yitam]

@Beakerboy, glad to hear you found a workaround behind the scenes. As you can probably see, not using PDO API pdo_parse_params means a redesign of how we handle named parameters, which is not trivial. We will keep this issue open and continue to watch the bug report.

[yitam]

Hi @Beakerboy , we will close this issue now as it's unlikely we will change our current design to work around this special case (which will be fixed by PHP PDO, eventually). Good luck to your work :)