How does Room use sql statement keywords as query parameters?

Question

    @Query(
        """
        SELECT * FROM t_article ORDER BY id :order
    """
    )
    fun pagingSource(order: String): PagingSource<Int, Article>

I want order set ASC or DESC, but the compiler prompts

'(', '.', ASC, BETWEEN, COLLATE, DESC, IN, LIMIT, comma or semicolon expected, got ':order'

How to solve this problem?

Does this answer your question? [Room DAO Order By ASC or DESC variable](https://stackoverflow.com/questions/55297165/room-dao-order-by-asc-or-desc-variable) — forpas, Feb 02 '23 at 16:50
@forpas This provides a solution, but is not optimal. Because this function is provided by sqlite, not by Room. Like I said, allow query parameters to contain sql statement keywords and spliced into strings. — SageJustus, Feb 03 '23 at 04:17
This is not allowed. You can use named parameters only to pass literal values and not SQL keywords or even table/column names. — forpas, Feb 03 '23 at 05:22
@forpas So this creates a problem. To achieve this requirement through SQL statements, compared with directly splicing strings, it increases the complexity of the code. And this basic need, I believe many people should have encountered it, can you tell me how you solved it? — SageJustus, Feb 03 '23 at 06:29

MikeT · Accepted Answer · 2023-02-06T03:25:24.960

The issue is that you can only bind (replace ? with a value) literal values, not KEYWORDS or component names. In short if bound then the values are suitably enclosed so binding the string ASC would result in 'ASC'

You basically have the following options.

a) Multiple @Querys with selection of the appropriate Query (ascending or descending) e.g.

@Query("SELECT * FROM t_article ORDER BY id ASC")
fun pagingSourceAsc(): PagingSource<Int, Article>

@Query("SELECT * FROM t_article ORDER BY id DESC")
fun pagingSourceDesc(): PagingSource<Int, Article>

fun pagingSource(order: String): PagingSource<Int, Article> {
    if(order.equals
}

b) Manipulation according to a value passed to the query where the keywords are selected according to the selection criteria e.g.

@Query("SELECT * FROM t_articale ORDER BY WHEN :order = 'ASC' THEN id ASC ELSE id DESC  END")

c) By using a @RawQuery, which is very flexible BUT not validated at compile time e.g.

@RawQuery
fun pagingSourceRawQuery(rawQuery: SupportSQLiteQuery): PagingSource<Int, Article>
fun pagingSource(order: String): PagingSource<Int,Article> {
    return pagingSourceRawQuery(SimpleSQLiteQuery("SELECT * FROM t_artical ORDER BY $order"))
}

Noting that Room will not validate the SQL at compile time and also that as the order is not bound then there is the potential for SQL injection.
this option can eliminate complex SQL but for the sake of the complexity being moved to the programming code (and thus often more understandable).

How does Room use sql statement keywords as query parameters?

1 Answers1