PostgreSQL block query doesn't work anymore

[kmdelucaNG]

We updated to Budibase v1.0.110 and during regression test, the following query no longer works.

DO $$
DECLARE
dsKey integer;
sdiKey integer;
BEGIN
select "dataSourceKey" from "Data Source" where "Data Source"."dataSourceName" = '{{dataSourceName}}' into dsKey;
insert into "Source Data Item" ("dataSourceKey","mappingDecision", "dataSourceTypeKey") values (dsKey, '{{ mappingDecision }}', 1) returning "sourceDataItemKey" into sdiKey;
insert into "TMS Source Data" ("sourceDataItemKey", "trackType", "methodName", "sourceDataNotes") values (sdiKey,'{{ tmsTrackType }}', '{{ tmsMethodName }}', '{{ tmsNotes }}');
END $$;

The error we get back from the postgres server is:

Query Error: error: bind message supplies 5 parameters, but prepared statement "" requires 0

We're using postgres v13.4

Tried removing the single quotes and got the same results.
Did something maybe change in the way bindings for statement blocks (BEGIN... END) are handled?
All the other (non statement block) query bindings are working.

thanks.

[melohagan]

HI @kmdelucaNG

Just to confirm, are you upgrading from v1.0.110 to the latest version v1.0.206 ?
Or are you upgrading from an old version to v1.0.110 ? If so, what was the version you are upgrading from?

[kmdelucaNG]

@melohagan I updated TO v1.0.110. Prior to that we were on v0.9.168.

[melohagan]

Hi @kmdelucaNG

Thanks for the confirmation. Is there any reason you're not upgrading straight to 1.0.206 (it would include a number of additional SQL related bug fixes)

With the major upgrade from v0 to v1, there were security improvements made to the SQL queries, which as a result may also limit the types of queries you can make.

It may be worth running the various parts of your query in Budibase to determine by trial and error which part is not permissible.

[kmdelucaNG]

@melohagan we did the upgrade in late April - so that's why we're on 1.0.110 - but we are just noticing the problem now. It's a bit disruptive to upgrade, but I can look at going to the latest again.

I have performed the following debugging steps to get to where we are now:

1. Successfully executed a similar query (not functionally the same) WITHOUT the DO...BEGIN...END. Which is why I believe this is a clue.
2. One-by-one replaced the handlebar references with hard-coded values, which resulted in the error message referencing 4, 3, 2... parameters (i.e. bind message supplies N parameters...)

Thanks for the insight.

[kmdelucaNG]

Upgraded to v1.0.206 but still have the same problem. Appears to have something to do with the combination of statement blocks and bindings.

[kmdelucaNG]

Not sure if there was a better way to fix this, but I ended up converting the Budibase query to a postgres stored procedure and was able to successfully call it from Budibase.
CALL "CreateTMSSourceData"( {{tmsTrackType}}::text, {{tmsMethodName}}::text, {{tmsNotes}}::text, {{mappingDecision}}::text );