Variable parsing seems broken after upgrading to v0.23.0

1

A lot of our cards broke after we upgraded to v0.23.0:

For example, we have this query:

SELECT
    COUNT(*)
FROM videos
[[INNER JOIN projects USING (project) -- {{webAccount}}
INNER JOIN accounts USING (account)]]
[[WHERE -- {{projectID}}
]]
[[WHERE -- {{webAccount}}
]]
[[project = {{projectID}}]]
[[AND -- {{projectID}} {{webAccount}}
]]
[[web_account = {{webAccount}}]]

Where you should be able to insert the webAccount variable, the projectID variable, or neither. If neither, the expected query is:

SELECT COUNT(*) FROM videos

If you insert the webAccount variable, the expected query is:

SELECT COUNT(*) FROM videos
INNER JOIN projects USING (project)
INNER JOIN accounts using (account)
WHERE
web_account = <some web account ID>

If you insert the projectID variable, the expected query is:

SELECT COUNT(*) FROM videos
WHERE
project = <some project ID>

But now when you navigate to this card, there’s just the error message:

Unable to substitute ':webAccount': param not specified.
Found: (:projectID :webAccount)

Not sure what the problem is.

2

We made a big change in how we substituted variables in 0.23 to close out possible SQL injection attacks. We ratcheted things down pretty tightly, as with public links/embedding, the consequences of a template allowing arbitrary strings to be inserted anywhere in the template got much higher. Eg before, the worst that could happen if you or other sql writer made a mistake is that one of your users could see data they weren’t allowed to. With remote access, the worst case would have been that someone without an account could access your data. To close this out, we’re now treating all sql template parameters as actual parameters in the sql connection. What this means is that what you’re inserting as a variable needs to be a sql value (eg, “123”), and sql snippets (eg “JOIN user”) are no longer allowed.

Longwinded explanation aside, I’m not sure what’s going on with that query. I replicated a similar query against the sample dataset

select count(*) 
from orders
where 1=1
[[and user_id = {{uid}}]]
[[and product_id = {{pid}}]]

and it worked as expected in that I could enter zero, both or either parameters. One thing that I don’t understand is why you’re doing

[[AND -- {{projectID}} {{webAccount}}
]]
[[web_account = {{webAccount}}]]

instead of

[[AND web_account = {{webAccount}}]]
3

Hmm, you’re right. I tried changing the section as you suggested, making the entire query:

SELECT
    COUNT(*)
FROM videos
[[INNER JOIN projects USING (project) INNER JOIN accounts USING (account) -- {{webAccount}}]]
[[WHERE -- {{projectID}}]]
[[WHERE -- {{webAccount}}]]
[[project = {{projectID}}]]
[[AND web_account = {{webAccount}}]]

But then we get this error:

ERROR: syntax error at or near "AND"
  Position: 734
4

Hang on a sec, I think I figured it out:

SELECT
    COUNT(*)
FROM videos
[[INNER JOIN projects USING (project) INNER JOIN accounts USING (account) -- {{webAccount}}]]
WHERE 1=1
  [[AND project = {{projectID}}]]
  [[AND web_account = {{webAccount}}]]
1 Like
5

yup, that looks right.