A way to accept SQL input with sanitization and parameterization with a json format
Project description
JsonSQL
JsonSQL is a Python class that allows safe execution of SQL queries from untrusted JSON input. It validates the input against allowed queries, tables, columns etc. before constructing a safe SQL string.
Usage
Installing the package
pip install jsonsql
Full SQL String
Import the JsonSQL class:
from jsonsql import JsonSQL
Initialize it by passing allowed queries, items, tables, connections and column types:
allowed_queries = ["SELECT"]
allowed_items = ["*"]
allowed_tables = ["users"]
allowed_connections = ["WHERE"]
allowed_columns = {"id": int, "name": str}
jsonsql = JsonSQL(allowed_queries, allowed_items, allowed_tables, allowed_connections, allowed_columns)
Pass a JSON request to sql_parse() to validate and construct the SQL:
request = {
"query": "SELECT",
"items": ["*"],
"table": "users",
"connection": "WHERE",
"logic": {
"id": {"=":123}
}
}
valid, sql, params = jsonsql.sql_parse(request)
The logic is validated against the allowed columns before constructing the final SQL string.
Search Criteria for partial string
The logic_parse method can also be used independently to validate logic conditions without constructing a full SQL query. This allows reusing predefined or dynamically generated SQL strings while still validating any logic conditions passed from untrusted input.
For example:
sql = "SELECT * FROM users WHERE"
logic = {
"AND": [
{"id": {"=":123}},
{"name": {"=":"John"}}
]
}
valid, message, params = jsonsql.logic_parse(logic)
if valid:
full_sql = f"{sql} ({message})"
# execute full_sql with params
This validates the logic while allowing the SQL query itself to be predefined or constructed separately.
The logic_parse method will return False if the input logic is invalid. Otherwise it returns the parsed logic string and any bound parameters for safe interpolation into a SQL query.
All arguments to JsonSQL like allowed_queries, allowed_columns etc. are optional and can be empty lists or dicts if full validation of the SQL syntax is not needed.
Current operations supported by the logic
{
"AND":[],
"OR":[],
{"variable":{"<, >, =, etc":"value"}},
{"variable":{"BETWEEN":["value1","value2"]}},
{"variable":{"IN":["value1","value2","..."]}}
}
values can also be valid variables such that variable1 = variable2
{"variable":{"=":"othervariable"}}
Project details
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.