def _RunQuery(self,
query,
select_arg=[],
from_arg=[],
join_arg=[],
where_arg=[],
having_arg=[],
group_by_arg=[],
order_by_arg=[],
limit_arg=[],
as_arg={},
within_arg={}):
"""Run the actual test."""
clauses = {
'SELECT': select_arg,
'AS': as_arg,
'WITHIN': within_arg,
'FROM': from_arg,
'JOIN': join_arg,
'WHERE': where_arg,
'HAVING': having_arg,
'GROUP BY': group_by_arg,
'ORDER BY': order_by_arg,
'LIMIT': limit_arg,
}
real_clauses = parser.ParseQuery(query)
self.assertEqual(clauses, real_clauses)
def testRewriteQueryWhenLocalEvaluate(self):
master_key = test_util.GetMasterKey()
schema = test_util.GetCarsSchema()
query = 'SELECT Price + 1 from test_dataset.cars'
clauses = parser.ParseQuery(query)
expect_rewritten_query = ('SELECT %sPrice FROM test_dataset.cars' %
util.PROBABILISTIC_PREFIX)
self.assertEqual(
expect_rewritten_query,
query_lib.RewriteQuery(clauses, schema, master_key, _TABLE_ID)[0])
def testRewriteQueryWhenSumYear(self):
master_key = test_util.GetMasterKey()
schema = test_util.GetCarsSchema()
query = 'SELECT SUM(Year) from test_dataset.cars having SUM(Year) > 7000'
clauses = parser.ParseQuery(query)
rewritten_query = ('SELECT SUM(Year) AS %s0_ FROM test_dataset.cars '
'HAVING (SUM(Year) > 7000)' %
util.UNENCRYPTED_ALIAS_PREFIX)
self.assertEqual(
query_lib.RewriteQuery(clauses, schema, master_key, _TABLE_ID)[0],
rewritten_query)
def testRewriteQueryWhenCountMakeAlias(self):
master_key = test_util.GetMasterKey()
schema = test_util.GetCarsSchema()
query = 'SELECT COUNT(Make) AS cnt_make FROM test_dataset.cars'
rewritten_query = (
'SELECT COUNT(%sMake) AS cnt_make FROM test_dataset.cars' %
(util.PSEUDONYM_PREFIX))
clauses = parser.ParseQuery(query)
self.assertEqual(clauses['AS'], {0: 'cnt_make'})
self.assertEqual(
query_lib.RewriteQuery(clauses, schema, master_key, _TABLE_ID)[0],
rewritten_query)
def testRewriteQueryWhenMakeAlias(self):
master_key = test_util.GetMasterKey()
schema = test_util.GetCarsSchema()
query = 'SELECT Make AS alias_make FROM test_dataset.cars'
expect_rewritten_query = (
'SELECT %sMake AS alias_make FROM test_dataset.cars' %
(util.PSEUDONYM_PREFIX))
clauses = parser.ParseQuery(query)
self.assertEqual(clauses['AS'].get(0, None), 'alias_make')
self.assertEqual(
expect_rewritten_query,
query_lib.RewriteQuery(clauses, schema, master_key, _TABLE_ID)[0])
def Query(self, query, **kwds):
"""Execute the given query, returning the created job and info for print.
Arguments:
query: Query to execute.
**kwds: Passed on to BigqueryClient.ExecuteJob.
Returns:
The resulting job info and other info necessary for printing.
"""
self._CheckKeyfileFlag()
master_key = load_lib.ReadMasterKeyFile(self.master_key_filename)
try:
clauses = parser.ParseQuery(query)
except ParseException as e:
raise bigquery_client.BigqueryInvalidQueryError(
e, None, None, None)
if clauses['FROM']:
table_id = '%s_%s' % (clauses['FROM'][0],
self._GetTableCreationTime(
clauses['FROM'][0]))
hashed_table_key, table_version, table_schema = self._GetEBQTableInfo(
clauses['FROM'][0])
hashed_master_key = hashlib.sha1(master_key)
# pylint: disable=too-many-function-args
hashed_master_key = base64.b64encode(hashed_master_key.digest())
if hashed_master_key != hashed_table_key:
raise bigquery_client.BigqueryAccessDeniedError(
'Invalid master key for this table.', None, None, None)
if table_version != util.EBQ_TABLE_VERSION:
raise bigquery_client.BigqueryNotFoundError(
'Invalid table version.', None, None, None)
cipher = ecrypto.ProbabilisticCipher(master_key)
orig_schema = zlib.decompress(
cipher.Decrypt(base64.b64decode(table_schema), raw=True))
orig_schema = json.loads(orig_schema.decode('utf-8'))
else:
table_id = None
orig_schema = []
manifest = query_lib.QueryManifest.Generate()
rewritten_query, print_args = query_lib.RewriteQuery(
clauses, orig_schema, master_key, table_id, manifest)
job = super(EncryptedBigqueryClient,
self).Query(rewritten_query, **kwds)
self._LoadJobStatistics(manifest, job)
printer = EncryptedTablePrinter(**print_args)
bq.Factory.ClientTablePrinter.SetTablePrinter(printer)
return job
def testRewriteQueryWhenGroupBy(self):
master_key = test_util.GetMasterKey()
schema = test_util.GetCarsSchema()
query = (
'SELECT Year from test_dataset.cars WHERE Year > 1990 GROUP BY Year '
'ORDER BY Year LIMIT 2')
clauses = parser.ParseQuery(query)
rewritten_query = (
'SELECT Year AS %s0_ FROM test_dataset.cars WHERE (Year > 1990) '
'GROUP BY %s0_ LIMIT 2' %
(util.UNENCRYPTED_ALIAS_PREFIX, util.UNENCRYPTED_ALIAS_PREFIX))
self.assertEqual(
query_lib.RewriteQuery(clauses, schema, master_key, _TABLE_ID)[0],
rewritten_query)
def testRewriteJoin(self):
master_key = test_util.GetMasterKey()
schema = test_util.GetCarsSchema()
query = ('SELECT Year '
'FROM test_dataset.cars '
'JOIN avg_yearly_car_costs ON '
'avg_yearly_car_costs.year = test_dataset.cars.Year '
'JOIN reliability_data ON '
'reliability_data.make = test_dataset.cars.Make')
clauses = parser.ParseQuery(query)
rewritten_query = (
'SELECT Year AS p698000442118338_ue0_ '
'FROM test_dataset.cars '
'JOIN avg_yearly_car_costs ON '
'(avg_yearly_car_costs.year = test_dataset.cars.Year) '
'JOIN reliability_data ON '
'(reliability_data.make = test_dataset.cars.Make)')
self.assertEqual(
query_lib.RewriteQuery(clauses, schema, master_key, _TABLE_ID)[0],
rewritten_query)
def testRewriteQueryWhen(self):
master_key = test_util.GetMasterKey()
schema = test_util.GetCarsSchema()
query = ('SELECT SUM(Invoice_Price), GROUP_CONCAT(Make) '
'FROM test_dataset.cars')
clauses = parser.ParseQuery(query)
rewritten_query = (
'SELECT COUNT(%sInvoice_Price), %s%sInvoice_Price), '
'\'\\x44\\x08\\xb5\\xaa\\xcc\\x3f\\xf6\\xb3\\x36\\xe4'
'\\xb2\\xec\\xc7\\x75\\x1f\\xb2\\xdb\\xf3\\x3a\\x54\\xa1'
'\\x86\\xf3\\x66\\xcc\\xcb\\x49\\xc1\\x41\\xd2\\x05\\xe2'
'\\x8a\\x07\\xf2\\xe8\\x00\\x09\\x2e\\x6e\\x41\\x32\\x6c'
'\\xe8\\xa9\\x07\\x62\\x5c\\x94\\x7d\\x00\\x0e\\x5d\\x8d'
'\\xd0\\x1e\\x44\\x6d\\xe6\\x6a\\x2d\\x38\\x5e\\x53\\xfd'
'\\xbc\\x47\\x6a\\xdc\\xd7\\x35\\x09\\xa3\\x1d\\xdf\\x98'
'\\x17\\x6d\\x65\\xa1\\x7f\\xdd\\x6c\\x0e\\x26\\x06\\xc9'
'\\x6f\\x87\\x4d\\x0e\\x60\\x90\\x8f\\xe5\\x39\\xf6\\xfc'
'\\xd7\\x5a\\xea\\xd9\\x6d\\x44\\x51\\x23\\xee\\xaa\\xff'
'\\xd3\\xa5\\xae\\xa7\\x66\\xfd\\x5e\\xa9\\x16\\x4e\\x60'
'\\x86\\x83\\x44\\x83\\xb5\\x8c\\xdb\\x7f\\x06\\x8d\\x44'
'\\x10\\xa2\\x47\\x99\\x35\\xf0\\xe5\\x7d\\x1d\\x19\\x91'
'\\xc2\\x13\\x9e\\x18\\xdf\\x60\\xb1\\xca\\xf0\\xe9\\xe0'
'\\x9e\\xaa\\xb2\\x92\\x9f\\xac\\xfb\\x3a\\x18\\xc8\\xf4'
'\\xfe\\xb9\\x98\\xee\\x8a\\xcb\\x84\\x8e\\xc1\\x54\\xf2'
'\\x55\\x71\\xdc\\x0b\\xd2\\x86\\x4c\\xbc\\xc3\\x47\\x96'
'\\x1d\\x83\\xac\\x10\\x36\\x2c\\x81\\xd3\\x39\\x1e\\x64'
'\\x51\\xe2\\xd7\\x35\\x1b\\x54\\xb8\\xbe\\x2b\\x42\\xea'
'\\x51\\x58\\x1a\\x36\\xbe\\x45\\xe2\\xd1\\xd0\\x15\\x8f'
'\\xa4\\xa7\\xb4\\x34\\x19\\xa1\\x4d\\xd0\\x14\\x77\\x9d'
'\\xd8\\xab\\xc7\\xda\\x6f\\x15\\xae\\x42\\x12\\xfd\\x5c'
'\\x4d\\x6a\\x41\\xfb\\x06\\x6a\\x1c\\xf4\\x54\\x59\\xfe'
'\\xb1\\xc3\\xec\\x11\'))), GROUP_CONCAT(%sMake) '
'FROM test_dataset.cars' %
(util.HOMOMORPHIC_INT_PREFIX, util.PAILLIER_SUM_PREFIX,
util.HOMOMORPHIC_INT_PREFIX, util.PSEUDONYM_PREFIX))
self.assertEqual(
query_lib.RewriteQuery(clauses, schema, master_key, _TABLE_ID)[0],
rewritten_query)
def testRewriteQuery(self):
master_key = test_util.GetMasterKey()
schema = test_util.GetCarsSchema()
query = (
'SELECT Year from test_dataset.cars WHERE Year > 1990 GROUP BY '
'Year ORDER BY Year LIMIT 2')
clauses = parser.ParseQuery(query)
rewritten_query = (
'SELECT Year AS %s0_ FROM test_dataset.cars WHERE '
'(Year > 1990) GROUP BY %s0_ LIMIT 2' %
(util.UNENCRYPTED_ALIAS_PREFIX, util.UNENCRYPTED_ALIAS_PREFIX))
self.assertEqual(
query_lib.RewriteQuery(clauses, schema, master_key, _TABLE_ID)[0],
rewritten_query)
query = 'SELECT SUM(Year) from test_dataset.cars having SUM(Year) > 7000'
clauses = parser.ParseQuery(query)
rewritten_query = ('SELECT SUM(Year) AS %s0_ FROM '
'test_dataset.cars HAVING (SUM(Year) > 7000)' %
util.UNENCRYPTED_ALIAS_PREFIX)
self.assertEqual(
query_lib.RewriteQuery(clauses, schema, master_key, _TABLE_ID)[0],
rewritten_query)
query = 'SELECT Price + 1 from test_dataset.cars'
clauses = parser.ParseQuery(query)
rewritten_query = (
'SELECT %sPrice AS %sPrice FROM test_dataset.cars' %
(util.PROBABILISTIC_PREFIX, util.PROBABILISTIC_PREFIX))
self.assertEqual(
query_lib.RewriteQuery(clauses, schema, master_key, _TABLE_ID)[0],
rewritten_query)
query = ('SELECT SUM(Invoice_Price), GROUP_CONCAT(Make) '
'FROM test_dataset.cars')
clauses = parser.ParseQuery(query)
rewritten_query = (
'SELECT COUNT(%sInvoice_Price), %s%sInvoice_Price), '
'\'\\x44\\x08\\xb5\\xaa\\xcc\\x3f\\xf6\\xb3\\x36\\xe4'
'\\xb2\\xec\\xc7\\x75\\x1f\\xb2\\xdb\\xf3\\x3a\\x54\\xa1'
'\\x86\\xf3\\x66\\xcc\\xcb\\x49\\xc1\\x41\\xd2\\x05\\xe2'
'\\x8a\\x07\\xf2\\xe8\\x00\\x09\\x2e\\x6e\\x41\\x32\\x6c'
'\\xe8\\xa9\\x07\\x62\\x5c\\x94\\x7d\\x00\\x0e\\x5d\\x8d'
'\\xd0\\x1e\\x44\\x6d\\xe6\\x6a\\x2d\\x38\\x5e\\x53\\xfd'
'\\xbc\\x47\\x6a\\xdc\\xd7\\x35\\x09\\xa3\\x1d\\xdf\\x98'
'\\x17\\x6d\\x65\\xa1\\x7f\\xdd\\x6c\\x0e\\x26\\x06\\xc9'
'\\x6f\\x87\\x4d\\x0e\\x60\\x90\\x8f\\xe5\\x39\\xf6\\xfc'
'\\xd7\\x5a\\xea\\xd9\\x6d\\x44\\x51\\x23\\xee\\xaa\\xff'
'\\xd3\\xa5\\xae\\xa7\\x66\\xfd\\x5e\\xa9\\x16\\x4e\\x60'
'\\x86\\x83\\x44\\x83\\xb5\\x8c\\xdb\\x7f\\x06\\x8d\\x44'
'\\x10\\xa2\\x47\\x99\\x35\\xf0\\xe5\\x7d\\x1d\\x19\\x91'
'\\xc2\\x13\\x9e\\x18\\xdf\\x60\\xb1\\xca\\xf0\\xe9\\xe0'
'\\x9e\\xaa\\xb2\\x92\\x9f\\xac\\xfb\\x3a\\x18\\xc8\\xf4'
'\\xfe\\xb9\\x98\\xee\\x8a\\xcb\\x84\\x8e\\xc1\\x54\\xf2'
'\\x55\\x71\\xdc\\x0b\\xd2\\x86\\x4c\\xbc\\xc3\\x47\\x96'
'\\x1d\\x83\\xac\\x10\\x36\\x2c\\x81\\xd3\\x39\\x1e\\x64'
'\\x51\\xe2\\xd7\\x35\\x1b\\x54\\xb8\\xbe\\x2b\\x42\\xea'
'\\x51\\x58\\x1a\\x36\\xbe\\x45\\xe2\\xd1\\xd0\\x15\\x8f'
'\\xa4\\xa7\\xb4\\x34\\x19\\xa1\\x4d\\xd0\\x14\\x77\\x9d'
'\\xd8\\xab\\xc7\\xda\\x6f\\x15\\xae\\x42\\x12\\xfd\\x5c'
'\\x4d\\x6a\\x41\\xfb\\x06\\x6a\\x1c\\xf4\\x54\\x59\\xfe'
'\\xb1\\xc3\\xec\\x11\'))), GROUP_CONCAT(%sMake) '
'FROM test_dataset.cars' %
(util.HOMOMORPHIC_INT_PREFIX, util.PAILLIER_SUM_PREFIX,
util.HOMOMORPHIC_INT_PREFIX, util.PSEUDONYM_PREFIX))
self.assertEqual(
query_lib.RewriteQuery(clauses, schema, master_key, _TABLE_ID)[0],
rewritten_query)