如何在Node.js中为BigQuery带三引号的语句传递参数

我来帮你搞定这个需求！要从Node.js端把dateFrom和dateTo动态传给BigQuery的远程函数，咱们可以用BigQuery的命名参数机制，既避免手动拼接字符串的风险，又能灵活传递变量。下面是具体的实现步骤和代码示例：

核心思路 #

1. 用BigQuery的@参数名语法定义查询参数，在Node.js调用时传入对应的值。
2. 借助BigQuery内置的JSON_BUILD_OBJECT函数，安全构建远程函数需要的JSON参数（替代硬编码的JSON字符串）。
3. 注意在Node.js模板字符串中转义特殊符号，避免被提前解析。

完整代码示例 #

Node.js端代码 #

const { BigQuery } = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function runRemoteFunctionQuery() {
  // 从Node.js端定义的动态参数
  const dateFrom = '2022-06-01T08:00:00.000Z';
  const dateTo = '2023-01-01T07:59:59.999Z';

  // 构建参数化的BigQuery查询语句
  const query = `
    with postgres_things as (
      select
          \`remote_function_name\`(
            'db_name'
            , """
                select
                  name
                  , age
                  , sex
                  , created_at
                from users
                where
                  created_at >= \${dateFrom}::timestamptz
                  and created_at <= \${dateTo}::timestamptz
              """
            , JSON_BUILD_OBJECT('dateFrom', @dateFrom, 'dateTo', @dateTo)
          ) as things
    ),
    -- 这里继续你的后续查询逻辑（比如处理返回的things数据）
    select * from postgres_things
  `;

  // 配置查询选项，传入参数
  const queryOptions = {
    query: query,
    params: {
      dateFrom: dateFrom,
      dateTo: dateTo
    }
  };

  // 执行查询并处理结果
  const [rows] = await bigquery.query(queryOptions);
  console.log('查询结果:', rows);
}

// 执行函数
runRemoteFunctionQuery().catch(console.error);

关键细节说明 #

• 参数传递: 用@dateFrom和@dateTo作为BigQuery的命名参数，在queryOptions.params中传入Node.js的变量值，这是BigQuery官方推荐的参数化方式，能有效防止SQL注入。
• JSON参数构建: 用JSON_BUILD_OBJECT函数动态生成远程函数需要的JSON参数，比手动拼接字符串更安全，还能自动处理JSON格式的转义问题。
• 符号转义: 在Node.js的模板字符串中，和${需要用反斜杠转义（\和${），避免被Node.js提前解析成模板变量，确保这些符号原封不动传到BigQuery中。

不推荐的方式（仅作参考） #

如果你实在要手动拼接JSON字符串（不建议，存在SQL注入和格式错误风险），可以这么写，但一定要确保参数值经过严格的转义：

// 不推荐：手动拼接JSON，存在安全风险
const query = `
  with postgres_things as (
    select
        \`remote_function_name\`(
          'db_name'
          , """
              select
                name
                , age
                , sex
                , created_at
              from users
              where
                created_at >= \${dateFrom}::timestamptz
                and created_at <= \${dateTo}::timestamptz
            """
          , '{"dateFrom":"${dateFrom.replace(/"/g, '\\"')}","dateTo":"${dateTo.replace(/"/g, '\\"')}"}'
        ) as things
  ),
  -- 后续查询逻辑
  select * from postgres_things
`;

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