使用@vercel/postgres模板字面量插值时出现语法错误排查

在Next.js API路由中使用@vercel/postgres库构建带插值的动态SQL查询时，遇到了语法错误，错误信息如下：

⨯ unhandledRejection: NeonDbError: syntax error at or near "$1"

at execute (file:///Users/nic/Desktop/proj-beta/node_modules/@neondatabase/serverless/index.mjs:1539:48)

at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {

code: '42601',

sourceError: undefined

}
GET /api/all-courses/ 500 in 654ms

原代码如下：

import { sql } from '@vercel/postgres';

export default async function handler(request, response) {
  try {
    const { page, limit, short, search, category, dateSort } = request.query;
    const pageNumber = parseInt(page) || 1;
    const offset = (pageNumber - 1) * limit;

    let query = sql`
      SELECT c.*, u.first_name, u.last_name, u.profile_photo, COUNT(e.id) AS enrolments, cat.name AS category_name
      FROM courses c
      LEFT JOIN users u ON c.user_id = u.id
      LEFT JOIN enrolments e ON c.id = e.course_id
      LEFT JOIN categories cat ON c.category_id = cat.id
      WHERE c.approved = true
    `;

    if (search) {
      query = sql`${query} AND (c.title LIKE ${sql.literal(`%${search}%`)} OR c.short_desc LIKE ${sql.literal(`%${search}%`)})`
    }

    if (category) {
      query = sql`${query} AND c.category_id = ${category}`;
    }

    let orderBy = [];
    if (short) {
      orderBy.push(`c.latest_price ${short}`);
    }

    const currentDate = new Date();
    if (dateSort) {
      switch (dateSort) {
        case 'recent':
          orderBy.push('c.created_at DESC');
          break;
        case 'in_month':
          query = sql`${query} AND c.created_at >= ${sql`${currentDate.getFullYear()}-${currentDate.getMonth() + 1}-01`}`;
          break;
        case 'in_year':
          query = sql`${query} AND c.created_at >= ${sql`${currentDate.getFullYear()}-01-01`}`;
          break;
      }
    } else {
      orderBy.push('c.created_at ASC');
    }

    if (orderBy.length > 0) {
      query = sql`${query} ORDER BY ${sql.join(orderBy, ', ')}`;
    }

    query = sql`${query} LIMIT ${limit} OFFSET ${offset}`;

    const { rows: courses, rowCount: coursesCount } = await query;

    const totalPages = Math.ceil(coursesCount / limit);

    return response.status(200).json({ courses, totalPages, coursesCount });
  } catch (error) {
    return response.status(500).json({ message: error.message });
  }
}

1. SQL片段拼接时缺少空格：初始SQL的WHERE c.approved = true结尾没有空格，拼接AND条件时会生成WHERE c.approved = trueAND(...)这类语法错误的SQL，导致数据库解析参数位置时出错。
2. sql.literal的错误使用：sql.literal会生成带引号的字符串，但将其插入sql模板字面量时，会被再次当作参数绑定，导致SQL语法混乱。
3. 日期条件的错误拼接：直接用sql模板拼接日期字符串（如${currentDate.getFullYear()}-${currentDate.getMonth() + 1}-01）会导致参数绑定错误，应该直接传入日期对象或使用数据库的日期函数。
4. ORDER BY部分的参数处理错误：sql.join将排序规则数组当作参数绑定，而不是直接作为SQL的一部分，导致排序语法错误。
5. 聚合函数缺少GROUP BY：使用COUNT(e.id)聚合函数但未指定GROUP BY子句，PostgreSQL会拒绝执行这类查询。

import { sql } from '@vercel/postgres';

export default async function handler(request, response) {
  try {
    const { page, limit, short, search, category, dateSort } = request.query;
    const pageNumber = parseInt(page) || 1;
    const limitNum = parseInt(limit) || 10;
    const offset = (pageNumber - 1) * limitNum;

    // 初始SQL结尾保留空格，避免拼接时语法错误
    let query = sql`
      SELECT c.*, u.first_name, u.last_name, u.profile_photo, COUNT(e.id) AS enrolments, cat.name AS category_name
      FROM courses c
      LEFT JOIN users u ON c.user_id = u.id
      LEFT JOIN enrolments e ON c.id = e.course_id
      LEFT JOIN categories cat ON c.category_id = cat.id
      WHERE c.approved = true 
    `;

    if (search) {
      // 直接在sql模板中处理LIKE的通配符，库会自动转义参数
      query = sql`${query} AND (c.title LIKE ${`%${search}%`} OR c.short_desc LIKE ${`%${search}%`}) `;
    }

    if (category) {
      // 确保category是数字类型，避免类型错误
      const categoryId = parseInt(category);
      query = sql`${query} AND c.category_id = ${categoryId} `;
    }

    let orderBy = [];
    if (short) {
      // 用sql.raw标记排序规则为原生SQL，避免被当作参数绑定
      orderBy.push(sql.raw(`c.latest_price ${short}`));
    }

    const currentDate = new Date();
    if (dateSort) {
      switch (dateSort) {
        case 'recent':
          orderBy.push(sql.raw('c.created_at DESC'));
          break;
        case 'in_month':
          // 构造月初日期对象，直接传入参数
          const monthStart = new Date(currentDate.getFullYear(), currentDate.getMonth(), 1);
          query = sql`${query} AND c.created_at >= ${monthStart} `;
          break;
        case 'in_year':
          // 构造年初日期对象，直接传入参数
          const yearStart = new Date(currentDate.getFullYear(), 0, 1);
          query = sql`${query} AND c.created_at >= ${yearStart} `;
          break;
      }
    } else {
      orderBy.push(sql.raw('c.created_at ASC'));
    }

    if (orderBy.length > 0) {
      // 用sql.join拼接排序规则，每个元素是sql.raw类型
      query = sql`${query} ORDER BY ${sql.join(orderBy, ', ')} `;
    }

    // 补充GROUP BY子句，匹配所有非聚合字段
    query = sql`${query} GROUP BY c.id, u.id, cat.id `;

    // 确保limit和offset是数字类型
    query = sql`${query} LIMIT ${limitNum} OFFSET ${offset}`;

    const { rows: courses } = await query;
    // 单独查询总条数，避免分页时聚合函数的干扰
    const countResult = await sql`SELECT COUNT(*) FROM courses WHERE approved = true`;
    const coursesCount = parseInt(countResult.rows[0].count);
    const totalPages = Math.ceil(coursesCount / limitNum);

    return response.status(200).json({ courses, totalPages, coursesCount });
  } catch (error) {
    console.error(error);
    return response.status(500).json({ message: error.message });
  }
}

• SQL片段空格处理：每个拼接的SQL片段结尾添加空格，确保拼接后的SQL语法连贯。
• LIKE查询参数处理：直接将带通配符的字符串传入sql模板，@vercel/postgres会自动完成参数绑定和转义，无需手动使用sql.literal。
• 日期参数处理：构造Date对象直接传入，数据库会自动解析为合法日期类型，避免字符串拼接带来的格式错误。
• ORDER BY规则处理：使用sql.raw将排序规则标记为原生SQL内容，避免被当作参数绑定；同时要确保short和dateSort是可控的合法值，防止SQL注入。
• 分页参数类型校验：将limit和offset转为数字类型，避免字符串参数导致的语法错误。
• GROUP BY补充：由于使用了聚合函数COUNT(e.id)，必须添加GROUP BY子句，指定所有非聚合的查询字段，符合PostgreSQL的语法要求。

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