[Feature] Escape the adapter-specific string literal delimiter within the string_literal macro

[dbeatty10]

Originally posted by @nicods-fr in dbt-labs/dbt-core#10654 (comment) :

In my point of view, the proposed workaround is a bug. So anyone using it, should stop and fix their code. Also, the macro dbt.string_literal should escape single quotes, so doubling like this : dbt.string_literal(dbt.escape_single_quotes(value)) should produce too many quotes, if it doesn't, one of those macros has a bug.

Describe the feature

Provide a cross-database Jinja macro that converts a Jinja string into a SQL string literal.

Example 1

This code:

select {{ macro_name("Haute-Corse") }}

should render this SQL in Postgres:

select 'Haute-Corse'

which would return this when executed:

Haute-Corse

Example 2

This code:

select {{ macro_name("L'Île-Rousse") }}

should render this SQL in Postgres:

select 'L''Île-Rousse'

which would return this when executed:

L'Île-Rousse

Describe alternatives you've considered

We could update the implementation of string_literal to use escape_single_quotes, but it would change the behavior for anyone that is already using these long-standing macros in concert with each other (like here) or is otherwise using string_literal with full knowledge of its current limitations. 💥

We could also keep things as-is, one consequence being user-level overrides like described in dbt-labs/dbt-core#10654 (comment)

Anything else?

Put another way, we should provide a cross-database Jinja macro that safely does two things to a string value:

1. Escapes any instances of the database-specific delimiter for string values (a la the escape_single_quotes macro)
2. Wraps the value in the database-specific delimiter for string values (a la the string_literal macro)

The quote config within accepted_values should then use that implementation.