Field level encryption - can't query on encrypted field
Posted: 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.
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
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?
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
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_IDThis 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?