Peewee: how to pass raw SQL query parameter without apostrophes

Question

Using Python & Peewee, I want to create a function that takes a SQL view name as an argument and returns its results. To avoid any unwanted SQL injections, I want to pass the view name as the SQL query parameter:

def run_view_query(view_name: str):
    query = BaseModel.raw("SELECT * FROM %s", view_name)
    return query

The problem is that Peewee automatically adds apostrophes around the keyword, so I'm getting the following error:

peewee.ProgrammingError: syntax error at or near "'vw_all_users'"
LINE 1: SELECT * FROM 'vw_all_users'

I know I can do it using python f-string like this:

query = BaseModel.raw(f"SELECT * FROM {view_name}")

but then I'd have to do some regex validation for the possible threat of SQL injections. Is there any better solution for that?

Answer 1

Why are you parameterizing your view name? Presumably you know ahead of time what view to select from -- although if this is something that is vulnerable to untrusted input, then you will need to sanitize it yourself.

Peewee forwards all user-provided parameters to the driver, which is responsible for escaping them or using safe APIs to avoid injection. Table names / view names cannot be parameterized in this way.

Answer 2

PeeWee uses classes to represent tables & views. So you can use a if/elif/else on the Python side:

def run_view_query(view_name: str):
  if view_name == "Foo":
    return Foo.select()
  elif view_name == "Bar":
    return Bar.select()
  else:
    return SomeDefault.select()