使用 SQLAlchemy 通过用户输入安全地创建实例化视图-解网

问：

我正在尝试在Postgres（使用TimescaleDB）中创建物化视图，用户可以在其中控制所选字段之一。我知道物化视图不能参数化为准备好的语句，因为需要保存这些参数以备以后更新视图时使用（感谢这个答案）。

我正在寻找一种方法来说服自己，在尝试做这样的事情时，我的代码是安全的：

from sqlalchemy import text
from databases import Database
db = Database('postgres+asyncpg://localhost')
await db.connect()

path = '{some_key,nested_key}'  # User controlled string

await db.execute(text(
    '''
    CREATE MATERIALIZED VIEW test_view WITH (timescaledb.continuous) AS
      SELECT
        time_bucket(interval '5 minutes', timestamp) AS bucket,
        avg(:variable_part)
      FROM metrics
      GROUP BY bucket
      WITH NO DATA;
    ''')
    .bindparams(variable_part=f"results#>>'{path}'")  # JSONB path selection 
)

当然这个例子不起作用，我的驱动程序（asyncpg）正确报告。FeatureNotSupportedError: materialized views may not be defined using bound parameters

我实际上并不想要绑定参数，我想使用提供的值定义一个具体化视图。如果不是担心注入，我们只会在查询字符串本身中插入结果路径。

我是否在使用某种正则表达式手动验证用户控制的变量时遇到困难？我显然需要担心单引号、括号、大括号、注释字符、转义符等。path

最好的出路是什么？

PostgreSQL SQLalchemy SQL注入物化视图

如果我正确理解您的代码，则会将：result_path 中的字符串转换为浮点数。如果是这种情况，则只需在尝试创建 MV 之前验证它是否为有效的数值。以下内容似乎验证了这一点：（至少在 Postgres 正则表达式引擎中）。请参阅演示。对不起，我对 SQLAlchemy 的了解不够，无法尝试编写它。但你应该能够轻松地做到这一点。:result_path\:\:float'^[+-]?\d*(\.\d*)?(e[+-]?\d+)?$'

0赞 Zev Isert 8/14/2022

@Belayer 感谢您对此进行调查，但问题不在于验证浮点数。它是关于创建一个具体化视图，其中包含在“编译时”不是静态的查询。我需要接受一些用户输入，然后才能正确创建具体化视图。我已经编辑了代码以删除一些不相关的部分。从本质上讲，具体化视图似乎不能使用准备好的语句 - 那么我该如何解决这个问题呢？

答： 暂无答案

上一个：有关改进此 postgres 函数的任何提示，该函数创建别名，然后将其作为 json 返回。将列标题翻译成其他语言

下一个：查找此尝试的 SQL 注入查询的解释

使用 SQLAlchemy 通过用户输入安全地创建实例化视图

Safely creating materialized views with user input using SQLAlchemy

评论