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

Calling stored procedure with XML parameter does not work with Column Encryption enabled #674

Open
v-kaywon opened this issue Jan 29, 2018 · 0 comments

Comments

Projects
None yet
1 participant
@v-kaywon
Copy link
Member

commented Jan 29, 2018

Driver version or file name

5.2.0-RC

SQL Server version

SQL Server 2016

Client operating system

Windows 10

PHP version

7.2.1

Microsoft ODBC Driver version

17.0.1.1

Table schema

(c1 int encrypted, c2 xml not encrypted)

Problem description

Calling stored procedure with XML parameter does not work with Column Encryption enabled even if the xml column is not encrypted

Expected behavior and actual behavior

The stored procedure should be invoked properly

Repro code

$conn = sqlsrv_connect($server, array("Database" => $databaseName, "UID" => $username, "PWD" => $password, "ColumnEncryption" => "Enabled"));

$tableName = 'testDataTypes_GH231_VC';
$createSql = "CREATE TABLE $tableName ( [c1] int ENCRYPTED WITH (ENCRYPTION_TYPE = deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = AEColumnKey) , [c2] xml )";
sqlsrv_query($conn, $createSql);

$sql = "INSERT INTO [$tableName] (c1, c2) VALUES (?, ?)";
$data = "<XmlTestData><Letters1>The quick brown fox jumps over the lazy dog</Letters1><Digits1>0123456789</Digits1></XmlTestData>";
$params = array(8, array($data, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR), SQLSRV_SQLTYPE_XML));
$stmt = sqlsrv_prepare($conn, $sql, $params);
sqlsrv_execute($stmt);

$procName = "testBindOutSp";
$createProc = "CREATE PROC $procName (@p1 int, @p2 xml OUTPUT) AS BEGIN SET @p2 = (SELECT c2 FROM $tableName WHERE c1 = @p1)";
sqlsrv_query($conn, $createProc);

$callProc = "{ CALL [$procName] (?, ?)}";
$callResult = "ShortString";
$params = array(array(8, SQLSRV_PARAM_IN), array(&$callResult, SQLSRV_PARAM_OUT, null, SQLSRV_SQLTYPE_XML));
$stmt = sqlsrv_prepare($conn, $callProc, $params);
sqlsrv_execute($stmt);

var_dump($callResult);

sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);

@v-kaywon v-kaywon changed the title Calling sotred procedure with XML parameter does not work with Column Encryption enabled Calling stored procedure with XML parameter does not work with Column Encryption enabled Jan 29, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.