tonybaloney/pycharm-security

Add checks for SQL Alchemy

tonybaloney opened this issue · 5 comments

tonybaloney commented

Feature Request

From what I could find from some quick googling (but need to actually test this)

filter   - uses _literal_as_text (NOT SAFE)
having   - uses _literal_as_text (NOT SAFE)

distinct - uses _literal_as_label_reference (NOT SAFE)
group_by - uses _literal_as_label_reference (NOT SAFE)
order_by - uses _literal_as_label_reference (NOT SAFE)
tonybaloney commented

Also found

  • sqlalchemy.sql.text
  • <connection>.execute()

Both execute raw SQL directly. Would be caught by the existing SQL injection checks

tonybaloney commented

Tried this and SQL100 catches both anyway

with engine.connect() as conn:
    conn.execute(addresses.insert(), [
        {'user_id': 1, 'email_address' : 'jack@yahoo.com'},
        {'user_id': 1, 'email_address' : 'jack@msn.com'},
        {'user_id': 2, 'email_address' : 'www@www.org'},
        {'user_id': 2, 'email_address' : 'wendy@aol.com'},
    ])
    data = ( {'user_id': 1, 'email_address' : 'jack@yahoo.com\''},)
    statement = text("""INSERT INTO addresses(user_id, email_address) VALUES({}, :email_address)""".format(x))
    for line in data:
        conn.execute(statement, **line)
    conn.execute("SELECT email_address FROM addresses WHERE email_address = \'{}\'".format(foo))
tonybaloney commented

I think it would be possible to use text() as a text-fragment with the filter() function?

session.Query(FooBar).filter(text(sql_injection))