{"id":5266,"library":"jinjasql","title":"JinjaSQL","description":"JinjaSQL is a Python library (version 0.1.8) that enables the generation of SQL queries and their corresponding bind parameters using Jinja2 templates. It leverages Jinja2's powerful templating features (conditionals, loops, macros) while automatically binding parameters to mitigate common SQL injection vulnerabilities for templated values. It prepares the query and parameters, leaving actual execution to the database driver. While its latest release was in May 2020, this release included a critical bug fix, suggesting it is in a maintenance state rather than active development.","status":"maintenance","version":"0.1.8","language":"en","source_language":"en","source_url":"https://github.com/hashedin/jinjasql","tags":["sql","jinja2","templating","database","orm-alternative"],"install":[{"cmd":"pip install jinjasql","lang":"bash","label":"Install JinjaSQL"}],"dependencies":[{"reason":"Core templating engine; versions >=3.1.0 are known to cause compatibility issues with JinjaSQL 0.1.8, requiring a downgrade of Jinja2.","package":"Jinja2","optional":false}],"imports":[{"symbol":"JinjaSql","correct":"from jinjasql import JinjaSql"}],"quickstart":{"code":"from jinjasql import JinjaSql\n\nj = JinjaSql()\n\ntemplate = \"\"\"\nSELECT username, sum(spend)\nFROM transactions\nWHERE start_date > {{ request.start_date }}\n  AND end_date < {{ request.end_date }}\n{% if request.organization %}\n  AND organization = {{ request.organization }}\n{% endif %}\n\"\"\"\n\ndata = {\n    \"request\": {\n        \"start_date\": \"2026-01-01\",\n        \"end_date\": \"2026-03-31\",\n        \"organization\": \"ExampleOrg\"\n    }\n}\n\nquery, bind_params = j.prepare_query(template, data)\n\nprint(\"Generated Query:\", query)\nprint(\"Bind Parameters:\", bind_params)\n\n# Example with missing organization\ndata_no_org = {\n    \"request\": {\n        \"start_date\": \"2026-01-01\",\n        \"end_date\": \"2026-03-31\"\n    }\n}\nquery_no_org, bind_params_no_org = j.prepare_query(template, data_no_org)\n\nprint(\"\\nGenerated Query (no organization):\", query_no_org)\nprint(\"Bind Parameters (no organization):\", bind_params_no_org)","lang":"python","description":"This quickstart demonstrates how to initialize JinjaSQL, define a Jinja2 template for an SQL query, and prepare the query along with its bind parameters using a context dictionary. It shows how conditional logic in the template affects the final query and parameters. The prepared query and parameters can then be passed to any database driver for execution."},"warnings":[{"fix":"Upgrade to JinjaSQL version 0.1.8 or higher.","message":"A critical bug in versions prior to 0.1.8 could lead to SQL injection if SQL templates used string concatenation or other Python operators directly. Users are strongly advised to upgrade to 0.1.8 or later.","severity":"breaking","affected_versions":"<0.1.8"},{"fix":"Downgrade Jinja2 to a version below 3.1.0 (e.g., `pip install 'Jinja2<3.1.0'`). A fix for JinjaSQL itself is not yet available in the latest release.","message":"JinjaSQL versions are incompatible with Jinja2 versions 3.1.0 and higher due to internal changes in Jinja2's `Markup` and `escape` classes. Importing JinjaSQL with Jinja2 >= 3.1.0 will likely result in an `ImportError`.","severity":"breaking","affected_versions":"All versions <=0.1.8 when used with Jinja2 >=3.1.0"},{"fix":"Use `{{ my_list_variable | inclause }}` in your SQL template instead of `{{ my_list_variable }}`.","message":"When binding a list or tuple to create an SQL `IN` clause (e.g., `WHERE id IN (...)`), you must explicitly apply the `|inclause` filter to the variable in the template. Failure to do so will result in a `MissingInClauseException`.","severity":"gotcha","affected_versions":"All versions"},{"fix":"For dynamic SQL identifiers, use `{{ column_name | sqlsafe }}`. Always sanitize inputs passed to `|sqlsafe` to prevent injection.","message":"To insert dynamic table names, column names, or other SQL identifiers (which cannot be bound as parameters), the `|sqlsafe` filter must be used. However, using `|sqlsafe` bypasses automatic parameter binding, making the developer responsible for preventing SQL injection in such cases.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Check the `param_style` or the type of the returned `bind_params` to process correctly, or initialize `JinjaSql(param_style='...')` explicitly to ensure a consistent return type.","message":"The return type for bind parameters from `j.prepare_query()` depends on the `param_style` used. For `named` or `pyformat` styles, it returns a dictionary. For `format`, `qmark`, or `numeric` styles, it returns a list. Ensure your code handles both possibilities or explicitly sets a `param_style`.","severity":"gotcha","affected_versions":">=0.1.5"},{"fix":"Remove redundant `|sqlsafe` filters from macro outputs if your JinjaSQL version is 0.1.3 or higher.","message":"As of v0.1.3, JinjaSQL now utilizes Jinja2's autoescape feature, making the output of macros automatically SQL safe. This means manually applying the `|sqlsafe` filter to macro outputs is no longer necessary and is considered deprecated for this specific use case.","severity":"deprecated","affected_versions":"<0.1.3"}],"env_vars":null,"last_verified":"2026-04-13T00:00:00.000Z","next_check":"2026-07-12T00:00:00.000Z"}