T09 · Insecure Skill Coding Practices
Warning
- Location
- SKILL.md:23
- Finding
- Collection of Unredacted BigQuery Query and Billing Metadata<![CDATA[ ## Vulnerability Details **File Location**: `SKILL.md`, lines 23–35 **Vulnerability Type**: Excessive collection and potential disclosure of sensitive cloud metadata **Risk Level**: Medium ### Vulnerable Code ```markdown 1. **INFORMATION_SCHEMA.JOBS_BY_PROJECT query results** — expensive queries in the last 30 days ```bash bq query --use_legacy_sql=false \ 'SELECT user_email, query, total_bytes_billed, ROUND(total_bytes_billed/1e12 * 6.25, 2) as cost_usd, creation_time FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE DATE(creation_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) ORDER BY total_bytes_billed DESC LIMIT 50' ``` 2. **BigQuery storage usage per dataset** — to identify large datasets ```bash bq query --use_legacy_sql=false \ 'SELECT table_schema as dataset, ROUND(SUM(size_bytes)/1e9, 2) as size_gb FROM `project`.INFORMATION_SCHEMA.TABLE_STORAGE GROUP BY 1 ORDER BY 2 DESC' ``` 3. **GCP Billing export filtered to BigQuery** — monthly BigQuery costs ```bash gcloud billing accounts list ``` ``` ### Technical Analysis The skill directs users to export and submit complete BigQuery job SQL, user email addresses, dataset names, and billing-account metadata. These fields exceed the minimum information necessary for many cost-optimization tasks. The `query` field can contain sensitive literals embedded in SQL statements, including customer identifiers, email addresses, internal project or table names, access tokens, or other application data. The `user_email` field directly exposes user or service-account identities. Dataset names may disclose internal system structure or business functions. The command `gcloud billing accounts list` enumerates billing-account identifiers and display names but does not provide the requested monthly BigQuery cost data. It therefore introduces unnecessary metadata exposure without directly satisfying the stated analysis objective. Although the skill later tells th ...[truncated 1827 chars]
- Remediation
- <![CDATA[ ## Remediation Suggestions 1. Apply data minimization by removing `user_email` from the requested query unless identity-level attribution is explicitly required. 2. Do not request complete raw SQL by default. Prefer query hashes, normalized query patterns, destination table metadata, bytes billed, execution count, and timestamps. 3. If SQL text is required, instruct users to redact: - String and numeric literals - Email addresses and account identifiers - Project, dataset, and table names where feasible - Tokens, keys, passwords, and connection strings - Customer, tenant, or personal information 4. Replace `gcloud billing accounts list` with an aggregated billing-export query that returns only BigQuery cost totals for the relevant period and excludes billing-account identifiers. 5. Place the redaction warning before all export commands rather than checking only after data has been submitted. 6. Provide a minimal safe schema for submitted data, such as: - Sanitized query fingerprint - Total bytes billed - Execution count - Creation date rounded to the day - An anonymized principal identifier, if attribution is necessary 7. Explicitly tell users to inspect generated files locally before uploading or pasting them. 8. Recommend using synthetic or anonymized samples whenever raw production query text is unnecessary. ]]>