0

I am using PHP, IIS with SQL server 2016 as backend. I am using always encrypted feature. for that I am able to select the data in plain text. but not able to insert the data into the table. I am using the code like this.

$queryInsert = "insert into empinfo (EmpID, JobTitle, LoginID, NatID) values 

(?,?,?,?)";
$res = odbc_prepare($connect, $queryInsert);
$result = odbc_execute($res, array($empId, $jobTitle, $loginId, $natId));

I am getting the error like this.

PHP Warning: odbc_prepare(): SQL error: [Microsoft][ODBC Driver 13 for SQL 
Server][SQL Server]Operand type clash: void type is incompatible with 
nvarchar(256) encrypted with (encryption_type = 'DETERMINISTIC', 
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
column_encryption_key_name = 'CEK_Auto1', 
column_encryption_key_database_name = 'Test'), SQL state 22005 in 
SQLDescribeParameter in C:\inetpub\wwwroot\odbctest\odbctest.php on line 22 
PHP Warning: odbc_execute() expects parameter 1 to be resource, boolean 
given in C:\inetpub\wwwroot\odbctest\odbctest.php on line 23

can please anyone tell where am I doing wrong.

Sanjay Yadav
  • 1
  • 1
  • Have you followed the DSN configuration specified here: https://learn.microsoft.com/en-us/sql/connect/odbc/using-always-encrypted-with-the-odbc-driver ? And provisioned the client keys? – David Browne - Microsoft Aug 03 '17 at 13:43
  • Thanks for the prompt responce David. Yes , I configured the DSN as per the article you specified and provisioned the client keys as well and that is working fine as I am able to run the select statement from code. – Sanjay Yadav Aug 03 '17 at 14:08

1 Answers1

0

Please try specifying the parameter types as follows

/* Construct the parameter array. */  
$employeeId = 5;  
$changeDate = "2005-06-07";  
$rate = 30;  
$payFrequency = 2;  
$params1 = array(  
               array($employeeId, null),  
               array($changeDate, null, null, SQLSRV_SQLTYPE_DATETIME),  
               array($rate, null, null, SQLSRV_SQLTYPE_MONEY),  
               array($payFrequency, null, null, SQLSRV_SQLTYPE_TINYINT)

This article explains how to consume always encrypted using ODBC 13.1 driver

Nikhil Vithlani - Microsoft
  • 797
  • 5
  • 13
  • Hi Nikhil, Thanks for the information. but the article provided uses the sqlsrv drivers but [https://blogs.msdn.microsoft.com/sqlphp/2016/07/11/microsoft-drivers-4-0-for-php-for-sql-server-with-php-7-0-support-released/] does not support always encrypt. So I was trying to use the odbc 13.1 – Sanjay Yadav Aug 08 '17 at 16:03
  • I see I will remove the link from my answer then. Were you able to provide the parameter types for the parameters when using odbc driver? You might be able to specify the parameter type in the manner I described above. – Nikhil Vithlani - Microsoft Aug 08 '17 at 20:23
  • No nikhil. I do not see any parameter where I can provide type of parameter in odbc 13.1... Now I am using pdo but that is also not being accepted. – Sanjay Yadav Aug 09 '17 at 05:12
  • Have you looked at this article, https://learn.microsoft.com/en-us/sql/connect/odbc/using-always-encrypted-with-the-odbc-driver – Nikhil Vithlani - Microsoft Aug 10 '17 at 22:48