msphpsql icon indicating copy to clipboard operation
msphpsql copied to clipboard

Error with parameterized subquery on Always Encrypted connection

Open tmessier opened this issue 5 years ago • 4 comments

PHP Driver version or file name

5.3.0+11108

SQL Server version

13.0.1601.5

Client operating system

Windows 10

PHP version

7.2.11

Microsoft ODBC Driver version

ODBC Driver 17 for SQL Server

Problem description

When using Always Encrypted, running a SELECT query that includes a sub-query with parameters throws an error. I tested running with a sub-query and inlining the parameters, that works fine. I also tried using a subquery without parameters and having a parameter outside of the subquery and that also works fine. So looks like the problem is specific to having parameters inside of the subquery. I also try specifying the parameter type by specifying the parameter like ['value', null, null, SQLSRV_SQLTYPE_NVARCHAR(128)], that didn't fix it either.

Expected behavior and actual behavior

Expected behavior is for the query to run without error. Instead, it returns the following error 42000: "[Microsoft][ODBC Driver 17 for SQL Server]Syntax error, permission violation, or other nonspecific error"

Repro code or steps to reproduce

$conn = sqlsrv_connect('localhost', [
    'Database'             => 'my_db',
    'UID'                  => 'my_user',
    'PWD'                  => 'my_pwd',
    'ReturnDatesAsStrings' => true,
    'CharacterSet'         => 'UTF-8',
    'ColumnEncryption'     => 'Enabled'
]);
sqlsrv_configure('WarningsReturnAsErrors', 1);
$sql = "
    SELECT t1.*
    FROM table1 t1
    WHERE
      t1.fk_id IN (
            SELECT
                t2.field2
            FROM table2 t2
            WHERE t1.fk_id = t2.fk_id
                AND t2.field1 = ?
        )
";
$stmt = sqlsrv_prepare($conn, $sql, ['value']);
if (is_bool($stmt)) {
    var_dump(sqlsrv_errors());
    die;
}
if (!sqlsrv_execute($stmt)) {
    // more code here...
}

tmessier avatar Apr 23 '19 13:04 tmessier

Yes @tmessier this is a known limitation. See related Issue #716.

yitam avatar Apr 23 '19 15:04 yitam

That being said, @tmessier , you can try using sqlsrv_query and make sure you provide the correct SQLSRV_SQLTYPE_* constant

yitam avatar Apr 23 '19 15:04 yitam

Thanks for the link, don't know how I missed that issue with all the searching I did! Sadly, I'm in the same boat as the user in issue #716 , I'm dealing with a large existing application and adding SQLSRV_SQLTYPE_* contants everywhere is not an option. So there is absolutely no plan to find a way to get around this limitation? We can assume this will not get changed/fixed in the foreseeable future?

tmessier avatar Apr 23 '19 16:04 tmessier

@tmessier we leave #716 open and labeled it a feature request mainly because the underlying server call has its own limitations. Depending on the demand, a feature request may be scheduled in the future.

yitam avatar Apr 23 '19 16:04 yitam