使用 Node.js 动态生成 SQL 查询

自从我发布了Slonik（Node.js 的 PostgreSQL 客户端）并撰写了一篇颇具争议的《停止使用 Knex.js》文章（简而言之：查询构建器旨在作为 ORM 的构建块；当大多数查询是静态的时，它们不会增加价值）以来，我经常被问到——那么如何生成动态查询呢？我将通过分享几个真实的例子来回答这个问题。

本文中的所有查询都是实际业务中使用的实际查询，Applaudience严重依赖于 PostgreSQL。

免责声明：(1) 所有示例仅讨论 SQL 注入威胁。授权逻辑（例如，将用户有权访问的列列入白名单）不在本文讨论范围内。(2) 所有陈述均假设 Slonik 实现中没有错误。

具有动态值绑定的静态查询

如果您的查询逻辑不会根据用户的输入而改变，那么只需使用sql标记模板文字构造 SQL 查询，例如



sql`
  SELECT c1.country_id
  FROM cinema_movie_name cmn1
  INNER JOIN cinema c1 ON c1.id = cmn1.cinema_id
  WHERE cmn1.id = ${cinemaMovieNameId}
`;

如果您使用的是 Slonik，则可以安全地将值作为模板文字占位符传递。sql它将解释所有占位符标记并构建最终的 SQL 查询。在这种情况下，查询的唯一动态部分是值绑定本身，因此最终查询是：



SELECT c1.country_id
FROM cinema_movie_name cmn1
INNER JOIN cinema c1 ON c1.id = cmn1.cinema_id
WHERE cmn1.id = $1

查询和绑定值将分别发送到 PostgreSQL：没有 SQL 注入的风险。

绑定值列表

当查询输入是值列表时（例如检索与多个标识符匹配的行时），则可以使用sql.valueList，例如



sql`
  SELECT m1.*
  FROM movie m1
  WHERE m1.id IN (${sql.valueList(movieIds)})
`;

这将生成具有动态值绑定集的查询，即movieIds发送[1, 2, 3]到 PostgreSQL 的查询将是：



SELECT m1.*
FROM movie m1
WHERE m1.id IN ($1, $2, $3)

然而，尽管这是一种常见的模式，我不建议使用它。相反，请使用sql.array，例如



sql`
  SELECT m1.*
  FROM movie m1
  WHERE m1.id = ANY(${sql.array(movieIds, 'int4')})
`;

这将生成一个固定长度的查询，该查询不会根据其输入而改变，即



SELECT m1.*
FROM movie m1
WHERE m1.id = ANY($1::"int4"[])

继续阅读sql.arrayvssql.valueList。

使用动态列进行查询

如果查询结果涉及依赖于用户输入的列，则使用sql.identifier生成标识这些列的 SQL，例如

（注意：这不是业务中使用的实际查询。请参阅下一段。）



sql`
  SELECT m1.id, ${sql.identifier(['m1', movieTableColumnName])}
  FROM movie m1
  WHERE
    m1.id = ${moveId}
`;

此查询将生成一个仅选择 1 个动态标识列的查询。不存在 SQL 注入风险，也就是说，即使生成查询的逻辑以movieTableColumnName某种方式被破坏，最坏的情况也不过是查询攻击者能够返回m1别名下的任何列，或使用无效的列标识符值执行查询（这两种情况都存在风险；业务逻辑不在本文讨论范围内）。

你不能这么做，只是因为你能这么做。当你的应用程序需要根据用户的查询返回不同的列时，最好选择业务逻辑范围内的所有列，然后选择所需列的值。例如，如果后一个查询的目的是根据返回不同的电影标识符movieTableColumnName，那么最好编写一个静态查询：



sql`
  SELECT
    m1.id,
    m1.foreign_comscore_id,
    m1.foreign_imdb_id,
    m1.foreign_metacritic_id
    m1.foreign_rottentomatoes_id,
    m1.foreign_tmdb_id,
    m1.foreign_webedia_id
  FROM movie m1
  WHERE
    m1.id = ${moveId}
`;

后者确实会在每次查询时返回一些多余的数据，但它有几个优点：

它降低了 SQL 注入的风险（无论您多么信任代码生成逻辑，静态代码总是比动态代码更安全）。
它只生成一个条目。随着应用程序的扩展，pg_stat_statements您将学会尽可能少地使用查询。pg_stat_statements

具有多个动态列的查询

与上述相同，但是sql.identifierList。

嵌套动态 SQL 查询

sql标记模板文字可以嵌套，例如

（注意：业务中使用的实际查询的简化版本。）



const futureEventEventChangeSqlToken = sql`
  SELECT
    ec1.event_id,
    ec1.seat_count,
    ec1.seat_sold_count
  FROM event_change_future_event_view ec1
`;

sql`
  SELECT
    event_id,
    seat_count,
    seat_sold_count
  FROM (
    ${futureEventEventChangeSqlToken}
  ) AS haystack
  WHERE ${paginatedWhereSqlToken}
  ORDER BY ${orderSqlToken}
  LIMIT ${limitSqlToken}
`

这允许将预绑定的 SQL 查询作为“一等公民”在程序中传递。当需要隔离 SQL 生成逻辑以进行测试，或在查询之间共享大型 SQL 片段，或只是想减少代码复杂性集中在一处时，这非常方便。

注入动态 SQL 片段

sql.raw用于注入动态 SQL 片段，即



sql`
  SELECT ${sql.raw('foo bar baz')}
`

转换为（无效）查询：



SELECT foo bar baz

sql与前面使用标记模板的示例不同，sql.raw它并不安全——它允许使用用户输入创建动态 SQL。

目前还没有已知的使用嵌套绑定表达式（“嵌套动态 SQL 查询”中描述）或其他现有查询构建方法sql.raw之一未涵盖的生成查询的用例。作为执行外部存储的静态（例如，存储在文件中的查询）的机制存在。sqlsql.raw

使用动态比较谓词成员或运算符的查询

如果查询中存在的比较谓词运算符是动态的，则使用sql.comparisonPredicate，例如

（注意：这不是业务中使用的实际查询。）



sql`
  SELECT
    c1.id,
    c1.nid,
    c1.name
  FROM cinema c1
  WHERE
    ${sql.comparisonPredicate(
      sql`c1.name`,
      nameComparisonOperator,
      nameComparisonValue
    )}
`;

nameComparisonOperator可以是诸如=、、等值>。<假设nameComparisonOperator是“=”，则结果查询将是：



SELECT
  c1.id,
  c1.nid,
  c1.name
FROM cinema c1
WHERE
  c1.name = $1

后者是极其罕见的用例，几乎完全用于构建更高级的 SQL 抽象工具（例如 ORM）。它可能对“高级搜索”场景有用，但请继续阅读以熟悉其他模式（参见sql.booleanExpression）。

使用动态 WHERE 子句成员进行查询

如果WHERE子句成员的存在是动态的，则使用sql.booleanExpression。



const findCinemas = (root, parameters, context) => {
  const booleanExpressions = [
    sql`TRUE`,
  ];

  if (parameters.input.query) {
    const query = parameters.input.query;

    if (query.countryId !== undefined) {
      booleanExpressions.push(
        sql`c2.id = ${query.countryId}`
      );
    }

    if (query.nid !== undefined) {
      booleanExpressions.push(
        sql`c1.nid % ${query.nid}`
      );
    }

    if (query.name !== undefined) {
      booleanExpressions.push(
        sql`c1.name % ${query.name}`
      );
    }
  }

  const whereSqlToken = sql.booleanExpression(
    booleanExpressions,
    'AND'
  );

  return context.pool.any(sql`
    SELECT
      c1.id,
      c1.nid,
      c1.name,
      c2.code_alpha_2 country_code,
      c2.name country_name
    FROM cinema c1
    INNER JOIN country c2 ON c2.id = c1.country_id
    WHERE ${whereSqlToken}
  `);
},

findCinemas是 GraphQL 解析器的一个实现。查询的 WHERE 子句由 3 个可能的布尔表达式组合构成。与 Slonik 中所有其他查询构建方法一样，所有表达式都可以嵌套：您可以将其他布尔表达式作为布尔表达式的成员，甚至可以使用sql带标记的模板文字构造 SQL 表达式。

概括

这些示例涵盖了所有常见的动态 SQL 构建场景，并提供了足够的知识来解释 Slonik 的工作原理，使读者能够继续熟悉Slonik 提供的其他查询构建方法。本文的主要目的是说明 Slonik 提供了一种安全的抽象来构建 SQL 查询，同时保持查询的静态部分完整无缺。

如果您重视我的工作并希望看到 Slonik 和我的许多其他开源项目不断改进，那么请考虑成为赞助人：

最后，我错过了一个您希望我介绍的用例场景，请在评论中提及它，我会很乐意将其包括在内。

文章来源：https://dev.to/gajus/dynamically-generate-sql-queries-using-node-js-2c1g