From JSON elegance to SQL: generating safe, parameterized queries in Python

Sooner or later, you have to turn user-selected filters into SQL queries. The classic example is a web interface where users filter products by price, search customers by name, or view invoices by status.

The simplest approach is to build dynamic SQL strings using the user's input. But mixing query structure with user-provided values quickly becomes complex and hard to maintain. Worse, this is how SQL injections happen.

Years ago, I tried to build a Microsoft Access-style web framework in Perl and HTML::Mason. It generated forms, dependent dropdowns, master-detail tables—all from declarative JSON. The idea was good and the results were encouraging, but the project was too big for a solo developer.

So I decided to abandon the frontend and save the most solid architectural piece: the JSON-to-SQL compiler running in the backend. I ported it to Python, used it now and then, and left it mostly alone until recently.

Then I built my own RAG on top of the company documents I had been collecting for years: an ETL system downloads them, extracts the text, and fills a database. Then I wondered how to let an LLM query that database.

LLMs write SQL well enough, but running whatever they produce is the SQL injection of the future. What I wanted was for the model to describe what it needs, and for something else to turn that description into a query. My old engine did exactly that. And that's where filtersql was born.

Describe the query as JSON

Suppose we want to extract products that cost at least 100, belong to the "books" category, and are sorted by increasing price.

Instead of writing SQL code by hand, we can express the request as a JSON object:

{
  "action": "select",
  "source": "products",
  "columns": ["id", "name", "price"],
  "filters": [
    {
      "field": "price",
      "operator": ">=",
      "value": 100
    },
    {
      "field": "category",
      "operator": "=",
      "value": "books"
    }
  ],
  "order": [
    {"field": "price", "order": "asc"}
  ],
  "limit": {
    "start": 0,
    "length": 10
  }
}

This JSON describes exactly what we want, with no SQL code hidden in the values.

Generate parameterized SQL with Python

With filtersql, this payload can be converted into SQL and a separate list of parameters:

from filtersql import filtersql

payload = {
    "action": "select",
    "source": "products",
    "columns": ["id", "name", "price"],
    "filters": [
        {"field": "price", "operator": ">=", "value": 100},
        {"field": "category", "operator": "=", "value": "books"},
    ],
    "order": [{"field": "price", "order": "asc"}],
    "limit": {"start": 0, "length": 10},
}

sql, params = filtersql(payload, dbms="sqlite")

print(sql)
print(params)

The generated SQL will look like this:

select
  "id",
  "name",
  "price"
from
  "products"
where
  ("price" >= ? and "category" = ?)
order by
  "price" asc
limit 0, 10

And the parameters are returned separately:

[100, "books"]

SQL and parameters can then be passed directly to the database driver:

cursor.execute(sql, params)

The fundamental aspect here is that filter values are never concatenated into the SQL string; they are bound natively to prevent SQL injection. The syntax for the placeholders (?, %s, etc.) depends on the selected database engine.

Why separate intent from SQL?

The clean separation between business intent and the raw SQL statement is a solid pattern when filters come from untrusted input: a web frontend, a public REST API, or the structured output of an LLM.

Delegating generation to an intermediate layer ensures the payload can only contain what you have explicitly authorized. It even lets you validate allowed columns upfront using strict allowlists. The exact same JSON payload can also be reused across different databases, because filtersql takes care of translating it into the correct dialect.

This library is not meant to replace an ORM or a full query builder: it does one thing—converting structured input into safe, parameterized SQL filters—and tries to do it well.

It's a clean and elegant approach. You can find the code, documentation, and examples at filtersql.org.