basetenlabs/jinjasql

basetenlabs/jinjasql

Description: Template Language for SQL with Automatic Bind Parameter Extraction

Language: Python

License: MIT

Stars: 0

Forks: 0

Open issues: 0

Created: 2022-07-19T16:09:46Z

Pushed: 2022-07-19T19:57:10Z

Default branch: master

Fork: yes

Parent repository: sripathikrishnan/jinjasql

Archived: no

README:

Generate SQL Queries using a Jinja Template, without worrying about SQL Injection #


JinjaSQL is a template language for SQL statements and scripts. Since it's based in Jinja2, you have all the power it offers - conditional statements, macros, looping constructs, blocks, inheritance, and many more.

JinjaSQL automatically binds parameters that are inserted into the template. After JinjaSQL evaluates the template, you get:

1. A Query with %s placeholders for the parameters 2. A List of values corresponding to the placeholders that need to be bound to the query

JinjaSQL doesn't actually execute the query - it only prepares the query and the bind parameters. You can execute the query using any database engine / driver you are working with.

For example, if you have a template like this -

select username, sum(spend)
from transactions
where start_date > {{request.start_date}}
and end_date %s
and end_date = version 2.5

To install from PyPI (recommended) :

pip install jinjasql

To install from source :

git clone https://github.com/sripathikrishnan/jinjasql
cd jinjasql
sudo python setup.py install

## How does JinjaSQL work? ##

### The bind filter ###

At it's core, JinjaSQL provides a filter called `bind`. This filter gobbles up whatever value is provided, and always emits the placeholder string %s. The actual value is then stored in a thread local list of bind parameters.

jinja.prepare_query("select * from user where id = {{userid | bind}}", {userid: 143})

When this code is evaluated, the output query is `select * from user where id = %s`.

### Pre-processing the Query Template ###

Manually applying the `bind` filter to every parameter is error-prone. Sooner than later, a developer will miss the filter, and it will lead to SQL Injection.

JinjaSQL automatically applies the bind filter to ALL variables. The query template is transformed before it is evaluated.

select * from user where id = {{userid}}

becomes

select * from user where id = {{userid | bind}}

Jinja lets extensions to [rewrite the token stream](http://jinja.pocoo.org/docs/dev/extensions/#jinja2.ext.Extension.filter_stream). JinjaSQL looks for `variable_begin` and `variable_end` tokens in the stream, and rewrites the stream to include the `bind` filter as the last filter.

### Autoescape and JinjaSQL ###

Jinja has an autoescape feature. If turned on, it automatically HTML escapes variables. It does this by wrapping strings using the `Markup` class.

JinjaSQL builds on this functionality. JinjaSQL requires autoescape to be turned on. As a result, strings that are injected are wrapped using the Markup class. JinjaSQL uses this wrapper class as well to prevent double-binding of parameters.

## License

jinjasql is licensed under the MIT License. See [LICENSE](https://github.com/sripathikrishnan/jinjasql/blob/master/LICENSE)

## Developer Notes

JinjaSQL runs tests against a variety of databases and database drivers. It uses the testcontainers project to launch databases in docker containers.

To setup your development environment and run the tests on an ubuntu machine:

sudo apt-get install gcc g++ python3-dev unixodbc unixodbc-dev pip install -r requirements.txt python run_tests