Как выбрать значение параметра хранимой процедуры из всегда зашифрованного столбца

Question

Я зашифровал один столбец mob_no в таблице. после шифрования столбца хранимая процедура получает ошибку.

Я добавил SP ниже

create procedure get_cut

@mobNo varchar(50),

@custId int 

As 

Begin

if(@mobNo = null or @mobNo = '')

Begin

    select @mobNo = mob_no  
    from table1 where cust_id = @custId



End

select cust_name from tbl_cust where mob_no = @mobNo and cust_id = @custId


End

При запуске этого зр я получил ошибку ниже

Сообщение 33299, Несоответствие схемы шифрования для столбцов / переменных '@mobNo'. Схема шифрования для столбцов / переменных: (encryption_type = 'PLAINTEXT'), и выражение рядом со строкой '9' ожидает, что оно будет (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_xx_name_name_name_name) column_en mydb') (или слабее).

Answer 1

Следующее условие равенства

 @mobNo = '' 

в вашей хранимой процедуре будет оцениваться на сервере. Поскольку вы используете Always Encrypted, столбец, соответствующий mobNo, сохраняется на сервере в виде двоичных данных. сервер не сможет сравнивать значения в базе данных с '' потому что сервер не знает зашифрованное двоичное значение, соответствующее ''

Вы можете изменить хранимую процедуру так, чтобы она принимала 3 аргумента вместо 2 следующим образом:

create procedure get_cut
@mobNo varchar(50),    
@custId int,
@emptyString varchar(50)
...
if(@mobNo is null or @mobNo = @emptyString)
...

Замечания: is null вместо = null

Вы можете передать значение '' как

• параметр вашего SqlCommand, если вы используете приложение для выполнения хранимой процедуры или
• если вы используете Sql Server Management Studio для выполнения вашей хранимой процедуры, посмотрите Параметризация для Always Encrypted, чтобы понять, как отправлять незашифрованные значения для зашифрованных столбцов

Как пример, из SSMS (Requires at least SSMS version 17.0.) Вы можете сделать следующее:

Declare @emptyStringVar varchar(50) = ''
Declare @mobNoVar varchar(50) = '000-000-0000'
EXEC get_cut 
@mobNo varchar = @mobNoVar  
@custId int = 1234
@emptyString @emptyStringVar