PostgreSQL中如何安全地匹配包含特殊字符的字符串模式

LIKE模式匹配中的特殊字符问题

在postgresql中使用like操作符进行字符串模式匹配时，%和_被视为通配符：%匹配任意长度的任意字符序列（包括空字符串），而_匹配任意单个字符。当用户输入作为匹配模式的一部分时，如果未经过处理，这些特殊字符可能会导致非预期的匹配结果。例如，用户输入“rob_”期望匹配字面上的“rob_the_man”，但如果直接用于like 'rob\_%'，它将匹配“robert42”和“rob_the_man”，因为_被解释为通配符。为了确保用户输入被字面匹配，必须对这些特殊字符进行逃逸（escaping）。

LIKE语句的逃逸机制

PostgreSQL提供了两种方式来处理LIKE模式中的特殊字符：

1. 默认逃逸字符：默认情况下，反斜杠\被用作逃逸字符。这意味着如果你想匹配字面上的%或_，你需要在它们前面加上\，例如'rob\_%'。
2. 自定义逃逸字符：你可以通过在LIKE子句后紧跟ESCAPE子句来指定一个自定义的逃逸字符。例如，WHERE field LIKE 'john^%node1^^node2.uucp@%' ESCAPE '^'将使用^作为逃逸字符，匹配以“john%node1^node2.uccp@”开头的所有字符串。需要注意的是，如果自定义的逃逸字符本身也需要被字面匹配，它必须在模式中重复两次（例如^^匹配字面上的^）。

注意事项：

• 默认逃逸字符\在某些旧版本的PostgreSQL中（standard_conforming_strings配置为OFF时）可能与字符串字面量中的其他用途冲突。虽然PostgreSQL 9.1及更高版本默认standard_conforming_strings为ON，但了解这一点有助于兼容性考虑。
• 选择一个不常出现在用户输入中的字符作为自定义逃逸字符是更稳妥的做法。

客户端与服务器端逃逸的权衡

处理LIKE模式中的特殊字符，可以在应用程序客户端完成，也可以在数据库服务器端完成。

• 客户端逃逸：应用程序在将用户输入发送到数据库之前，遍历字符串并替换%、_（以及自定义逃逸字符）为它们的逃逸形式。

  • 优点：逻辑集中在应用程序层。
  • 缺点：需要确保所有使用LIKE的场景都正确实现了逃逸逻辑，容易遗漏。

• 服务器端逃逸：利用SQL内置函数（如REPLACE()）在数据库层面对用户输入进行处理。

  • 优点：逻辑集中在SQL查询中，更接近数据处理，减少应用程序的负担。
  • 缺点：SQL语句可能变得稍微复杂。

对于需要高度健壮性和一致性的场景，服务器端逃逸通常是更优的选择，因为它将逃逸逻辑与SQL查询本身绑定，减少了应用程序层面的潜在错误。

知我AI·PC客户端

离线运行 AI 大模型，构建你的私有个人知识库，对话式提取文件知识，保证个人文件数据安全

35

查看详情

结合SQL注入防范的服务器端逃逸示例

在处理用户输入时，除了LIKE模式的特殊字符逃逸外，防止SQL注入是至关重要的。始终使用参数化查询（prepared statements）或占位符来传递用户输入，而不是直接拼接字符串。

以下是一个结合了参数化查询和服务器端REPLACE()函数的Go语言（使用go-pgsql库）示例，它演示了如何在PostgreSQL中安全地处理用户输入的LIKE模式，确保字面匹配并防止SQL注入：

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lxn/go-pgsql" // 导入PostgreSQL驱动
)

func main() {
    // 假设db已经初始化并连接到PostgreSQL
    // db, err := sql.Open("pgsql", "user=postgres password=root dbname=testdb sslmode=disable")
    // if err != nil {
    //  log.Fatal(err)
    // }
    // defer db.Close()

    // 模拟一个数据库连接
    db, _ := sql.Open("dummy", "") // 仅用于示例，实际应用中替换为真实连接

    userInput := "rob_the%man^" // 模拟用户输入，包含特殊字符和自定义逃逸字符

    // 使用服务器端REPLACE函数进行逃逸，并指定自定义逃逸字符'^'
    // 1. 将自定义逃逸字符'^'替换为'^^'，以匹配字面上的'^'
    // 2. 将'%'替换为'^%'
    // 3. 将'_'替换为'^_
    // 4. 最后拼接'%'，表示匹配以处理后的用户输入开头的字符串
    // 5. ESCAPE '^' 指定'^'为逃逸字符
    query := `
        SELECT * 
        FROM USERS 
        WHERE name LIKE REPLACE(REPLACE(REPLACE($1,'^','^^'),'%','^%'),'_','^_') || '%' ESCAPE '^'
    `

    // 执行查询，将用户输入作为参数传递，防止SQL注入
    rows, err := db.Query(query, userInput)
    if err != nil {
        fmt.Printf("查询出错: %v\n", err)
        return
    }
    defer rows.Close()

    fmt.Println("查询成功，结果如下：")
    // 遍历并处理查询结果
    for rows.Next() {
        var id int
        var name string
        if err := rows.Scan(&id, &name); err != nil {
            fmt.Printf("扫描行出错: %v\n", err)
            continue
        }
        fmt.Printf("ID: %d, Name: %s\n", id, name)
    }
    if err = rows.Err(); err != nil {
        fmt.Printf("遍历行出错: %v\n", err)
    }
}

// 模拟sql.DB接口，仅用于编译通过示例代码
type dummyDB struct{}

func (d *dummyDB) Query(query string, args ...interface{}) (*sql.Rows, error) {
    fmt.Printf("执行查询:\n%s\n参数: %v\n", query, args)
    // 实际应用中会执行真正的数据库查询
    // 这里返回一个空的或模拟的rows
    return &sql.Rows{}, nil
}

func (d *dummyDB) Close() error { return nil }

func sqlOpenDummy(driverName, dataSourceName string) (*sql.DB, error) {
    return &sql.DB{}, nil
}

代码解析：

• REPLACE($1,'^','^^'): 首先将用户输入中的所有自定义逃逸字符^替换为^^。这是为了确保如果用户输入本身包含^，它也能被字面匹配。
• REPLACE(...,'%','^%'): 接着将所有%替换为^%。
• REPLACE(...,'_','^_'): 然后将所有_替换为^_。
• || '%': 在处理后的模式末尾拼接%，实现“以...开头”的匹配逻辑。如果需要完全匹配，则不拼接%；如果需要“包含”，则拼接'%...%'。
• ESCAPE '^': 明确指定^为LIKE模式的逃逸字符。
• $1: 这是Go语言database/sql接口中用于参数化查询的占位符，variable_user_input（即Go代码中的userInput）会作为参数安全地传递给数据库。

总结

在PostgreSQL中处理用户输入的LIKE模式匹配时，核心在于对特殊通配符%和_进行正确的逃逸。通过使用ESCAPE子句自定义逃逸字符，并结合服务器端的REPLACE()函数进行字符替换，可以构建出既安全又健壮的查询。始终记住，这种逃逸处理是在防止SQL注入的基础上进行的补充措施，参数化查询是保护应用程序免受SQL注入攻击的第一道防线。采用服务器端逃逸策略，能够将复杂性封装在SQL语句中，提高代码的可维护性和安全性。

以上就是PostgreSQL中如何安全地匹配包含特殊字符的字符串模式的详细内容，更多请关注php中文网其它相关文章！