Dynamic SQL generation

[omi-donlimit]

Hi, fairly new gopher here :)

How can i achieve something like this:

SELECT *
FROM product_product
WHERE id = $1 OR id = $2
ORDER BY id $3
LIMIT $4

and then will pass this args [1 2 "ASC" 10]
?

I manage to get WHERE and LIMIT working, but I get error QueryRow failed: ERROR: syntax error at or near "$3" (SQLSTATE 42601) with the ORDER BY

Thank you!

[jackc]

This is a PostgreSQL issue.

jack@[local]:5432 jack=# create table product_product ( id text primary key );
CREATE TABLE
Time: 12.902 ms
jack@[local]:5432 jack=# prepare s as SELECT *
FROM product_product
WHERE id = $1 OR id = $2
ORDER BY id $3
LIMIT $4;
ERROR:  42601: syntax error at or near "$3"
LINE 4: ORDER BY id $3
                    ^
LOCATION:  scanner_yyerror, scan.l:1245
Time: 0.751 ms

ASC and DESC are not values, they are part of the SQL statement. Bound parameters must be values, they cannot be part of the SQL statement.

If you whitelist safe values for the variable holding ASC or DESC you could build the SQL string.

[omi-donlimit]

If you whitelist safe values for the variable holding ASC or DESC you could build the SQL string.

how do i do this with pgx? I've looked around the repo but have found nothing regarding this.

Oh on second thought, i think i have an idea now, i'll just do sprintf to place those values instead of the args for the query. But there might be a better way...

[jackc]

Exactly. It's actually not a pgx thing. It's plain go.

[jackc]

But be sure to only do it for the ASC/DESC and to sanitize that variable. Use the $1, $2, for other values.