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

i have been doing research on implement operator onto query encrypted data. wondering if google's encrypted bigquery has functionality implemented?

tl;dr yes, after fashion (or, no, if want 'full power' of like).

you cannot straight query on arbitrarily encrypted string, because there's no way of running comparisons on arbitrarily encrypted strings. you'd need have decryption ran server side, applied. like:

... decrypt(field, key) '%search%' 

which in case can't done since encryption run in connector, need retrieve all rows, decrypt them all, , run check.

in cases possible use on encrypted, if use special (weaker) encryption maps symbols in deterministic way, e.g. "a" converted "f" (or other calculable-at-runtime value) whatever position. means encrypted search key, minus modifier (e.g. %), present in encrypted field.

if holds, instead of searching 'attack' in field that, unencrypted, 'attack @ dawn', search 'fxxfjr' in field that, encrypted, 'fxxfjr fx pfnu'. can search '%fx%' , same results you'd searching '%at%' on unencrypted table.

but need encrypt field using specific algorithm. rijndael example wouldn't @ all. also, algorithm inherently weaker; it's what's called shuffle cipher, more general case of caesar cipher (one strengthen implementation @ expense of search time extending vigenère cipher).

in this case there appears specific algorithm need encrypt searchable fields with, "searchwords" (and second 1 called "probabilistic"). if encrypt column algorithm, gets passed to

https://github.com/google/encrypted-bigquery-client/blob/master/src/query_interpreter.py  def rewritesearchwordsencryption(field, literal): 

and converted form can searched. it's unclear whether works any string (e.g. '%at daw%') or if need specify 1 of words (e.g. 'attack', 'at' or 'dawn'; '%tack' won't work).

actually, on rereading code, looks me if encrypted searchable fields contain concatenation of hashes of encrypted keywords. run kind of search 'contains' keyword.