supabase / supabase / 27430453960

74%

push

github

fix: warn against dangerous queries in dynamic SQL execution (#46882)

Extends the SQL editor's destructive query detection to catch dangerous
operations (DROP, DELETE, TRUNCATE) hidden within dynamic SQL execution
patterns like `EXECUTE`, `EXECUTE format()`, `OPEN cursor FOR EXECUTE`,
and `RETURN QUERY EXECUTE`.

Previously, only direct statements like `DROP TABLE users` triggered
warnings. Dynamic SQL inside PL/pgSQL blocks bypassed detection
entirely.

Closes FE-3603 and https://github.com/supabase/supabase/issues/46876

<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->
## Summary by CodeRabbit

* **Bug Fixes**
* Improved detection of potentially destructive SQL across many
execution styles, including DROP/DELETE/TRUNCATE, ALTER ... DROP COLUMN,
and dynamic/executed or string-constructed queries to reduce missed
destructive cases.

* **Tests**
* Expanded test coverage for SQL validation with extensive positive and
negative scenarios covering dynamic execution patterns (EXECUTE
variants, concatenation, dollar-quoted and escaped strings) to reduce
false positives and negatives.
<!-- end of auto-generated comment: release notes by coderabbit.ai -->

990 of 1384 branches covered (71.53%)

Branch coverage included in aggregate %.

1524 of 2082 relevant lines covered (73.2%)

249.64 hits per line