Sequelize原生SQL全文搜索报错求助

问题场景 #

尝试通过Sequelize执行原生SQL实现PostgreSQL全文搜索，数据库中存储tsvector类型的列名为searchable。发送GET请求http://localhost:8080/api/books?searchable=humor时，应用崩溃，日志显示SQL语句中to_tsquery的参数被解析为[object Object]，触发语法错误（错误码42601）。

原控制器代码 #

const db = require("../../models");
const Book = db.book;
const User = db.user;
const Sequelize = require('sequelize');
const { sequelize } = require('../../models/index.js');

exports.getAllBooks = async (req, res) => {
    console.log('query: ', req.query);
    let books;
    if (Object.keys(req.query).length === 0) {
        books = await Book.findAll();
        res.json(books);
    } else {
        [books, metadata] = await sequelize.query(`
            SELECT ('title')
            FROM Book
            WHERE searchable @@ to_tsquery(${req.query});
        `);
        res.json(books);
    }
};

错误原因 #

1. 参数提取错误：req.query是包含所有查询参数的对象（此处为{ searchable: 'humor' }），直接拼接进SQL会被转为字符串[object Object]，导致SQL语法错误。
2. SQL注入风险：直接拼接用户输入到SQL语句中，属于典型安全漏洞，可能被攻击者利用执行恶意SQL。

修复方案 #

1. 提取正确查询参数 #

从req.query中取出具体的搜索关键词req.query.searchable，而非整个对象。

2. 使用Sequelize参数绑定 #

通过replacements选项做参数绑定，自动处理字符串转义，同时避免SQL注入。

修复后的代码：

const db = require("../../models");
const Book = db.book;
const User = db.user;
const Sequelize = require('sequelize');
const { sequelize } = require('../../models/index.js');

exports.getAllBooks = async (req, res) => {
    console.log('query: ', req.query);
    let books;
    if (Object.keys(req.query).length === 0) {
        books = await Book.findAll();
        res.json(books);
    } else {
        const searchTerm = req.query.searchable;
        // 提前处理空关键词的情况
        if (!searchTerm) {
            return res.status(400).json({ error: '搜索关键词不能为空' });
        }
        // 使用命名参数绑定执行SQL
        [books, metadata] = await sequelize.query(`
            SELECT title
            FROM Book
            WHERE searchable @@ to_tsquery(:searchTerm);
        `, {
            replacements: { searchTerm }
        });
        res.json(books);
    }
};

额外优化建议 #

• 指定文本搜索配置：如果需要针对特定语言分词，可以在to_tsquery中指定配置，例如to_tsquery('english', :searchTerm)，匹配数据库的文本搜索配置。
• 更灵活的搜索逻辑：若需要支持空格分隔的多关键词搜索，可改用plainto_tsquery替代to_tsquery，它会自动处理关键词的空格分隔，例如plainto_tsquery(:searchTerm)。
• 参数校验：可以对searchTerm做进一步格式校验，避免无效输入。

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