False positive: SQL Injection when creating query

[kyrofa]

Background

Brakeman version: 7.0.0
Rails version: 7.1.3.4
Ruby version: 3.1.4

False Positive

Full warning from Brakeman:

Confidence: Weak
Category: SQL Injection
Check: SQL
Message: Possible SQL injection
Code: scope.where("account_id IN (#{accessible_accounts_with_privs_subquery([Privileges::VIEW_WORKSHEETS])})")
File: app/policies/worksheet_policy.rb
Line: 50

Relevant code:

subquery = accessible_accounts_with_privs_subquery([Privileges::VIEW_WORKSHEETS])
scope.where("account_id IN (#{subquery})")

Why might this be a false positive?

The relevant models in play in this applications are users, accounts, and worksheets. Worksheets belong to accounts. Users are the people who actually login to the system and do things. Users may or may not have permission to view worksheets for a particular account. Accounts are hierarchical, and set up as a recursive tree, and permissions are inherited (i.e. if the user has permission to view account A's worksheets, they also are thus granted permission to view worksheets for all of its descendants). As a result, the question "what worksheets does this user have permission to access" involves a pretty ugly recursive query to determine all the accounts on which they have that permission. That recursive query is put together via the accessible_accounts_with_privs_subquery. Ultimately this method returns the string of an SQL query that resolves to a list of account IDs, but as you can see it doesn't involve any data that is under the control of users.

I totally get why brakeman thinks this is dangerous; I'm not entirely sure what else it could do in this situation. Is there a better way for me to put this query together? scope.where('account_id IN (?)', subquery) doesn't work, for example, because it explodes on comments in the SQL of the subquery (I mean, sanitizing what I actually intend to be SQL is obviously problematic).