Room Database查询传入动态排序参数时自动加单引号如何解决

问题原因 #

Room的SQL参数绑定机制会自动将字符串类型的绑定参数识别为SQL字面量，添加单引号转义以规避SQL注入风险，因此你直接传入排序语句作为绑定参数时，会被当成普通字符串处理，无法作为排序规则生效。

解决方案 #

方案一：使用CASE WHEN写固定排序规则（官方推荐，安全无注入风险） #

这种方案提前将所有允许的排序字段、升降序规则写在SQL语句中，通过传入参数匹配对应规则，完全规避SQL注入风险，适合可排序字段固定的场景：

@Query("""
    SELECT * FROM DEVICES_TABLE 
    ORDER BY 
        -- 按title排序
        CASE WHEN :sortBy = 'title' AND :isAsc = 1 THEN title END ASC,
        CASE WHEN :sortBy = 'title' AND :isAsc = 0 THEN title END DESC,
        -- 按price排序
        CASE WHEN :sortBy = 'price' AND :isAsc = 1 THEN price END ASC,
        CASE WHEN :sortBy = 'price' AND :isAsc = 0 THEN price END DESC,
        -- 按type排序
        CASE WHEN :sortBy = 'type' AND :isAsc = 1 THEN type END ASC,
        CASE WHEN :sortBy = 'type' AND :isAsc = 0 THEN type END DESC
""")
fun sortDatabase(sortBy: String, isAsc: Boolean): Flow<List<DeviceData>>

调用示例：
需要按title降序查询时，传入参数sortBy = "title", isAsc = false即可。

方案二：使用RawQuery动态拼接SQL（灵活性更高，需自行做参数校验） #

如果需要更灵活的排序逻辑，可以使用Room提供的@RawQuery注解，自行拼接SQL语句，必须自行做好参数合法性校验避免SQL注入：

1. 首先在DAO中定义RawQuery方法：

@RawQuery
fun sortDatabaseRaw(query: SupportSQLiteQuery): Flow<List<DeviceData>>

2. 在数据层拼接合法SQL并调用：

// 提前定义允许的排序字段、排序规则，拦截非法参数
private val allowedSortColumns = listOf("title", "price", "type")
private val allowedSortOrders = listOf("ASC", "DESC")

fun getSortedDeviceList(sortColumn: String, sortOrder: String): Flow<List<DeviceData>> {
    // 校验参数合法性，非法参数替换为默认值
    val safeColumn = if (allowedSortColumns.contains(sortColumn)) sortColumn else "title"
    val safeOrder = if (allowedSortOrders.contains(sortOrder.uppercase())) sortOrder.uppercase() else "ASC"
    
    // 拼接安全的SQL语句
    val safeQuery = SimpleSQLiteQuery(
        "SELECT * FROM DEVICES_TABLE ORDER BY $safeColumn $safeOrder"
    )
    return deviceDao.sortDatabaseRaw(safeQuery)
}

内容的提问来源于stack exchange，提问作者sony