3

I am experiencing some unexpected results when querying a on a text field that is using Case Sensitive Deterministic Encryption.

The field can contain dashes - in the string, when I use an exact match in WHERE CLAUSE of a SOQL query no results are returned

Example (Imagine I have two Account records, and each have custom fields with values as follows):

Account.Encrypted_Field__c = 123-45-6789

Account.Encrypted_Field__c = 123456789

SELECT Encrypted_Field__c FROM Account WHERE Encrypted_Field__c = '123456789'

Returns 1 row.

SELECT Encrypted_Field__c FROM Account WHERE Encrypted_Field__c = '123-45-6789'

Returns 0 rows.

Example Code:

List<Account> acc = [SELECT Id,Encrypted_Field__c FROM Account where id = '...'];
acc[0].Encrypted_Field__c = '862-52-1771';
update acc[0];

acc.clear();
acc = [SELECT Id,Encrypted_Field__c FROM Account where Encrypted_Field__c = '862-52-1771'];
System.assertEquals(1, acc.size());

Fails. No results returned.

But the following does work (SOSL):

List<Account> acc = [SELECT Id,SSN__c FROM Account where id = '...'];
acc[0].Encrypted_Field__c = '862-52-1771';
update acc[0];

List<List<sObject>> accounts = [FIND '862-52-1771' IN ALL FIELDS Returning Account ];
System.assertEquals(1, accounts[0].size());

The documentation of limitations mentions that I need to 'exact' filters on the SOQL queries, but doesn't mention anything about "special characters" (that I've found so far.

As a test I created another text field the same length ( but not encrypted ) and ran the following code which passed:

List<Account> acc = [SELECT Id,Encrypted_Field_Test__c FROM Account where id = '...'];
acc[0].Encrypted_Field_Test__c = '862-52-1771';
update acc[0];

acc.clear();
acc = [SELECT Id,Encrypted_Field_Test__c FROM Account where Encrypted_Field_Test__c = '862-52-1771'];
System.assertEquals(1, acc.size());
3

You are comparing apples and oranges... either Em to En Dash, and/or hyphens - not the same thing at all.

To illustrate... this code snippet will return false if you drop it into execute anonymous:

System.debug('123-45-6789'=='123–45–6789');

But it is almost impossible to discern the difference with the naked eye. A final consideration to watch out for: your text editor or IDE or browser may also be auto-correcting to En or Em or hyphen without you noticing... usually this is a configurable setting.

Edit
I am certain this is an Apples and Oranges issue. Steps I have taken to prove this out:
1. Created a Text(255) Deterministically Encrypted Field on Account, API name Encrypted_Field_Test__c.
2. Carefully (via the GUI) populated three records with three variants of Social Security Number, using Hyphen, En Dash, and Em Dash.
3. Ran the following code in Execute Anonymous, native DE in Chrome 71.

Note: the point of the below code was to narrowly and painstakingly focus on the issue at hand: comparing Hyphens, En Dashes, and Em Dashes in the context of Deterministically Encrypted Fields.

Map<Id,Account> acctMap = new Map<Id,Account>([select id, Encrypted_Field_Test__c, Site from account where (name like '%800%' OR name like '%801%' OR name like '%802%')]);
Set<Id> keysInScope = new Set<Id>(acctMap.keySet());
System.assertEquals(3,keysInScope.size());

String hyphen = '123-45-6789'; //can't speak to what your eyes see... or what your IDE or Text Editor or Browser does to this Hyphen... but in my org and my code this is truly a Hyphen
String enDash = '123–45–6789'; //can't speak to what your eyes see... or what your IDE or Text Editor or Browser does to this En Dash... but in my org and my code this is truly an En Dash
String emDash = '123—45—6789'; //can't speak to what your eyes see... or what your IDE or Text Editor or Browser does to this Em Dash... but in my org and my code this is truly an Em Dash

System.assertNotEquals(hyphen,enDash);
System.assertNotEquals(hyphen,emDash);
System.assertNotEquals(enDash,emDash);

List<Account> acctListToUpdate = new List<Account>();
for(Account acct : acctMap.values()) {
    switch on acct.site {
        when 'Hyphen' {
            acct.Encrypted_Field_Test__c = hyphen;
        }
        when 'En Dash' {
            acct.Encrypted_Field_Test__c = enDash;
        }
        when 'Em Dash' {
            acct.Encrypted_Field_Test__c = emDash;
        }
    }
    acctListToUpdate.add(acct);
}
update acctListToUpdate;

acctMap.clear();
System.assertEquals(3,keysInScope.size());

acctMap = new Map<Id,Account>([select id, Encrypted_Field_Test__c, Site from account where Id IN: keysInScope]);
for(Account acct : acctMap.values()) {
    switch on acct.site {
        when 'Hyphen' {
            System.assertEquals(hyphen, acct.Encrypted_Field_Test__c);
        }
        when 'En Dash' {
            System.assertEquals(enDash, acct.Encrypted_Field_Test__c);
        }
        when 'Em Dash' {
            System.assertEquals(emDash, acct.Encrypted_Field_Test__c);
        }
    }
}
  • I tried a couple of different combinations. I believe they are all hyphens, and there shouldn't be any auto-correct. I'm using the developer console in chrome (71) and copy/pasting results of the open query into the editor. I also tried searching with Em, En, and hyphens and none return results. – jordan.baucke Jan 11 at 7:04
  • 2
    Also tried updating "-" (Hyphen) to "—" (Em dash) on the record. We have a workflow rule that rejects saving of records with em' dashes. – jordan.baucke Jan 11 at 7:10
  • As a simple elimination check, @jordan.baucke, can you replicate the same behavior with the same code on a non-Platform Encrypted field? – David Reed Jan 11 at 17:38
  • 1
    @David Reed - yep created a non-encrypted field ran the same test ... got results. – jordan.baucke Jan 11 at 18:40
  • @jordan.baucke I still think that some how, some way... you are facing the Em vs En vs Hyphen situation. I just ran this SOQL and I get a result just fine: select id, Encrypted_Field_Test__c from account where Encrypted_Field_Test__c = '123-45-6789'. The custom field in my DE is also case sensitive deterministic encryption field. – krigi Jan 11 at 19:04
1

As of today the issue appears to be resolved. This was a bug in Spring '19 Release. Thanks to everyone for suggestions, issue was intermittent and as such hard to isolate, but never-the-less acknowledged and patched after opening a ticket with Salesforce.

  • 1
    this would have driven me bonkers (we have Shield as well) so I appreciate the "trailblazing" – cropredy Jan 17 at 1:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.