Order by clause: dynamic parameters ignored?

[basilex]

Hi, All!

OS - Debian Linux 10 DB - PostgreSQL 13.2.

W'd like to create dynamic parameter(s) for the simple query:

-- name: PersonsGet :many
select *
  from persons
 order by @sql_order::text
 limit @sql_limit offset @sql_offset;

@sql_limit and @sql_offset works perfectly

but @sql_order with dynamic parameters seems doesn't work... and always ignored...

For example:

• order by [id]
• order by [id desc]
• order by [name, created_at desc]

Generated code below:

const personsGet = `-- name: PersonsGet :many
select id, salut, first_name, last_name, birthday, gender, status, created_at, updated_at
  from persons
 order by $1::text
 limit $3 offset $2
`

type PersonsGetParams struct {
    SqlOrder  string `json:"sql_order"`
    SqlOffset int32  `json:"sql_offset"`
    SqlLimit  int32  `json:"sql_limit"`
}

func (q *Queries) PersonsGet(ctx context.Context, arg PersonsGetParams) ([]Persons, error) {
    rows, err := q.query(ctx, q.personsGetStmt, personsGet, arg.SqlOrder, arg.SqlOffset, arg.SqlLimit)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    items := []Persons{}
    for rows.Next() {
        var i Persons
        if err := rows.Scan(
            &i.ID,
            &i.Salut,
            &i.FirstName,
            &i.LastName,
            &i.Birthday,
            &i.Gender,
            &i.Status,
            &i.CreatedAt,
            &i.UpdatedAt,
        ); err != nil {
            return nil, err
        }
        items = append(items, i)
    }
    if err := rows.Close(); err != nil {
        return nil, err
    }
    if err := rows.Err(); err != nil {
        return nil, err
    }
    return items, nil
}

Any ideas why? Any help would be appreciated!

[aitva]

Your problem comes from how requests are prepared: ORDER BY expects an SQL expression and you are passing a text parameter. Postgres does the right thing and does not evaluate @sql_order as SQL, preventing you from injecting SQL in the query.

https://www.postgresql.org/docs/11/sql-select.html

[aitva]

You can avoid SQL injection by using a CASE in your query:

-- name: PersonsGet :many
select *
from persons
order by case
    when @sql_order = 'first_name' then first_name
    when @sql_order = 'created_at' then created_at
end
limit @sql_limit offset @sql_offset;

[basilex]

ohh. THANK YOU very much!!

BUT

-- name: PersonsGet :many
select *
  from persons
 order by case
  when @sql_order = 'id' then id
  when @sql_order = 'first_name' then first_name
  when @sql_order = 'last_name' then last_name
  when @sql_order = 'birthday' then birthday
  when @sql_order = 'gender' then gender
  when @sql_order = 'status' then status
  when @sql-order = 'created_at' then created_at
  when @sql_order = 'updated_at' then updated_at
 end
 limit @sql_limit offset @sql_offset;

storage/query/persons.sql:1:1: syntax error at or near "order"

Any ideas?

[aitva]

You have a small typo on the 7th when: you wrote @sql-order and not @sql_order. The error message from the parser is not very helpful.

You can use play.sqlc.dev to share your code in issues, it makes it easier for others to reproduce.

[basilex]

You have a small typo on the 7th when: you wrote @sql-order and not @sql_order. The error message from the parser is not very helpful.

You can use play.sqlc.dev to share your code in issues, it makes it easier for others to reproduce.

Ohh, thanx a lot, friend!!! You helped me a lot!!! Again, thanx, thanx, thanx!!! )))

[kyleconroy]

@aitva Thanks for helping out here, really appreciate it.

[kennedyjustin]

I am experiencing the following SQL Error when following @aitva's example above:

CASE types text and timestamp with time zone cannot be matched

if the columns I am ordering by are not the same type.

I would think this means that the solution provided by @aitva in this issue, and similarly in https://github.com/kyleconroy/sqlc/issues/830#issuecomment-827489986 does not work.

My intuition is that dynamic ORDER BY clauses are extremely common use case for many applications (any visualization via table likely has sorting by column).

The fact that sqlc does not have an answer to this use case and doesn't provide a way to drop down into raw sql is causing me thinking about using a different solution for my Go Postgres applications (although I have had an extremely positive experience with the library in general).

@kyleconroy is there any way you could provide some guidance here (I apologize in advance if I am missing something obvious), but this is a total deal breaker I would imagine for a large class of applications.

[kennedyjustin]

For posterity, there was a lengthy discussion already here.

[ryan-berger]

@kennedyjustin The above query may not work since it is using column names of different types, but you can use a column's ordinal number instead to achieve the same functionality:

select *
from persons
order by case
    when @sql_order = 'first_name' then 1
    when @sql_order = 'created_at' then 2
end
limit @sql_limit offset @sql_offset;

Would satisfy Postgres' type checker

[piavgh]

@kennedyjustin The above query may not work since it is using column names of different types, but you can use a column's ordinal number instead to achieve the same functionality:

select *
from persons
order by case
    when @sql_order = 'first_name' then 1
    when @sql_order = 'created_at' then 2
end
limit @sql_limit offset @sql_offset;

Would satisfy Postgres' type checker

@ryan-berger : what are 1 and 2 in this case? Do you mean that it's the index of the column? What if someone changes the column order? It will make the code broken, right?