An abnormal trap in PostgreSQL 9.0 Selects using Order By?
I am trying to create a list page like the one in the Computers sample. My environment is Play 2.0 and PostrgreSQL 9.0
I have the following method on a User object:
def list(page: Int = 0, pageSize: Int = 10, orderBy: Int = 1, filter: String = "%"): Page[User] = {
val offset = pageSize * page
val mode = if (orderBy > 0) "ASC NULLS FIRST" else "DESC NULLS LAST"
Logger.debug("Users.list with params: page[%d] pageSize[%d] orderBy[%d] filter[%s] order[%s]".format(page, pageSize, orderBy, filter, mode))
DB.withConnection {
implicit connection =>
val users = SQL(
"""
select * from publisher
where name ilike {filter}
order by {orderBy} %s
limit {pageSize} offset {offset}
""".format(mode)
).on(
'pageSize -> pageSize,
'offset -> offset,
'filter -> filter,
'orderBy -> scala.math.abs(orderBy)
).as(User.simple *)
val totalRows = SQL(
"""
select count(*) from publisher
where name like {filter}
"""
).on(
'filter -> filter
).as(scalar[Long].single)
Page(users, page, offset, totalRows)
}
}
It doesn't matter which "orderBy" value I provide, the order is always based on the entity ID.
The query generated by Anorm is valid PostgreSQL and it works great when run directly on the database directly. But it seems that if the Anorm parser ignored the order in which the results were returned and instead returns a list ordered by "id".
I even tried to simplify the query to "select * from publisher order by 2 ASC/DESC"
but nothing fixed, ordering is ignored by Anorm on return.
Any suggestion on how to resolve this issue?
source to share
Thanks to Guillaume on the Play mailing list, I found a workaround.
All placeholders work, besides, they are fine. The worst part is that when you follow the logs, the driver generates the correct request and PostgreSQL receives it. I'm not sure if the deal is very confusing, but if I remove this placeholder it just works.
Pressing: (
I solved it like this:
val users = SQL(
"""
select * from publisher
where name ilike {filter}
order by %d %s
limit {pageSize} offset {offset}
""".format(scala.math.abs(orderBy), mode)
).on(
'pageSize -> pageSize,
'offset -> offset,
'filter -> filter
).as(User.simple *)
Now you will shout "SQL INJECTION". Relax. Although it is possible, one way or another, orderBy
is an integer (which we turn into abs for more safety). If you try to call a controller that provides a orderBy
string, Play returns an error 404
. Therefore, only whole numbers are allowed. And if there is no column corresponding to the given integer, it is order by
ignored. So, not perfect, but not very bad.
source to share