Field level encryption - can't query on encrypted field

[Steven]

Using LiteDAC 2.2.6 Professional with Delphi Xe5, compiling as 32 VCL application.

Followed the code examples for encrypting a field and still can't run a query where the encrypted field is used in the "where" part of the SQL statement.
Let's ignore my code for the moment and focus just on the LiteDAC components first.

If I use TLiteConnections, TLiteEncryptor & TLiteQuery and link them up.
In TLiteEncryptor set
DatatHeader: ehTagandHash
EncryptionsAlgorithm: eaBlowfish
HashAlgorithm: haSHA1
Password: 'A&TESTtest~Ͽ' (quotes not included)

TLiteQuery has a variety of the fields (PASSWORD, PASSWORD_OLD, & a number of others).
ltqryUser.Encryption.Fields := 'PASSWORD';
ltqryUser.DataTypeMap.AddFieldNameRule('PASSWORD', ftString);

If I double click on the TLiteQuery component and enter this query under the SQL tab.

Code: Select all

SELECT User.*, ALevelType.Descrip, ALevelType.Detail
FROM User, ALevelType
WHERE User.LoginName="quatro" AND User.Password_OLD="four" AND User.AL_ID = ALevelType.AL_ID

When I click on the Data Editor button in the open TLiteQuery window under the SQL code one record found is found.
This is as it should be.
The PASSWORD encrypted field/column shows in the TLiteQuery Data Editor as plain text (i.e. it's being decrypted)

When I try to run the query using the encrypted PASSWORD field

Code: Select all

SELECT User.*, ALevelType.Descrip, ALevelType.Detail
FROM User, ALevelType
WHERE User.LoginName="quatro" AND User.Password="four" AND User.AL_ID = ALevelType.AL_ID

When I click on the Data Editor NO records are found.

This mirrors the results I'm getting via code in my app.
The only difference in the 2 SQL statements are the words PASSWORD_OLD vs PASSWORD.

The PASSWORD_OLD is setup as VARCHAR field 128 in length.
The PASSWORD field (per the Help specifications) is setup as VARBINARY of 256 in length (2x the length of the original).

So I am puzzled and for the moment stuck.
Do I need to alter my SQL statement in someway to make this work?

[AlexP]

Hello,

Encrypted fields can't be used in a WHERE clause. For search by such fields, a local filter can be used.