ASP.NET C#中限制仅执行SELECT查询的额外安全防护方案问询

Great question! You're already off to a strong start with keyword filtering and a read-only database user—those are critical foundational safeguards. Let's break down more pre-execution validation layers you can implement to harden your UI against malicious queries, accidental performance hits, and unauthorized data access:

1. Parse and Validate via Abstract Syntax Tree (AST) #

Instead of relying solely on string-based keyword checks (which can be bypassed with obfuscation), use a SQL parser to convert the user's input into an Abstract Syntax Tree (AST). This lets you inspect the actual structure of the query, not just its text:

• Ensure the top-level statement is strictly a SELECT (no hidden nested statements like INSERT or DROP that might slip past keyword filters).
• Restrict allowed operations: block dangerous constructs like overly complex correlated subqueries that could cause excessive load, or limit the depth of nested subqueries.
• Validate that joins only use permitted types (e.g., INNER JOIN, LEFT JOIN—avoid less common, high-risk join types if they aren't needed for your use case).
  Tools like ANTLR or language-specific SQL parsers (e.g., sqlparse for Python) can help generate and traverse the AST.

2. Implement a Strict Allowlist (Whitelist) #

Move beyond blacklist-based keyword filtering to an allowlist approach, which is far more secure:

• Define exactly which tables, columns, and SQL functions users are permitted to access. For example, if your users only need data from customers and orders, reject any query that references employee_salaries or other sensitive tables.
• Allow only specific join patterns or aggregate functions (e.g., ban COUNT(*) on large tables if it's not required, or restrict to SUM()/AVG() on approved columns).
• This prevents users from probing your database schema or accessing data they shouldn't see, even if they find a way around keyword filters.

3. Enforce Query Complexity and Resource Limits #

Malicious or poorly written queries can grind your database to a halt—add checks to prevent this before execution:

• Use your database's EXPLAIN command to analyze the query's estimated execution plan. Reject queries that would trigger full-table scans on large tables, or have an excessively high row count estimate.
• Set hard limits:
  • Maximum number of tables allowed in joins (e.g., no more than 3 tables per query).
  • Maximum expected result row count (reject queries that would return millions of rows).
  • Timeout thresholds: if the estimated execution time exceeds a set limit (e.g., 5 seconds), block the query.

4. Block Multi-Statement Queries #

Even with a read-only user, multi-statement queries can be used to probe your database or cause unexpected behavior. Add validation to ensure the user's input contains only a single SELECT statement:

• Check for statement separators like semicolons (;) that would allow multiple queries to run in one execution.
• Use your AST parser to confirm there's only one top-level statement in the input.

5. Schema-Based Validation #

Cross-reference the user's query against your database's actual schema to catch unauthorized or invalid references:

• Verify that all tables and columns referenced in the query exist in your allowed schema.
• Confirm that your read-only database user has explicit access to every object the query references (this prevents users from guessing the names of sensitive tables they don't have permission to access).
  This step also reduces unnecessary database round-trips by rejecting invalid queries upfront.

6. Sanitize Input for Obfuscation Attempts #

Keyword filters can be bypassed with obfuscation (e.g., DrOp instead of DROP, or comments splitting keywords). Add sanitization steps to handle this:

• Normalize the input (convert to lowercase) before running keyword checks or AST parsing.
• Strip or block SQL comments (--, /* */) to prevent users from hiding malicious keywords or modifying the query's intended structure.
• Reject inputs with unusual characters or formatting that could indicate obfuscation attempts.

7. Rate Limiting and Session-Based Restrictions #

Add a layer of protection against brute-force or denial-of-service attempts:

• Limit the number of queries a single user (or IP address) can submit within a time window (e.g., 5 queries per minute).
• Track user sessions and block repeat offenders who submit malicious or invalid queries repeatedly.

Remember, security is layered—combining these checks with your existing safeguards (keyword filtering + read-only user) creates a robust defense against both intentional attacks and accidental misuse.

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