1

I'm trying to create materialized views in a Postgres (with TimescaleDB) where the user can control one of the selected fields. I understand that materialized views cannot be parametrized as a prepared statement because these parameters would need to be saved for when the view is updated later on (thanks to this answer).

I'm looking for a way to convince myself that my code is safe when trying to do something like this:

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 
)

Of course this example doesn't work, my driver (asyncpg) correctly reports FeatureNotSupportedError: materialized views may not be defined using bound parameters.

I don't actually want a bound parameter, I want to define a materialized view with the values as provided. If if weren't for worries about injection, we'd just plop the result path in the query string itself.

Am I stuck manually validating the user controlled path variable with a regex of some kind? I obviously need to be worried about single quotes, parens, braces, comment characters, escapes, etc.

What's the best way forward here?

Zev Isert
  • 915
  • 11
  • 20
  • I found some related answers: [this exposed why parameters don't work in materialized views](https://stackoverflow.com/a/53998373/4785629) and [this showed how I can escape a simple string](https://stackoverflow.com/a/64632908/4785629), but I need to validate that `path` variable doesn't inject something – Zev Isert Aug 12 '22 at 22:22
  • If I understand your code correctly, `:result_path\:\:float`, that casts the string in :result_path to a float. If that is the case then you only need to validate that it is a valid numeric value before attempting to create MV. The following seems to validate that: `'^[+-]?\d*(\.\d*)?(e[+-]?\d+)?$'` (at least in Postgres regex engine). See [Demo](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=cfe1abb2dd003d31dd8d59df70a833bc). Sorry I do not know SQLAlchemy well enough to attempt writing it. But you should able to do so easily. – Belayer Aug 13 '22 at 20:11
  • @Belayer Thanks for looking into this, but the question is not about validating a float. It's about creating a materialized view with a query that's not static at "compile time". I need to accept some user input before I can correctly create the materialized view. I've edited the code to remove some of the unrelated parts. Essentially, it's that materialized views can't seem to use prepared statements - so how can I work around that? – Zev Isert Aug 13 '22 at 22:45

0 Answers0