[CT-1701] [CT-1700] [Bug] escape_single_quotes is not correct for Spark/Databricks

[b-per]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

Currently, escape_single_quotes() is dispatched to {{ expression | replace("'","''") }} for all adapters.

In Spark and Databricks single quotes need to be escaped with \\' (see here) instead of ''.

We fixed the behavior in dbt_project_evaluator but it should most likely be fixed in Core.

Expected Behavior

Single quotes are correctly escaped in Spark/Databricks

Steps To Reproduce

Use {{ dbt.escape_single_quotes("ab'c") }} in Spark/Databricks. The result is abc instead of ab'c

Relevant log output

No response

Environment

No response

Which database adapter are you using with dbt?

spark

Additional Context

No response

[dbeatty10]

Thank you for reporting this @b-per !

Do you or @graciegoheen (or anyone else) have interest in submitting the fix?

It's quite simple:

1. Create a file named dbt/include/spark/macros/utils/escape_single_quotes.sql with contents similar to here and here (basically the same as the project_evaluator implementation)
2. Update this line to have BaseEscapeSingleQuotesBackslash instead of BaseEscapeSingleQuotesQuote (like here and here)

[graciegoheen]

I can take this one!