id: gcloud-sql-external-scripts-enabled

info:
  name: External Scripts Enabled in SQL Server Database Instances
  author: princechaddha
  severity: high
  description: |
    Ensure that the external scripts enabled database flag is turned off for your Google Cloud SQL Server database instances in order to disable the execution of scripts with certain remote language extensions.
  impact: |
    Enabling the external scripts enabled flag may expose the database to potential security risks by allowing the execution of external scripts that could exploit vulnerabilities.
  remediation: |
    Disable the external scripts enabled flag in your SQL Server database instance configuration to enhance security and prevent potential misuse of the feature.
  reference:
    - https://cloud.google.com/sql/docs/sqlserver/flags
  tags: cloud,devops,gcp,gcloud,google-cloud-sql,sqlserver,gcp-cloud-config

flow: |
  code(1)
  for(let projectId of iterate(template.projectIds)){
    set("projectId", projectId)
    code(2)
    for(let sqlInstance of iterate(template.sqlInstances)){
      set("sqlInstance", sqlInstance)
      code(3)
    }
  }

self-contained: true

code:
  - engine:
      - sh
      - bash
    source: |
      gcloud projects list --format="json(projectId)"

    extractors:
      - type: json
        name: projectIds
        internal: true
        json:
          - '.[].projectId'

  - engine:
      - sh
      - bash
    source: |
      gcloud sql instances list --project $projectId --filter='DATABASE_VERSION:SQLSERVER*' --format="json(name)"

    extractors:
      - type: json
        name: sqlInstances
        internal: true
        json:
          - '.[].name'

  - engine:
      - sh
      - bash
    source: |
      gcloud sql instances describe $sqlInstance --project $projectId --format=json | jq '.settings.databaseFlags[]? | select(.name=="external scripts enabled") | .value'

    matchers:
      - type: word
        words:
          - 'on'

    extractors:
      - type: dsl
        dsl:
          - '"The external scripts enabled flag is enabled for SQL Server instance " + sqlInstance + " in project " + projectId'
# digest: 4a0a0047304502205329eb7c7f1f2d7a64551e583f092cbc0a7dac7de8a1f7166e963a508a547781022100d3fc0726dffb036f770b63c8e2dba03c40699b05bd6b4c369b440b4cb054b39e:922c64590222798bb761d5b6d8e72950