postgresql 对全文搜索原始SQL查询进行序列化时出错

gmxoilav  于 2023-01-25  发布在  PostgreSQL
关注(0)|答案(1)|浏览(119)

尝试在sequelize db中使用原始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 {
        [books, metadata] = await sequelize.query(`
            SELECT ('title')
            FROM Book
            WHERE searchable @@ to_tsquery(${req.query});
        `);
        res.json(books);
    }
};

获取请求至
网址:
DB中的TSVector列被命名为“可搜索的
日志响应:

Server is running on port 8080.
query:  { searchable: 'humor' }
Executing (default): SELECT ('title')
            FROM Book
            WHERE searchable @@ to_tsquery([object Object]);
node:internal/process/promises:279
            triggerUncaughtException(err, true /* fromPromise */);
            ^

Error
    at Query.run (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/sequelize/lib/dialects/postgres/query.js:50:25)
    at /Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/sequelize/lib/sequelize.js:314:28
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async exports.getAllBooks (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/app/controllers/book.controller.js:16:29) {
  name: 'SequelizeDatabaseError',
  parent: error: syntax error at or near "["
      at Parser.parseErrorMessage (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/pg-protocol/dist/parser.js:287:98)
      at Parser.handlePacket (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/pg-protocol/dist/parser.js:126:29)
      at Parser.parse (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/pg-protocol/dist/parser.js:39:38)
      at Socket.<anonymous> (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/pg-protocol/dist/index.js:11:42)
      at Socket.emit (node:events:513:28)
      at addChunk (node:internal/streams/readable:315:12)
      at readableAddChunk (node:internal/streams/readable:289:9)
      at Socket.Readable.push (node:internal/streams/readable:228:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 90,
    severity: 'ERROR',
    code: '42601',
    detail: undefined,
    hint: undefined,
    position: '83',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'scan.l',
    line: '1180',
    routine: 'scanner_yyerror',
    sql: "SELECT ('title')\n" +
      '            FROM Book\n' +
      '            WHERE searchable @@ to_tsquery([object Object]);',
    parameters: undefined
  },
  original: error: syntax error at or near "["
      at Parser.parseErrorMessage (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/pg-protocol/dist/parser.js:287:98)
      at Parser.handlePacket (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/pg-protocol/dist/parser.js:126:29)
      at Parser.parse (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/pg-protocol/dist/parser.js:39:38)
      at Socket.<anonymous> (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/pg-protocol/dist/index.js:11:42)
      at Socket.emit (node:events:513:28)
      at addChunk (node:internal/streams/readable:315:12)
      at readableAddChunk (node:internal/streams/readable:289:9)
      at Socket.Readable.push (node:internal/streams/readable:228:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 90,
    severity: 'ERROR',
    code: '42601',
    detail: undefined,
    hint: undefined,
    position: '83',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'scan.l',
    line: '1180',
    routine: 'scanner_yyerror',
    sql: "SELECT ('title')\n" +
      '            FROM Book\n' +
      '            WHERE searchable @@ to_tsquery([object Object]);',
    parameters: undefined
  },
  sql: "SELECT ('title')\n" +
    '            FROM Book\n' +
    '            WHERE searchable @@ to_tsquery([object Object]);',
  parameters: {}
}
[nodemon] app crashed - waiting for file changes before starting...
ldioqlga

ldioqlga1#

首先,您传递了整个查询对象,而不是仅传递searchable prop。
其次,最好使用bind选项来安全地传递这些值(记住SQL注入!)

await sequelize.query(`
            SELECT ('title')
            FROM Book
            WHERE searchable @@ to_tsquery($searchable);
        `, {
   type: QueryTypes.SELECT,
   bind: {
     searchable: req.query.searchable
   }
})

相关问题