bug: `number_of_statements` has no effect on `read` statements

[aidanmelen]

Affected Resource(s)

Please list the resources as a list, for example:

• snowsql_exec

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.

Terraform Configuration Files

resource "snowsql_exec" "role_grant_all" {
  name = local.name

  create {
    statements = <<-EOT
      // must be 14 snowsql statements
    EOT
    number_of_statements = 14
  }

  read {
    statements = <<-EOT
      // any number of read query statements
    EOT
    number_of_statements = 100 # This value is not used.
  }

  delete {
    statements = <<-EOT
      // must be 14 snowsql statements
    EOT
    number_of_statements = 14
  }
}

Expected Behavior

The read block should work like the other nested blocks. The following error should be presented to the user when the read.0.statements does not match the read.0.number_of_statements in order to protect against sql injection.

│ Error: failed to execute the create statements.
│ 
│ Statements:
│ 
│   GRANT ALL PRIVILEGES ON ALL TABLES IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON ALL VIEWS IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON ALL FILE FORMATS IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON ALL SEQUENCES IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON ALL STREAMS IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON ALL PROCEDURES IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON FUTURE VIEWS IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON FUTURE FILE FORMATS IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON FUTURE SEQUENCES IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON FUTURE FUNCTIONS IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON FUTURE STREAMS IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON FUTURE PROCEDURES IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ 
│ 
│ 000008 (0A000): Actual statement count 14 did not match the desired statement count 2.
│ 
│   with snowsql_exec.role_grant_all,
│   on main.tf line 9, in resource "snowsql_exec" "role_grant_all":
│    9: resource "snowsql_exec" "role_grant_all" {
│ 
╵

Actual Behavior

The read.0.statements queries are run and the read.0.number_of_statements is ignored.

[aidanmelen]

According to the gosnowflakedb docs for Executing Multiple Statements in One Call, we should be using QueryContext like similar to ExecContext.

[aidanmelen]

Example code for a multistatement query context and process all of the resulting query rows:

package main

import (
    "context"
    "database/sql"
    "encoding/json"
    "fmt"
    "log"
    "os"
    "time"

    "github.com/snowflakedb/gosnowflake"
)

func main() {
    // Initialize the `statements` variable with multiple Snowflake query statement strings
    statements := `
          SHOW ROLES LIKE 'SYSADMIN';
          SHOW ROLES LIKE 'ACCOUNTADMIN';
        `

    // Set up a Snowflake connection
    cfg := &gosnowflake.Config{
        Account:   os.Getenv("SNOWFLAKE_ACCOUNT"),
        User:      os.Getenv("SNOWFLAKE_USER"),
        Password:  os.Getenv("SNOWFLAKE_PASSWORD"),
        Database:  os.Getenv("SNOWFLAKE_DATABASE"),
        Schema:    os.Getenv("SNOWFLAKE_SCHEMA"),
        Warehouse: os.Getenv("SNOWFLAKE_WAREHOUSE"),
    }
    dsn, err := gosnowflake.DSN(cfg)
    if err != nil {
        log.Fatal(err)
    }
    db, err := sql.Open("snowflake", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Set up a context with a timeout
    ctx := context.Background()
    ctx, cancel := context.WithTimeout(ctx, 10*time.Second)
    defer cancel()

    // Query Snowflake using the WithMultiStatement method
    multiStmtCtx, err := gosnowflake.WithMultiStatement(ctx, 0)
    if err != nil {
        log.Fatal(err)
    }
    rows, err := db.QueryContext(multiStmtCtx, statements)

    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    // Process all the rows from all the queries and store the results in a list
    results := make([]map[string]interface{}, 0)
    processRows := func(rows *sql.Rows) error {
        for rows.Next() {
            columns, err := rows.Columns()
            if err != nil {
                return err
            }
            values := make([]interface{}, len(columns))
            for i := range columns {
                values[i] = new(interface{})
            }
            err = rows.Scan(values...)
            if err != nil {
                return err
            }
            rowMap := make(map[string]interface{})
            for i, col := range columns {
                rowMap[col] = *values[i].(*interface{})
            }
            results = append(results, rowMap)
        }
        if err := rows.Err(); err != nil {
            return err
        }
        return nil
    }

    if err := processRows(rows); err != nil {
        log.Fatal(err)
    }

    for rows.NextResultSet() {
        if err := processRows(rows); err != nil {
            log.Fatal(err)
        }
    }

    marshalledResults, _ := json.Marshal(results)
    fmt.Printf("%v", string(marshalledResults))
}

and run with:

$ go run main.go | jq
[
  {
    "assigned_to_users": "0",
    "comment": "System administrator can create and manage databases and warehouses.",
    "created_on": "2021-11-02T19:45:29.136-07:00",
    "granted_roles": "0",
    "granted_to_roles": "1",
    "is_current": "N",
    "is_default": "N",
    "is_inherited": "Y",
    "name": "SYSADMIN",
    "owner": ""
  },
  {
    "assigned_to_users": "2",
    "comment": "Account administrator can manage all aspects of the account.",
    "created_on": "2021-11-02T19:45:29.108-07:00",
    "granted_roles": "2",
    "granted_to_roles": "0",
    "is_current": "Y",
    "is_default": "Y",
    "is_inherited": "N",
    "name": "ACCOUNTADMIN",
    "owner": ""
  }
]