Knex.js Raw SQL 命名参数传递完全指南：混合使用模板字符串与参数绑定

最新推荐文章于 2025-12-18 21:08:36 发布
原创 最新推荐文章于 2025-12-18 21:08:36 发布 · 755 阅读 · 30 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#1024程序员节 #postgresql #node.js #sql

📌 前言

在使用 Knex.js 进行复杂 SQL 查询时，我们经常面临一个问题：如何既能保证 SQL 注入安全，又能灵活处理某些必须在编译时确定的参数？

本文将深入探讨 Knex.js raw() 方法中命名参数的使用技巧，特别是为什么某些参数必须使用模板字符串插值而不能使用命名参数绑定。

🎯 核心问题

在实际项目中，我们遇到了这样的场景：

// 时间粒度参数
const timeGrain = 'day' // 可能是 'day', 'week', 'month', 'year'

// SQL 查询
const sql = `
    SELECT
        date_trunc('${timeGrain}', record_date) AS period_time,
        COUNT(DISTINCT order_id) AS cnt
        FROM daily_records
    GROUP BY date_trunc('${timeGrain}', record_date)
`

// 为什么 timeGrain 使用 ${} 而不是 :timeGrain ？
const result = await knex.raw(sql, {
    startDate,  // ✅ 使用命名参数
    endDate,    // ✅ 使用命名参数
    // timeGrain   // ❌ 不在参数对象中
})

疑问：为什么 timeGrain 不能像其他参数一样使用 :timeGrain 的命名参数方式传递？

📚 Knex.js 命名参数基础

1. 标准命名参数语法

Knex.js 支持命名参数绑定，语法如下：

// 方式一：使用 :paramName
const result = await knex.raw(`
    SELECT * FROM users
    WHERE name = :userName
    AND age > :minAge
`, {
    userName: 'John',
    minAge: 18
})

// 方式二：使用 ? 占位符（位置参数）
const result = await knex.raw(`
    SELECT * FROM users
    WHERE name = ? AND age > ?
`, ['John', 18])

2. 命名参数的优势

特性命名参数位置参数
可读性⭐⭐⭐⭐⭐⭐⭐⭐
SQL注入防护✅ 自动转义✅ 自动转义
参数复用✅ 可重复使用❌ 需要多次传递
维护性⭐⭐⭐⭐⭐⭐⭐⭐

参数复用示例：

// ✅ 命名参数可以在 SQL 中多次使用，只需传递一次
const sql = `
    SELECT * FROM orders
    WHERE start_date >= :startDate
    AND end_date <= :endDate
    AND created_at BETWEEN :startDate AND :endDate
`

await knex.raw(sql, {
    startDate: '2025-01-01',  // 在 SQL 中使用了 2 次
    endDate: '2025-12-31'      // 在 SQL 中使用了 2 次
})

// ❌ 位置参数需要传递多次
const sql2 = `
    SELECT * FROM orders
    WHERE start_date >= ?
    AND end_date <= ?
    AND created_at BETWEEN ? AND ?
`
await knex.raw(sql2, [
    '2025-01-01',  // 第1次
    '2025-12-31',  // 第1次
    '2025-01-01',  // 第2次 - 重复
    '2025-12-31'   // 第2次 - 重复
])

🔍 为什么某些参数不能使用命名参数？

核心原因：PostgreSQL 参数绑定的限制

PostgreSQL 的参数绑定（Prepared Statement）只能用于数据值，而不能用于：

  1. 表名和列名
  2. SQL 关键字
  3. 函数参数的常量部分（如 date_trunc() 的第一个参数）
  4. 数据类型名称

实际案例分析

❌ 错误写法：使用命名参数
const timeGrain = 'day'

// 这段 SQL 会报错！
const sql = `
    SELECT date_trunc(:timeGrain, record_date) AS period_time
        FROM daily_records
`

await knex.raw(sql, { timeGrain })

// PostgreSQL 错误：
// ERROR: function date_trunc(unknown, date) does not exist
// HINT: No function matches the given name and argument types.

错误原因：PostgreSQL 在解析 SQL 时，date_trunc() 的第一个参数必须是字符串常量，用于确定函数的重载版本。使用参数绑定会导致 PostgreSQL 无法在编译阶段确定函数签名。

✅ 正确写法：使用模板字符串插值
const timeGrain = 'day'

// ✅ 正确：使用 ES6 模板字符串
const sql = `
    SELECT date_trunc('${timeGrain}', record_date) AS period_time
        FROM daily_records
`

await knex.raw(sql)  // 不需要传递 timeGrain 参数

// 实际执行的 SQL：
// SELECT date_trunc('day', record_date) AS period_time
// FROM daily_records

安全性考虑

疑问：使用 ${} 插值不是会有 SQL 注入风险吗？

答案：确实存在风险，但可以通过白名单验证来规避：

// ✅ 安全的做法：参数白名单验证
const allowedGrains = ['day', 'week', 'month', 'year']
let timeGrain = 'day'

if (_.includes(allowedGrains, dateType)) {
    timeGrain = dateType
}

// timeGrain 只能是预定义的 4 个值之一，不存在注入风险
const sql = `
    SELECT date_trunc('${timeGrain}', record_date) AS period_time
        FROM daily_records
`

🎨 混合使用最佳实践

完整示例：复杂统计查询

/**
 * 异常订单趋势统计
 * @param {string} orderStatus - 订单状态：全部/进行中/已完成
 * @param {string} startDate - 开始日期
 * @param {string} endDate - 结束日期
 * @param {string} dateType - 时间粒度：day/week/month/year
 */
async function getStatistics(data) {
    const { orderStatus, startDate, endDate, dateType } = data

    // 1️⃣ 参数白名单验证
    let timeGrain = 'day'
    if (_.includes(['day', 'week', 'month', 'year'], dateType)) {
        timeGrain = dateType
    }

    // 2️⃣ SQL 模板：混合使用插值和命名参数
    const sql = `
        WITH filtered_orders AS (
            SELECT o.id, o.start_date, o.end_date
            FROM "public".orders o
            WHERE o.deleted_at IS NULL
            -- ✅ 动态条件：使用命名参数
            AND CASE :orderStatus
                WHEN '全部' THEN
                    (o.start_date, COALESCE(o.end_date, (:endDate || ' 23:59:59')::timestamp))
                    OVERLAPS ((:startDate || ' 00:00:00')::timestamp,
                             (:endDate || ' 23:59:59')::timestamp)
                WHEN '进行中' THEN
                    o.end_date IS NULL
                WHEN '已完成' THEN
                    o.end_date IS NOT NULL
                    AND CAST(o.end_date AS DATE) >= :startDate::DATE
                    AND CAST(o.end_date AS DATE) <= :endDate::DATE
                ELSE FALSE
            END
        ),
        order_days AS (
            SELECT
                id AS order_id,
                generate_series(
                    GREATEST(COALESCE(CAST(start_date AS DATE), :startDate::date),
                            :startDate::date),
                    LEAST(COALESCE(CAST(end_date AS DATE), :endDate::date),
                         :endDate::date),
                    '1 day'
                )::date AS record_date
            FROM filtered_orders
        ),
        group_order_count AS (
            -- ⚠️ 时间粒度：使用模板字符串插值
            SELECT
                date_trunc('${timeGrain}', record_date) AS period_time,
                COUNT(DISTINCT order_id) AS cnt
            FROM order_days
            GROUP BY date_trunc('${timeGrain}', record_date)
        )
        SELECT
            TO_CHAR(
                period_time,
                -- ⚠️ CASE 常量：使用模板字符串插值
                CASE '${timeGrain}'
                    WHEN 'year' THEN 'YYYY'
                    WHEN 'month' THEN 'YYYY-MM'
                    WHEN 'week' THEN 'YYYY-MM 第W周'
                    ELSE 'YYYY-MM-DD'
                END
            ) AS date_str,
            cnt AS order_cnt
        FROM group_order_count
        ORDER BY period_time ASC
    `

    // 3️⃣ 执行查询：只传递命名参数
    const result = await knex.raw(sql, {
        orderStatus,        // 订单状态
        startDate,   // 开始日期（在 SQL 中使用了 6 次）
        endDate,     // 结束日期（在 SQL 中使用了 6 次）
        // 注意：timeGrain 不在参数对象中！
    })

    return result.rows
}

参数使用对照表

参数名使用方式原因SQL 中使用次数
orderStatus:orderStatus 命名参数数据值，可以参数绑定1次
startDate:startDate 命名参数数据值，多次使用6次
endDate:endDate 命名参数数据值，多次使用6次
timeGrain${timeGrain} 模板插值函数参数常量，必须编译时确定4次

⚠️ 常见陷阱与注意事项

1. 数组参数的处理

// ❌ 错误：直接传递数组
const sql = `SELECT * FROM orders WHERE department_id = ANY(:departmentIds)`
await knex.raw(sql, { departmentIds: [1, 2, 3] })
// 错误：PostgreSQL 无法推断参数类型

// ✅ 方法一：转换为逗号分隔字符串
const departmentIdsParam = [1, 2, 3].join(',')
const sql = `
    SELECT * FROM orders
    WHERE department_id = ANY(string_to_array(:departmentIds, ',')::int[])
`
await knex.raw(sql, { departmentIds: departmentIdsParam })

// ✅ 方法二：使用模板字符串（需要白名单验证）
const departmentIdsArray = [1, 2, 3].map(id => _.toInteger(id)).filter(id => id > 0)
const sql = `SELECT * FROM orders WHERE department_id IN (${departmentIdsArray.join(',')})`
await knex.raw(sql)

2. 空值处理

// ⚠️ 传递空字符串而不是 NULL
const branchIdsParam = branchIdArray.length > 0
    ? branchIdArray.join(',')
    : ''  // ✅ 空字符串，不是 null

const sql = `
    SELECT * FROM orders
    WHERE CASE
        WHEN :branchIds = '' THEN TRUE
        ELSE branch_id = ANY(string_to_array(:branchIds, ',')::int[])
    END
`

// ✅ 传递空字符串可以避免类型推断问题
await knex.raw(sql, { branchIds: branchIdsParam })

3. 动态表名和列名

// ❌ 错误：命名参数不能用于表名
const sql = `SELECT * FROM :tableName WHERE id = :id`
await knex.raw(sql, { tableName: 'users', id: 1 })
// 错误：会被解释为字符串 'users' 而不是表名

// ✅ 正确：使用模板字符串 + 白名单验证
const allowedTables = ['users', 'orders', 'employees']
const tableName = allowedTables.includes(userInput) ? userInput : 'users'

const sql = `SELECT * FROM "${tableName}" WHERE id = :id`
await knex.raw(sql, { id: 1 })

📊 性能对比

命名参数 vs 模板字符串

// 测试场景：执行 1000 次查询

// 方式一：纯命名参数（推荐）
console.time('命名参数')
for (let i = 0; i < 1000; i++) {
    await knex.raw(`SELECT * FROM orders WHERE id = :id`, { id: i })
}
console.timeEnd('命名参数')
// 命名参数: 3250ms

// 方式二：模板字符串
console.time('模板字符串')
for (let i = 0; i < 1000; i++) {
    await knex.raw(`SELECT * FROM orders WHERE id = ${i}`)
}
console.timeEnd('模板字符串')
// 模板字符串: 3280ms (每次都是新的 SQL，无法利用查询计划缓存)

结论：

  • 命名参数可以利用 PostgreSQL 的 Prepared Statement 缓存
  • 模板字符串每次都生成新的 SQL，略慢
  • 差异不大，但大量重复查询时命名参数更优

🎓 最佳实践总结

✅ 推荐做法

  1. 优先使用命名参数：对于所有数据值类型的参数
  2. 白名单验证：对于必须使用模板字符串的参数，严格进行白名单验证
  3. 参数复用：利用命名参数可以多次使用的特性
  4. 空值处理：数组参数传递空字符串而不是 null

❌ 避免做法

  1. 不要对用户输入直接使用模板字符串插值
  2. 不要对数据值使用模板字符串（除非有特殊原因）
  3. 不要在命名参数中使用表名、列名、函数常量

📋 决策流程图

参数需要传递到 SQL 中？
    ├─ 是数据值（字符串、数字、日期等）？
    │   └─ ✅ 使用命名参数 :paramName
    │
    ├─ 是表名、列名、函数常量？
    │   ├─ 来自用户输入？
    │   │   └─ ✅ 白名单验证 + 模板字符串 ${param}
    │   └─ 来自系统配置？
    │       └─ ✅ 直接使用模板字符串 ${param}
    │
    └─ 是 SQL 关键字？
        └─ ✅ 使用模板字符串 ${param}

🔗 扩展阅读

📝 总结

在 Knex.js 的 raw() 方法中：

  • 命名参数（:paramName）适用于数据值，安全且可复用
  • 模板字符串（${param}）适用于 SQL 结构元素，需要白名单验证
  • 混合使用两者可以兼顾安全性和灵活性
  • date_trunc() 等函数的常量参数必须使用模板字符串

正确理解和使用这两种方式，可以写出既安全又高效的 SQL 查询代码。

Knex.js安全编程指南：防止SQL注入和其他安全威胁
gitblog_00928的博客
11-16 832
Knex.js是一个强大的Node.js SQL查询构建器和迁移工具，支持多种数据库系统。在当今数据驱动的应用开发中，**SQL注入防护**和**数据库安全**是每个开发者必须掌握的核心技能。本文将为您提供全面的Knex.js安全编程指南，帮助您构建更加安全可靠的应用程序。 ## 🔒 SQL注入防护：使用参数化查询 SQL注入是最常见的安全威胁之一，Knex.js通过**参数绑定机制**自动
mysql和knexjs_mysql – Knex.js会阻止sql注入吗？
weixin_39669761的博客
01-27 357
请仔细阅读knex文档,了解如何将值传递给knex raw(http://knexjs.org/#Raw).如果要将值作为参数绑定传递给raw,如：knex.raw('select * from foo where id = ?', [1])在这种情况下,参数和查询字符串分别传递给数据库驱动程序保护SQL注入查询.其他查询构建器方法内部始终使用绑定格式,因此它们也是安全的.要查看如何将某个查询传递...
Knex.js安全最佳实践：防止SQL注入和其他安全威胁
gitblog_00852的博客
11-16 547
Knex.js作为Node.js生态中最流行的SQL查询构建器，提供了强大的数据库操作能力。然而，在使用Knex.js时，**SQL注入防护**和**数据安全保护**是开发人员必须重视的核心安全议题。本文将为您详细介绍Knex.js的安全最佳实践，帮助您构建更加安全的应用程序。 ## 🔐 理解Knex.js的内置安全机制 Knex.js内置了多重安全防护机制，其中最重要的就是**自动参数绑定
Knex.js多数据库适配：从PostgreSQL到Oracle
gitblog_01183的博客
08-23 1082
Knex.js通过精心设计的方言系统架构实现了强大的多数据库适配能力，支持包括PostgreSQL、Oracle、MySQL/MariaDB和SQLite等多种数据库。该系统采用基于抽象基类的插件式设计模式，通过统一的接口定义和差异化的具体实现，在保持API一致性的同时为不同数据库提供最优化的查询生成和执行策略。文章详细解析了Knex.js的方言系统架构、各数据库的特有功能与优化策略，以及轻量级部...
【Express.js】sql-knex 增删改查
m0_51810668的博客
06-18 2009
使用 knex.js 进行基础的sql crud.
Knex.js高级查询技巧：Join、事务与复杂操作
gitblog_00967的博客
06-01 357
本文深入探讨Knex.js的高级数据库操作功能，涵盖多表Join操作的各种类型与实现方式，详细解析事务处理机制与回滚策略，介绍批量数据操作与高效分页技术，以及原生SQL与自定义函数的集成应用。通过实际代码示例和性能优化建议，帮助开发者掌握复杂业务场景下的数据库操作最佳实践。 ## 多表Join操作与关联查询实现 在现代Web应用开发中，数据库查询往往需要处理多个表之间的关联关系。Knex.j...
Awesome Node.js数据库迁移：Schema管理与版本控制
gitblog_00454的博客
09-18 1027
在企业级Node.js应用开发中，数据库Schema变更管理常面临三大核心挑战：**协同开发冲突**（多开发者同时修改Schema导致合并困难）、**生产环境中断**（直接执行ALTER TABLE导致服务不可用）、**回滚风险**（缺乏安全的版本回退机制）。根据2024年Node.js开发者调查报告，68%的生产事故与非结构化数据库变更相关，平均恢复时间超过45分钟。 本文将系统讲解基于Nod...
objection.js中的数据库连接健康检查：确保服务可用性
gitblog_01108的博客
11-05 680
在基于Node.js的应用开发中，数据库连接的稳定性直接影响服务可用性。objection.js作为一款SQL友好的ORM（Object-Relational Mapping，对象关系映射）工具，通过Knex.js管理数据库连接。本文将详细介绍如何在objection.js中实现数据库连接健康检查，确保服务持续稳定运行。 ## 数据库连接基础 objection.js依赖Knex.js（一款S...
objection.js中的数据一致性：分布式事务解决方案
gitblog_00582的博客
11-05 216
在分布式系统中，保持数据一致性是开发者面临的重要挑战。当多个数据库或服务参与同一业务流程时，如何确保所有操作要么全部成功，要么全部失败，成为保障系统稳定性的关键。objection.js作为Node.js生态中SQL友好的ORM（对象关系映射）工具，提供了强大的事务支持来应对这一挑战。本文将深入探讨objection.js中的事务机制，以及如何利用这些机制解决分布式环境下的数据一致性问题。 ##...
knex-migrate-sql-file:使用sql文件而不是`knex.schema`方法
04-30
导出up和down函数，该函数对带有相同文件名的.up.sql和.down.sql附加SQL文件执行knex.raw()方法。 概要 创建knex迁移文件 将此库导入迁移文件 创建SQL文件 $ knex migrate:make add-user-table Created Migration: ...
knex-soql:SOQL方言Knex.js查询生成器
05-01
适用于Knex.js的SOQL方言 用于构建和执行Salesforce查询（SOQL）的Knex.js方言 安装 npm install knex-soql 用法 const client = require ( 'knex-soql' ) ; const knex = require ( 'knex' ) ( { client , ...
booster:一个Knex.js实用程序，使建立数据库超级容易
03-29
如果未设置NODE_ENV ，则booster默认为knexfile.js的开发配置： ❯ yarn booster :running_shoe: Migrations run! :seedling: Database seeded! 设置test数据库： ❯ yarn booster test :rocket: Database ...
typed-knex:用于Knex.js的TypeScript包装器
05-02
由于TypeScript 4.1支持模板文字字符串类型，因此不再需要函数语法。 现在，您可以在保持类型安全的同时使用字符串。 从版本4开始删除函数语法。 为了帮助升级，您可以运行npx typed-knex -u string-parameters来...
ssh连接慢的原因调查及解决方案
qq_17009639的博客
12-13 661
dbus的服务重启后，systemd-logind服务没有重启导致，可以查看systemctl status systemd-logind的状态，解决方法就是重启该服务 systemctl restart systemd-logind。重启systemd-logind服务后，发现ssh可以秒连接了。1.通过[-v]参数，查看ssh连接的具体过程。2.打开服务器的系统日志，查看错误日志。首先观察有没有出现耗时的操作？
Linux的shell命令
2501_93649289的博客
12-18 853
进程是一个独立可调度的任务，是一个程序一次的执行过程。进程有四种状态：运行态：此时进程正在运行或准备运行，等待态：此时的进程正在等待一个事件的发生或者系统的某种资源，有可中断和不可中断两种。停止态：此时进程被停止。死亡态：这是一个已经终止的进程，但是还在进程向量数组中占有一个stack_struct结构。ps aux:显示进程。其中USER:进程的所有者，哪个用户运行的进程。PID：进程的进程号，1是系统的守护进程。%CPU:占用cpu的百分比。%MEM：占用内存的百分比。VSZ：虚拟内存的大小。
Nvidia H100 算力服务器 Cuda、FabricManager、Container 安装和升级
最新发布
TFATS的博客
12-18 779
本文介绍了在Ubuntu 22.04系统上安装NVIDIA显卡驱动的详细步骤。首先确保系统环境更新至最新版本，检查显卡型号并彻底卸载旧驱动。重点说明了如何禁用系统自带的nouveau驱动以避免冲突。提供两种安装方法：系统推荐安装（自动选择版本）和指定版本安装（需手动下载驱动包），均包含必要的依赖安装和权限设置。最后强调安装完成后需重启系统，并附带了NVIDIA Fabric Manager的部署说明。文中包含完整的命令行操作和参数解释，适用于H100等NVIDIA显卡的驱动安装场景。
Docker+Nginx+Node.js 全栈容器化部署
学不会shader
12-14 965
本文介绍了使用Docker Compose实现Nginx+Node.js全栈容器化部署的方案。前端采用Unity WebGL构建，后端基于Node.js提供REST API。重点解决了服务发现和网络配置问题：通过Nginx反向代理处理未知IP地址问题，使用路径请求方式获取服务器信息，并通过Docker Compose环境变量配置实现灵活部署。文章提供了完整的项目结构说明和配置示例，包括Node.js服务的Docker Compose文件、Express应用代码以及跨域处理策略，为容器化部署提供了实用参考。
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值