Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error with parameterized subquery on Always Encrypted connection #976

Open
tmessier opened this issue Apr 23, 2019 · 4 comments
Open

Error with parameterized subquery on Always Encrypted connection #976

tmessier opened this issue Apr 23, 2019 · 4 comments

Comments

@tmessier
Copy link

tmessier commented Apr 23, 2019

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...
}
@yitam
Copy link
Contributor

yitam commented Apr 23, 2019

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

@yitam
Copy link
Contributor

yitam commented Apr 23, 2019

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

@tmessier
Copy link
Author

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?

@yitam
Copy link
Contributor

yitam commented Apr 23, 2019

@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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants