如何在Kotlin-Exposed中实现PostgreSQL全文搜索并规避SQL注入？

1. 自定义Exposed函数封装全文搜索操作 #

Exposed默认没有内置to_tsvector和to_tsquery的API，你可以通过扩展CustomFunction来安全封装这两个函数，参数会自动做SQL注入防护：

import org.jetbrains.exposed.sql.CustomFunction
import org.jetbrains.exposed.sql.Expression
import org.jetbrains.exposed.sql.QueryParameter
import org.jetbrains.exposed.sql.StringColumnType
import org.jetbrains.exposed.sql.BooleanColumnType
import org.jetbrains.exposed.sql.VarCharColumnType

// 封装to_tsvector函数
class ToTsVector(
    config: Expression<String>,
    text: Expression<String>
) : CustomFunction<Boolean>(
    "to_tsvector",
    BooleanColumnType(),
    config,
    text
)

// 简化调用的扩展函数
fun Expression<String>.toTsVector(config: String = "english") = 
    ToTsVector(QueryParameter(config, StringColumnType()), this)

// 封装to_tsquery函数
class ToTsQuery(
    config: Expression<String>,
    query: Expression<String>
) : CustomFunction<String>(
    "to_tsquery",
    VarCharColumnType(),
    config,
    query
)

// 简化调用的扩展函数
fun String.toTsQuery(config: String = "english") = 
    ToTsQuery(QueryParameter(config, StringColumnType()), QueryParameter(this, StringColumnType()))

2. 使用封装函数编写安全查询 #

有了上面的扩展，就可以像使用Exposed内置API一样编写全文搜索查询，完全避免SQL注入：

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction

// 示例表定义
object Articles : Table() {
    val id = integer("id").autoIncrement()
    val title = varchar("title", 255)
    val content = text("content")
}

// 映射用的数据类
data class Article(val id: Int, val title: String, val content: String)

// 全文搜索示例
fun searchArticles(query: String): List<Article> {
    return transaction {
        Articles.select {
            (Articles.content.toTsVector() match query.toTsQuery()) or 
            (Articles.title.toTsVector() match query.toTsQuery())
        }.map {
            Article(it[Articles.id], it[Articles.title], it[Articles.content])
        }
    }
}

3. （可选）创建全文索引优化性能 #

为了提升搜索速度，建议给目标字段创建GIN索引，用Exposed的SchemaUtils可以安全执行：

transaction {
    SchemaUtils.createIndex(
        indexName = "articles_content_tsv_idx",
        table = Articles,
        columns = listOf(
            CustomFunction<Any>(
                "to_tsvector",
                StringColumnType(),
                QueryParameter("english", StringColumnType()),
                Articles.content
            )
        ),
        indexType = "GIN"
    )
}

4. 原生SQL的安全写法 #

如果必须使用原生SQL，一定要用参数绑定，绝对不要直接拼接用户输入的字符串：

fun searchArticlesNative(query: String): List<Article> {
    return transaction {
        val result = exec(
            "SELECT * FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', ?)",
            listOf(query)
        ) { rs ->
            mutableListOf<Article>().apply {
                while (rs.next()) {
                    add(
                        Article(
                            rs.getInt("id"),
                            rs.getString("title"),
                            rs.getString("content")
                        )
                    )
                }
            }
        }
        result ?: emptyList()
    }
}

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