[Bug]: Single quotes in Procedure statement are duplicated

GeMa300 commented 3 weeks ago

Terraform CLI Version

1.9.1

Terraform Provider Version

0.92.0

Terraform Configuration

resource "snowflake_procedure" "mwe" {
  name        = "mwe_test"
  database    = "TEST"
  schema      = "testschema"
  language    = "SQL"
  comment     = "MWE"
  return_type = "VARCHAR"
  execute_as  = "CALLER"
  statement   = <<-EOT
  DECLARE
  c1 CURSOR FOR SELECT "name" FROM ACTUAL_USERS;
  v_username VARCHAR;
  v_sql_statement STRING;
  BEGIN
      FOR row_value IN c1 DO
          v_username := row_value."name";
          v_sql_statement:= 'SHOW GRANTS TO USER "' || :v_username || '";';
          EXECUTE IMMEDIATE :v_sql_statement;
      END FOR;
  END;
  EOT
}

Object type(s)

resource:procedure

Expected Behavior

The given procedure should be available in Snowflake with the line v_sql_statement:= 'SHOW GRANTS TO USER "' || :v_username || '";';

Actual Behavior

The provider adds single quotes to every single quote, rendering the SQL procedure useless. v_sql_statement:= ''SHOW GRANTS TO USER "'' || :v_username || ''";'';

Steps to Reproduce

Copy the above configuration, create the database and schema in snowflake.
run terraform apply
look at the procedure in snowflake

How much impact is this issue causing?

High

Logs

No response

Additional Information

No response

Would you like to implement a fix?

[ ] Yeah, I'll take it 😎

GeMa300 commented 3 weeks ago

From testing and the snowflake query log: It appears that all single quotes in the heredoc get converted to \' in the SDK. The snowflake query will then turn around and convert every \' to '' .

sfc-gh-jmichalak commented 3 weeks ago

Hey @GeMa300. Thanks for reaching out to us.

The procedure resource will be redesigned as part of https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/ROADMAP.md#preparing-essential-ga-objects-for-the-provider-v1. I will link this issue to https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/v1-preparations/ESSENTIAL_GA_OBJECTS.MD. As a workaround, you can use unsafe_execute.

Snowflake-Labs / terraform-provider-snowflake