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?

the concept of your replacement is to prevent sql injection, but it is only for the values, as sql has to know all tables and columnbefore hand, so there is never a replacement for table or column names. so create a white list and compare it to the table name before you run the code — nbk, Aug 01 '22 at 19:26

score 0 · Answer 1 · answered Aug 04 '22 at 14:13

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.

score 0 · Answer 2 · answered Oct 05 '22 at 17:33

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()

Peewee: how to pass raw SQL query parameter without apostrophes

2 Answers2