AlwaysEncrypted input column in PreparedStatement type clash

[kokhoor]

I am using msnodesqlv8 4.2.1 with mssql 11.0.1

When using AlwaysEncrypted input column in PreparedStatement, the parameter seems to not be sent with a size. The column in database is nvarchar(128), and following is my code:

const ps = new sql.PreparedStatement(pool)
ps.input('name', sql.NVarChar(128));
await ps.prepare('select TOP 10 name from tbl_emp WHERE name=@name');
result = await ps.execute({ name: 'test' });

The error I received is:

[Error: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Operand type clash: nvarchar is incompatible with nvarchar(128) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'XXX', column_encryption_key_database_name = 'XXX')]

This is on Linux msodbcsql18.

Redacted Error:
at handleError (/opt/nodeprojects/msnodesql/node_modules/mssql/lib/msnodesqlv8/request.js:276:21)
at StreamEvents.emit (node:events:517:28)
at StreamEvents.emit (node:domain:489:12)
at /opt/nodeprojects/msnodesql/node_modules/msnodesqlv8/lib/reader.js:83:20
at Array.forEach ()
at Query.routeStatementError (/opt/nodeprojects/msnodesql/node_modules/msnodesqlv8/lib/reader.js:73:19)
at /opt/nodeprojects/msnodesql/node_modules/msnodesqlv8/lib/reader.js:239:12
at /opt/nodeprojects/msnodesql/node_modules/msnodesqlv8/lib/driver.js:191:13
at Immediate. (/opt/nodeprojects/msnodesql/node_modules/msnodesqlv8/lib/driver.js:167:9)
at process.processImmediate (node:internal/timers:476:21) {
code: 'EREQUEST'
sqlstate: '22018',
code: 206,
severity: 16,

[TimelordUK]

It might work with a stored proc wrapper I am not sure if that meta data from mssql is even being mapped in msnodesqlv8 certainly I never tested always on encryption with mssql my assumption was the library would be used natively.  You could write equivalent code without using mssql and explicitly set the column metadata else a stored proc the library will map parameters precisely I’m sorry but the library is now in maintenance I won’t be fixing anything unless it’s absolutely critical I simply do not have time anymore to spend. Sent from my iPhoneOn 9 Oct 2024, at 01:31, kokhoor ***@***.***> wrote: I am using msnodesqlv8 4.2.1 with mssql 11.0.1 When using AlwaysEncrypted input column in PreparedStatement, the parameter seems to not be sent with a size. The column in database is nvarchar(128), and following is my code: const ps = new sql.PreparedStatement(pool) ps.input('name', sql.NVarChar(128)); await ps.prepare('select TOP 10 name from tbl_emp WHERE ***@***.***'); result = await ps.execute({ name: 'test' }); The error I received is: [Error: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Operand type clash: nvarchar is incompatible with nvarchar(128) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'XXX', column_encryption_key_database_name = 'XXX')] This is on Linux msodbcsql18. Redacted Error: at handleError (/opt/nodeprojects/msnodesql/node_modules/mssql/lib/msnodesqlv8/request.js:276:21) at StreamEvents.emit (node:events:517:28) at StreamEvents.emit (node:domain:489:12) at /opt/nodeprojects/msnodesql/node_modules/msnodesqlv8/lib/reader.js:83:20 at Array.forEach () at Query.routeStatementError (/opt/nodeprojects/msnodesql/node_modules/msnodesqlv8/lib/reader.js:73:19) at /opt/nodeprojects/msnodesql/node_modules/msnodesqlv8/lib/reader.js:239:12 at /opt/nodeprojects/msnodesql/node_modules/msnodesqlv8/lib/driver.js:191:13 at Immediate. (/opt/nodeprojects/msnodesql/node_modules/msnodesqlv8/lib/driver.js:167:9) at process.processImmediate (node:internal/timers:476:21) { code: 'EREQUEST' sqlstate: '22018', code: 206, severity: 16, —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>