Django中PostgreSQL JSONB自定义查询的安全性问询

Hi there, let's break down your question clearly: your current implementation is not safe and carries several security risks that you need to address immediately. Here's why, along with actionable fixes:

Key Security Risks #

1. Unrestricted Query Construction Leads to Sensitive Data Exposure #

Your custom_query function allows arbitrary values for the operation parameter, which gets directly appended to meta__ to form a lookup key. This means a malicious user could:

• Access nested fields in the meta JSON that you never intended to expose (e.g., if meta has {"personal": {"ssn": "123-45-6789"}}, passing operation="personal__ssn" would let them query for specific SSN values).
• Use Django ORM's built-in lookup operations like __regex or __contains to scrape partial sensitive data from the JSON field.

2. Potential for Query Logic Abuse #

Even if you trust your users, allowing free-form operation values can break your application's intended behavior:

• A user could pass operation="__isnull" to find all items where the entire meta field is null, or operation="a__gt" to compare numeric values in the JSON—operations you might not want to expose.
• If the operation includes invalid characters or malformed lookup syntax, it could trigger unhandled exceptions that crash your app.

3. Theoretical SQL Injection Risk (Edge Cases) #

While Django's ORM protects against most SQL injection by parameterizing values, the lookup key itself is constructed via string concatenation. In rare cases, a crafted operation value could exploit gaps in Django's lookup parsing to alter the generated SQL query—for example, bypassing the user=user filter by injecting conditions that override the initial restriction.

How to Fix It #

You need to restrict and validate all user-provided input to only allow safe, intended operations. Here are two robust approaches:

Option 1: Restrict to Predefined JSON Lookups #

If you want to let users query specific JSON keys with limited operations, define explicit allowed keys and operations:

import string
from django.db import models
from django.core.exceptions import ValidationError

ALLOWED_JSON_OPERATIONS = {"exact", "contains", "gt", "lt", "in", "isnull"}
# Allow only alphanumeric and underscore for JSON keys (no double underscores!)
ALLOWED_KEY_CHARS = set(string.ascii_letters + string.digits + "_")

class Item(models.Model):
    user = models.ForeignKey("user", null=False, on_delete=models.CASCADE)  # Added on_delete for best practice
    meta = models.JSONField()  # For PostgreSQL, this maps to JSONB automatically

def custom_query(json_key, operation, value):
    # Validate JSON key
    if "__" in json_key or not all(c in ALLOWED_KEY_CHARS for c in json_key):
        raise ValidationError("Invalid JSON key format")
    # Validate operation
    if operation not in ALLOWED_JSON_OPERATIONS:
        raise ValidationError("Unsupported query operation")
    
    # Build safe lookup key
    lookup_key = f"meta__{json_key}__{operation}"
    return Item.objects.filter(user=user).filter(**{lookup_key: value})

Option 2: Explicitly Define Query Types #

If you only want to support specific high-level operations (like checking if the JSON contains an object), map user input to fixed lookups:

from django.db import models
from django.core.exceptions import ValidationError

class Item(models.Model):
    user = models.ForeignKey("user", null=False, on_delete=models.CASCADE)
    meta = models.JSONField()

def custom_query(query_type, value):
    allowed_types = {"contains_object", "has_key", "has_value"}
    if query_type not in allowed_types:
        raise ValidationError("Invalid query type")
    
    qs = Item.objects.filter(user=user)
    if query_type == "contains_object":
        return qs.filter(meta__contains=value)
    elif query_type == "has_key":
        return qs.filter(meta__has_key=value)
    elif query_type == "has_value":
        # Use meta__has_any_keys if checking for multiple values, adjust as needed
        return qs.filter(meta__has_any_keys=[value])

Final Notes #

• Always validate and sanitize user input before using it to construct ORM lookups.
• Keep your allowed operations and keys as restrictive as possible—only expose what your users actually need.
• Test edge cases: try passing malicious operation values (like those with __ or SQL fragments) to ensure your validation catches them.

内容的提问来源于stack exchange，提问作者flux