search

mcfunley / pugsql

A HugSQL-inspired database library for Python
https://pugsql.org
Apache License 2.0
673 stars 22 forks source link

adding ability to interpolate identifiers #45

Closed caryfitzhugh closed 3 years ago

caryfitzhugh commented 3 years ago

Our system needs to interpolate identifiers, such as a schema name.

This change grabs the SQL string before it goes to SQLAlchemy and interpolates variables which are wrapped in {{ }}. It also rejects any interpolation strings which are not valid SQL identifiers 

re.fullmatch(r'[a-zA-Z][0-9a-zA-Z_@$]+', pval)
mcfunley commented 3 years ago

Hey thanks. I've been avoiding parsing the sql in the library, and to date have muddled through relying on SQLAlchemy's features. It will probably be necessary to eventually parse the SQL.

But, when we do this, I think it'll need to be done in a context-aware way. Meaning, we'd need to use a library that understands SQL syntax and can leverage this to achieve parameterization safely. Regex replaces on the sql string very likely have some serious security implications, or at least are very hard to reason about.