Single quotes within comment break snowflake_role resources

[monti-python]

Terraform CLI and Provider Versions

terraform CLI -> 1.5.6
Snowflake provider -> 0.83.1

Terraform Configuration

terraform {
  backend "local" {
    path = "./terraform.tfstate"
  }
  required_providers {
    snowflake = {
      source = "Snowflake-Labs/snowflake"
      version = "~>0.83.0"
    }
  }
  required_version = "~>1.5.6"
}

provider "snowflake" {
  ...
}

resource "snowflake_role" "mkt_prod" {
  name = "MKT_PROD"
  comment = "Role that grants access on the 'MKT' schema in environment 'PROD'"
}

Expected Behavior

The role is created with the comment specified above.

This functionality was working flawlessly in version 0.37.1 before we upgraded the provider, so there seems to be a regression.

Actual Behavior

The resource creation fails because the single quotes are not escaped:

│ Error: 001003 (42000): SQL compilation error:
│ syntax error line 1 at position 125 unexpected '' schema in environment ''.

Running it with debug traces yields:

2024-01-24T10:59:15.207+0100 [DEBUG] provider.terraform-provider-snowflake_v0.83.1: 2024/01/24 10:59:15 [DEBUG] sql-conn-exec: [query CREATE ROLE "MKT_PROD" COMMENT = 'Role that grants access on the 'MKT' schema in environment 'PROD'' err 001003 (42000): SQL compilation error:
 
2024-01-24T10:59:15.207+0100 [DEBUG] provider.terraform-provider-snowflake_v0.83.1: syntax error line 1 at position 125 unexpected '' schema in environment ''.

Steps to Reproduce

1. Create a snowflake_role resource with a comment containing single-quotes, as in the example above
2. terraform init
3. terraform apply

How much impact is this issue causing?

Medium

Logs

No response

Additional Information

Might be related to #1049

[sfc-gh-jcieslak]

Hey @monti-python 👋
It's throwing an error because the text that is passed to the comment is later on used in the SQL that creates the role. It should work when you escape them like this comment = "Role that grants access on the \'MKT\' schema in environment \'PROD\'". Otherwise, Snowflake thinks your comment ends before MKT, because it's interpreted as the closing quote. Please let me know if that's the case.

[sfc-gh-asawicki]

But this is an error originating in the old implementation. It should be fine in the SDK, and we are moving there in #2405. So... it should be working correctly after we merge and release it.

[sfc-gh-jcieslak]

@monti-python could you confirm it's working in the latest version of the provider?

[monti-python]

@sfc-gh-jcieslak I confirm single quotes in role comments no longer cause issues
Thanks for the update!

[sfc-gh-asawicki]

Great to hear that @monti-python. I am closing the issue then.