msphpsql
msphpsql copied to clipboard
Error with parameterized subquery on Always Encrypted connection
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...
}
Yes @tmessier this is a known limitation. See related Issue #716.
That being said, @tmessier , you can try using sqlsrv_query and make sure you provide the correct SQLSRV_SQLTYPE_* constant
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 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.