Postgres with variables in subselects broken 2.6.4

[justinboynton]

I am getting the following error:

could not determine data type of parameter $1

with Postgres and variables in sub-selects using 2.6.4 (master@d1f61c4)

SELECT word, nentry AS times
FROM   ts_stat($$SELECT to_tsvector('english', query)
FROM "searches", orgs, users
WHERE (searches.user_id = users.id 
AND orgs.id = users.org_id)  
AND orgs.id not in (1,2,95,518, 270, 627,171,203, 787, 761, 693, 695, 904, 929)
and searches.created_at > {start_date}
and searches.created_at < {end_date}
AND users.industry_sector <> 1$$) 
ORDER BY nentry DESC
LIMIT 100;

Environment:

ruby 2.7.4p191 rails 6.1.5 pg 1.2.3

[ankane]

Hey @justinboynton, Blazer 2.6+ has stricter limitations on where variables can be placed in queries (to address #392). For instance, they can't be placed directly in strings like above. One way around this is to use CONCAT to build strings (but in the example above, it'll allow for injection in query passed to ts_stat).

[ankane]

Added some hints to the error message.

[justinboynton]

Thanks Andrew, that's good to know. Will have a rethink on the reports so they don't need the dates in the strings i.e separate queries for last 7 days, month etc.

On Wed, 3 Aug 2022, 22:36 Andrew Kane, @.***> wrote:

Hey @justinboynton https://github.com/justinboynton, Blazer 2.6+ has stricter limitations on where variables can be placed in queries (to address #392 https://github.com/ankane/blazer/issues/392). For instance, they can't be placed directly in strings like above. One way around this is to use CONCAT to build strings (but in the example above, it'll allow for injection in query passed to ts_stat).

— Reply to this email directly, view it on GitHub https://github.com/ankane/blazer/issues/406#issuecomment-1204509355, or unsubscribe https://github.com/notifications/unsubscribe-auth/AATARURLYRKZRUILXVKJHCDVXLQ5XANCNFSM55OCUEIQ . You are receiving this because you were mentioned.Message ID: @.***>