Field level encryption - can't query on encrypted field

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Steven
Posts: 30
Joined: Fri 06 Dec 2013 19:48

Field level encryption - can't query on encrypted field

Post by Steven » Tue 18 Mar 2014 09:09

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Field level encryption - can't query on encrypted field

Post by AlexP » Wed 19 Mar 2014 14:04

Hello,

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

Post Reply