krrik February 2016

Is Google Encrypted BigQuery capable of querying encrypted data using LIKE operator?

I have been doing some research on to implement like operator onto query the encrypted data. I was wondering if Google's Encrypted BigQuery has this functionality implemented?


lserni February 2016

TL;DR Yes, after a fashion (or, NO, if you want the 'full power' of LIKE).

You cannot do straight LIKE query on an arbitrarily encrypted string, because there's no way of running comparisons on arbitrarily encrypted strings. So you'd need to have decryption ran server side, then LIKE applied. Something like:

... WHERE DECRYPT(field, key) LIKE '%SEARCH%'

which in this case can't be done since encryption is run in the connector, which would then need to retrieve all rows, decrypt them all, and then run the LIKE check.

In some cases it is possible to use LIKE on an encrypted, if you use a special (weaker) encryption that maps symbols in a deterministic way, e.g. "A" will always be converted to "F" (or other calculable-at-runtime value) whatever its position. This means that the encrypted search key, minus any modifier (e.g. %), will be present in the encrypted field.

If this holds, then instead of searching 'ATTACK' in a field that, unencrypted, would be 'ATTACK AT DAWN', you search 'FXXFJR' in a field that, encrypted, is 'FXXFJR FX PFNU'. You can also search '%FX%' and get the same results you'd get by searching '%AT%' on the unencrypted table.

But you need to encrypt the field using a specific algorithm. Rijndael for example wouldn't do at all. Also, the algorithm is inherently weaker; it's actually what's called a shuffle cipher, a more general case of the Caesar cipher (one could slightly strengthen this implementation at the expense of search time by extending it to be a Vigenère cipher).

In this case there appears to be a specific algorithm you need to encrypt the searchable fields with, which is "SearchWords" (and a second one called "Probabilistic"). If you encrypt a column with that algorit

Post Status

Asked in February 2016
Viewed 2,266 times
Voted 9
Answered 1 times


Leave an answer