Parametrize column name

[phoe]

Is it possible to parametrize name in the below query? I want to be able to do things like SELECT gender FROM user ... or SELECT surname FROM user ... by passing in the column name as a function argument.

-- name: young-user-names-by-country @column
SELECT name
FROM user
WHERE (
  country_code = ?
  OR
  country_code = ?
)
AND age < :max_age

[phoe]

Currently, keywords/symbols are not understood by YESQL. You can perhaps leverage that by converting :foo-bar to a column named foo_bar.

[phoe]

My use case is exactly:

-- name: player-row-by-name @row
-- Retrieves a player whose data matches the arguments.
SELECT * from player
    WHERE player_name = ?

-- name: player-row-by-email @row
-- Retrieves a player whose data matches the arguments.
SELECT * from player
    WHERE email = ?

-- name: player-row-by-display-name @row
-- Retrieves a player whose data matches the arguments.
SELECT * from player
    WHERE display_name = ?

These three functions could be replaced by:

-- name: player-row @row
-- Retrieves a player whose data matches the arguments.
SELECT * from player
    WHERE ? = ?

that is then invokable via

(player-row :player-name "foo")
(player-row :email "foo@bar.com")
(player-row :display-name "Foo Bar-Baz")

[ruricolist]

My concern with this is that it can't be done with SQL parameters, which means it has to be done with string concatenation, which means opening the door to SQL injection. I'm not sure it's worth it.

[phoe]

It should be possible to do some minimal parsing based on where the argument is positioned in the query - therefore it should be possible to assert it is a valid table/column name and to assert it exists before proceeding.

[ruricolist]

One approach might be a whitelist, where you specify the possible columns (or tables) in the file. Something like

SELECT * from player
    WHERE ?{player_name, email, display_name} = ?

The argument would have to match one of the possibilities, which would eliminate any possibility of SQL injection, and since all the possible queries would be statically known it would still be relatively easy to implicitly prepare the queries.

[phoe]

Yes! That is exactly what I need.