[CT-2539] [Bug] CTE SQL Injection in Postgres

[LouisAuneau]

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

We run models using dagster hosted in kubernetes (each run is therefore running in a fresh identical pod). Those model perform transformations on PostgreSQL tables.
Since upgrading to PostgreSQL 14 (from 11), our model runs fails with a Database Error around 10 to 20% of the time. I wasn't able to draw any correlation between the data that is transformed and the fails (our data mostly changes at daytime and the fails occur day and night).

The model structure looks like this:

WITH current_model_subquery AS (
    SELECT ... FROM {{ ref('upstream_ephemeral_model') }}
)

SELECT ... FROM current_model_subquery;

The error seems to be related to the SQL injection and CTE. Indeed, when succeeding, the compiled SQL looks like this:

WITH __dbt__cte__upstream_ephemeral_model( 
         ...
     ),
     current_model_subquery AS (
         SELECT ... FROM __dbt__cte__upstream_ephemeral_model
     )

SELECT ... FROM current_model_subquery;

When failing, the compiled SQL looks like this:

WITH __dbt__cte__upstream_ephemeral_modell( 
    ...
)
WITH current_model_subquery AS (
    SELECT ... FROM __dbt__cte__upstream_ephemeral_model
)
SELECT ... FROM current_model_subquery;

And therefore I get a Postgres error: syntax error at or near \"WITH\"\n.

To be very honest the only way I could reproduce was just to repeat the dbt run until it fails with a dbt clean in between each run.

Expected Behavior

Always generate the main model CTE with the upstream ephemeral CTE.

Steps To Reproduce

1. Using a PostgreSQL 14 and dbt 1.2.5,
2. With an upstream ephemeral model and a main model having a CTE referencing the upstream model,
3. Run dbt run

Relevant log output

Environment

- OS: Debian GNU/Linux 11 (bullseye)
- Python: 3.8.16
- dbt: 1.2.5

Which database adapter are you using with dbt?

postgres

Additional Context

No response

[dbeatty10]

Thanks for reporting this @LouisAuneau !

On first blush, the double WITH looks similar to #7396 (comment).

Which version of sqlparse do you have installed? Is it sqlparse==0.4.4, by any chance?

[LouisAuneau]

Thanks for you quick reply. Exaclty, sqlparse 0.4.4 is installed. Should I downgrade to 0.4.3 as suggested in mentioned issue ?

[dbeatty10]

Sure thing @LouisAuneau 👍

Yep, could you downgrade to sqlparse==0.4.3 and let us how that goes?

[LouisAuneau]

Looks it solved our issue! Thanks a lot for your support !

[dbeatty10]

You're welcome @LouisAuneau !