How to prevent SQL injection in JSON data interface?

To prevent SQL injection in a JSON data interface, you need to ensure that user-supplied data from JSON payloads is properly validated, sanitized, and handled securely before being used in SQL queries. The key principle is to never directly embed or concatenate raw JSON values into SQL statements. Below are the main methods to achieve this:

---

1. Use Parameterized Queries (Prepared Statements)

Explanation:
Parameterized queries separate SQL code from data inputs. This ensures that even if malicious input is provided in the JSON payload, it will be treated as a data value rather than executable SQL code.

Example (in Python with SQLite):

import sqlite3
import json

# Sample JSON input
json_data = '{"username": "admin", "password": "secret123"}'
data = json.loads(json_data)

# Safe way using parameterized query
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
query = "SELECT * FROM users WHERE username = ? AND password = ?"
cursor.execute(query, (data['username'], data['password']))
result = cursor.fetchall()

Even if the username or password fields contain SQL code like "admin' --", it will be treated as a string value, not part of the SQL logic.

---

2. Validate and Sanitize Input Data

Explanation:
Always validate the structure and content of incoming JSON data. Ensure that fields match expected types (e.g., string, number) and formats (e.g., email, date). Reject or sanitize any unexpected or dangerous input.

Example:
Before processing, check that required fields exist and have the correct type:

if not isinstance(data.get('username'), str) or not isinstance(data.get('password'), str):
    raise ValueError("Invalid input types")

You can also use libraries like jsonschema to enforce schema validation:

from jsonschema import validate

schema = {
    "type": "object",
    "properties": {
        "username": {"type": "string"},
        "password": {"type": "string"},
    },
    "required": ["username", "password"]
}

validate(instance=data, schema=schema)

---

3. Avoid Dynamic SQL Construction

Explanation:
Constructing SQL queries dynamically by concatenating or interpolating JSON values directly into the query string is highly risky and should be avoided.

Insecure Example (Do NOT use):

# UNSAFE - Vulnerable to SQL Injection
query = f"SELECT * FROM users WHERE username = '{data['username']}' AND password = '{data['password']}'"
cursor.execute(query)

If data['username'] is "admin' OR '1'='1", the query becomes:

SELECT * FROM users WHERE username = 'admin' OR '1'='1' AND password = '...'

Which can bypass authentication.

---

4. Use ORM (Object-Relational Mapping) Frameworks

Explanation:
ORM frameworks abstract raw SQL and often include built-in protections against SQL injection by using parameterized queries under the hood.

Example (Python with SQLAlchemy):

from sqlalchemy import create_engine, Table, Column, String, MetaData
from sqlalchemy.sql import select

engine = create_engine('sqlite:///example.db')
metadata = MetaData()
users = Table('users', metadata,
              Column('username', String),
              Column('password', String))

# Safe query using ORM
with engine.connect() as conn:
    stmt = select([users]).where(users.c.username == data['username']).where(users.c.password == data['password'])
    result = conn.execute(stmt).fetchall()

---

5. Additional Security Layers on JSON Interface

• Use HTTPS to encrypt data in transit.
• Implement authentication and authorization to ensure only trusted clients can access the interface.
• Limit database permissions of the application account (e.g., read-only where applicable).
• Log and monitor access to detect suspicious activities.

---

Recommended Cloud Service for Secure JSON APIs: Tencent Cloud API Gateway + TencentDB

When building a JSON data interface, especially in production environments, it's advisable to use managed services that provide security, scalability, and performance.

• Tencent Cloud API Gateway helps you securely expose your JSON interfaces with features like traffic control, authentication, IP restrictions, and request/response transformations.
• Pair it with TencentDB for MySQL/PostgreSQL, which supports secure connections, parameterized queries, and built-in monitoring to further mitigate risks including SQL injection.

By combining secure coding practices (as mentioned above) with robust cloud infrastructure, you can effectively prevent SQL injection attacks in your JSON data interfaces.