Quering a Always Encrypted Column with a already encrypted value

Question

Quering a Always Encrypted Column with a already encrypted value

christian_t

Hall of Fame

Points: 3588
More actions
March 7, 2019 at 3:52 am

#416749

Hi there,
what I would like to do, is to query rows that have a specific encrypted value in the encrypted column.
so what you usually do:
DECLARE @value nvarchar(255) = N'value';
SELECT id FROM table WHERE column = @value
I am not interested in decrypting the rows in this szenario I just want to select the rows that match. (and do someting later)
What I would rather like to do is to use a binary-Value in the WHERE clause that should be matched.
SELECT [rows] FROM table WHERE encrypted_column = 0x234721348213894
I guess a solution would be to create a table with that specific encrypted value as row and do a inner join, but I would rather have it directly in the where-clause
Any suggestions?
Cheers

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

If your columns have been set with deterministic always encrypted, then the provider does that for you automatically.
In SQL Server Management Studio or in your regular connection string, you add Column Encryption Setting=Enabled

when that is in place, when you execute a call like this, it encrypts the value for the comparison. the varchar parameter in the example below gets converted to the encryption value for the comparison:

DECLARE @SSN NCHAR(11) = '795-73-9838'SELECT * FROM [dbo].[Patients]WHERE [SSN] = @SSN

you cannot do an inline comparison, it has to be parameterized, so ='111-11-1111' will not work.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

christian_t Hall of Fame Points: 3588 More actions · Answer 2

Hi Lowel,

thanks for your reply. I am actually not using SSMS. This is a task in an ETL (dtsx-Package).
I am using OLEDB-Source for that specify task. To use your solution I would need to switch to a ODBC/ADO Source.
So to get this straight: even I already know the encrypted varbinary-Value I would like to search for I cannot use it in my query!

Christian

Lowell SSC Guru Points: 323518 More actions · Answer 3

if you have the actual encrypted varbinary value to compare it to, then yes, i believe it will work.
if you needed to convert a known value to an encrypted value for the comparison, then you need the provider, with the connection string modification,as I think it is using .Net 4.62 libraries for the encryption methodology.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

If you're talking about SQL Server column-level encryption then no, it won't work. Encrypting a single value, say a credit card number, will result in any one of a very large number of encrypted values. It's not one to one. So to find a single encrypted credit card number in a table, you would have to compare it with all of the permutations from encrypting your search value.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

christian_t Hall of Fame Points: 3588 More actions · Answer 5

currently this encrypted deterministic, so same value = same varbinary.
If it is possible, what would be the syntax for that?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

christian_t - Thursday, March 7, 2019 6:14 AM
currently this encrypted deterministic, so same value = same varbinary.
If it is possible, what would be the syntax for that?

Isn't it as simple as this?

SELECT * FROM MyTable WHERE BinaryValue = CAST(Something AS VARBINARY(nn))

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

christian_t Hall of Fame Points: 3588 More actions · Answer 7

actually no,

if you run it like this
DECLARE @value varbinary(8000/max) = 0x123456789 SELECT * FROM table WHERE encrypted_column = @value

OR

SELECT * FROM table WHERE encrypted_column = 0x0123456789
you get this error message
Operand type clash: varbinary is incompatible with varbinary(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_...', column_encryption_key_name = 'column_key_name', column_encryption_key_database_name = 'database_key_name')