How to search by multiple values in "Always Encrypted" column?

Question

If I am using deterministic encryption I am able to perform searches in encrypted column. For example:

DECLARE @email NVARCHAR(222) = 'test';

SELECT *
FROM Users
Where email = @email;

But how to perform searches by multiple values? For example, if I want to get records where email addresses are userA@gmail.com, userB@gmail.com and userC@gmail.com.

I can't create many input parameters because usually the values are dynamic and passed as CSV string, for example.

From what I can see, you would need to either have multiple parameters or handle this in your application, ie: fire multiple queries, then combine the results and handle any duplication outside SQL Server — iamdave, Mar 02 '18 at 10:38
@iamdave In both cases this is not efficient. Having multiple parameters (i may need 5 or I may need 1000). Retiring all records to the client application and filtering there, again is not good as huge amount of data is passed between the SQL Server and the application. But, even I do not like this, it seems I have no choice. — gotqn, Mar 02 '18 at 10:51
all I can think of is parsing the csv within a while structure and search one email at a time — Jayvee, Mar 02 '18 at 15:37

score 0 · Answer 1 · answered Mar 02 '18 at 13:33

In you situation, I would recommend splitting if it suits you.

Try this query: Let me know if it works for you

DECLARE @email NVARCHAR(222) = 'userA@gmail.com,userB@gmail.com,userC@gmail.com'
DECLARE @Delimiter NCHAR(1) = ','

DECLARE @EmailParam TABLE(email NVARCHAR(222))

;WITH Split(StPos,EndPos)
AS(
    SELECT 0 AS StPos, CHARINDEX(@Delimiter,@email) AS EndPos
    UNION ALL
    SELECT EndPos+1, CHARINDEX(@Delimiter,@email,EndPos+1)
    FROM Split
    WHERE EndPos > 0
)

INSERT INTO @EmailParam (email)
SELECT SUBSTRING(@email,StPos,COALESCE(NULLIF(EndPos,0),LEN(@email)+1)-StPos) FROM Split

SELECT Users.*
FROM Users
INNER JOIN @EmailParam EmailParam
ON Users.email = EmailParam.email;

Explanation:

I have just extended your query to deal with multiple emails.
As you mentioned if are sure about input format being comma separated string, it might worth trying to derive table variable from it.
So If your input is like:

DECLARE @email NVARCHAR(222) = 'userA@gmail.com,userB@gmail.com,userC@gmail.com'

You can split it using any tsql technique, I have used common table expression here.
Then once you have it in tabular format. I have got it in table variable named @EmailParam here. You can then easily use add a join and filter it.
if your query works with deterministic encryption, then this query may work in the similar way as only additional change in regard to filer is we have inner join now. ,

@gotqn - Let me know if this stands again with your deterministic encryption. — Bhavin Gosai, Mar 02 '18 at 13:38
@gotqn - does your script work? `SELECT * FROM Users Where email = @email;` — Bhavin Gosai, Mar 02 '18 at 19:33
Sure, it works. Because I have enabled always encrypted parameterization (https://blogs.msdn.microsoft.com/sqlsecurity/2016/12/13/parameterization-for-always-encrypted-using-ssms-to-insert-into-update-and-filter-by-encrypted-columns/) — gotqn, Mar 02 '18 at 20:02
@BhavinGosai: your code will only work for fairly small sets of data. Do you happen to have code that might work for large sets of data, e.g. 3000 data items, maybe 7 characters max each (so a comma separated string of the values is only <24,000 characters, much to large for varchar, and the recursion is not reasonable)? That is, for the "you can split it using any tsql technique", it requires a different technique than what you used... Thanks. — David I. McIntosh, Apr 08 '22 at 20:41

How to search by multiple values in "Always Encrypted" column?

1 Answers1