Security Measures in the SqlQueryTool Class: How Dify Protects Database Queries

The SqlQueryTool class implements multiple layered security safeguards including mandatory input validation, SELECT-only SQL restriction, URL-encoded connection strings, and exception encapsulation to prevent injection attacks and data leakage.

The SqlQueryTool class in the junjiem/dify-plugin-tools-dbquery repository provides a secure interface for executing database queries within the Dify AI platform. When handling sensitive database connections and arbitrary SQL input, robust SqlQueryTool security measures are essential to prevent injection attacks, credential leakage, and unauthorized data modification. This analysis examines the specific security controls implemented in db_query/tools/sql_query.py and db_query/tools/db_util.py to ensure safe query execution.

Mandatory Input Validation and Parameter Sanitization

Before establishing any database connection, the SqlQueryTool._invoke() method enforces strict input validation on all connection parameters. In db_query/tools/sql_query.py (lines 19‑35 and 37‑40), the code explicitly checks for mandatory fields—including database type, host, username, password, and query—using tool_parameters.get().

If any required parameter is missing or empty, the tool raises a clear ValueError immediately, halting execution before reaching the database layer. This validation ensures that partially configured connections cannot proceed, eliminating a common source of connection errors and potential security misconfigurations.

SELECT-Only Query Restriction Using SQL Parsing

To prevent destructive operations, the tool implements SQL statement restriction that strictly limits queries to read-only operations. In sql_query.py (lines 40‑46), the code utilizes the sqlparse library to parse the incoming query string and verify two critical conditions:

• The input contains exactly one SQL statement (len(statements) == 1)
• The statement type is explicitly SELECT (statement.get_type() == 'SELECT')

If either check fails, the tool raises a ValueError with the message "Only a single query SQL can be filled" or "Only SELECT statements are allowed". This parsing layer acts as a critical barrier against SQL injection attempts that might try to append DROP, DELETE, or UPDATE commands to a legitimate query.

Safe Database Connection Construction

The DbUtil class in db_query/tools/db_util.py handles connection construction with rigorous escaping and encoding measures.

URL Encoding of Credentials: In DbUtil.get_url() (lines 54‑66), every connection component—including username, password, host, database name, and extra properties—is processed through urllib.parse.quote_plus(). This encoding prevents connection string injection attacks where malicious input in a password or database name might alter connection behavior.

Wildcard Escaping: Before query execution, the run_query() method (line 77) escapes percent signs (% → %%) in the raw SQL string. This prevents unintended pattern matching behavior in database drivers when the query is passed to pandas/SQLAlchemy.

Connection Pooling Limits: The SQLAlchemy engine initialization in DbUtil.__init__() (line 30) sets pool_size=100 with connection recycling, mitigating resource exhaustion attacks by limiting concurrent connections.

Driver-Specific Security: For Oracle databases, the code (lines 27‑30) automatically enables Thick mode (oracledb.init_oracle_client()) only when the oracle11g driver is selected, avoiding accidental use of insecure default configurations.

Secure Output Handling and Error Encapsulation

The tool implements strict controls over output formatting and error messaging to prevent information leakage.

Exception Encapsulation: In sql_query.py (lines 55‑57), any failure during query execution is caught, logged with a full stack trace for debugging, then re-raised as a generic RuntimeError. This prevents sensitive database error details—such as schema names or internal paths—from reaching end users.

Output Format Restriction: The tool explicitly supports only two output formats: markdown (tabular) and json (lines 47‑63). This restriction prevents the accidental emission of raw HTML or executable scripts that could enable cross-site scripting (XSS) attacks in downstream applications.

Data Type Normalization: The run_query() method (lines 84‑98) sanitizes returned data by normalizing dates, UUIDs, and floats, while replacing None values and empty strings with blank placeholders. This normalization prevents the exposure of raw Python objects or database-specific artifacts that might contain sensitive metadata.

Practical Implementation Examples

Executing a Valid SELECT Query

The following example demonstrates a properly configured read-only query:

from db_query.tools.sql_query import SqlQueryTool

tool = SqlQueryTool()
params = {
    "db_type": "postgresql",
    "db_host": "db.example.com",
    "db_port": 5432,
    "db_username": "readonly_user",
    "db_password": "s3cureP@ss",
    "db_name": "sales",
    "query_sql": "SELECT id, amount, created_at FROM orders LIMIT 10",
    "output_format": "markdown"
}

for msg in tool._invoke(params):
    print(msg.content)  # => GitHub-flavored markdown table

This invocation triggers the complete security chain: parameter validation, SQL parsing confirmation, URL-encoded connection construction, and sanitized markdown output.

Blocking Destructive SQL Statements

Attempting to execute a disallowed statement results in immediate rejection:

params["query_sql"] = "DROP TABLE users; SELECT * FROM orders"

# Invoking the tool now raises:

# ValueError: Only a single query SQL can be filled

The sqlparse validation detects multiple statements and halts execution before any database connection occurs.

Summary

The SqlQueryTool class implements a comprehensive defense-in-depth strategy for database query security:

• Mandatory input validation ensures all connection parameters are present before processing
• SELECT-only restriction via sqlparse parsing prevents data-modifying or destructive SQL execution
• URL-encoded connection strings using quote_plus eliminate credential injection vulnerabilities
• Wildcard escaping (%%) prevents unintended pattern matching in database drivers
• Connection pooling limits mitigate resource exhaustion attacks
• Exception encapsulation prevents leakage of sensitive database error details to users
• Output format control restricts results to markdown or JSON, blocking potential XSS vectors

Frequently Asked Questions

How does SqlQueryTool prevent SQL injection attacks?

The tool utilizes the sqlparse library to parse and validate all incoming queries, enforcing that only single SELECT statements are executed. By checking statement.get_type() == 'SELECT' and ensuring len(statements) == 1, the code blocks attempts to append malicious commands like DROP or DELETE to legitimate queries.

Why are database credentials URL-encoded in the connection string?

All credential components—including usernames, passwords, and database names—are processed through urllib.parse.quote_plus() in DbUtil.get_url() (lines 54‑66). This encoding neutralizes special characters that could otherwise manipulate the connection string syntax and redirect connections to unauthorized hosts or expose credentials in logs.

What happens when a query execution fails?

Errors are caught and logged internally with full stack traces for debugging, then re-raised as generic RuntimeError exceptions (lines 55‑57). This exception encapsulation ensures that database-specific error messages—which might reveal schema structures, file paths, or internal network details—never reach the end user.

Does the tool support data modification queries like INSERT or UPDATE?

No. The SqlQueryTool explicitly restricts queries to read-only SELECT operations. Any attempt to execute INSERT, UPDATE, DELETE, or other data manipulation language (DML) statements results in an immediate ValueError before database connection establishment, protecting data integrity by design.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}