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

_Originally posted by @nicods-fr in https://github.com/dbt-labs/dbt-core/issues/10654#issuecomment-2331295325_ :

> 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:

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

should render this SQL in Postgres:

```sql
select 'Haute-Corse'
```

which would return this when executed:

```
Haute-Corse
```

#### Example 2

This code:

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

should render this SQL in Postgres:

```sql
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](https://github.com/dbt-labs/dbt-adapters/blob/c310bc5fbd36e510353cfcd9a451f69bfdb9c9c3/dbt/include/global_project/macros/unit_test_sql/get_fixture_sql.sql#L87)) 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 https://github.com/dbt-labs/dbt-core/issues/10654#issuecomment-2331001514

### 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`](https://docs.getdbt.com/reference/dbt-jinja-functions/cross-database-macros#escape_single_quotes) macro)
2. Wraps the value in the database-specific delimiter for string values (a la the [`string_literal`](https://docs.getdbt.com/reference/dbt-jinja-functions/cross-database-macros#string_literal) macro)

The [`quote` config within `accepted_values`](https://docs.getdbt.com/reference/resource-properties/data-tests#accepted_values) should then use that implementation.