text2sql-assistant / src /t2s /safety /validator.py
Ashoksainibadu's picture
Upload folder using huggingface_hub
388137e verified
Raw History Blame Contribute Delete
10.2 kB
"""SQL AST Validator using sqlglot for safe text-to-SQL execution."""
import re
from dataclasses import dataclass, field
from typing import List, Optional, Set
import sqlglot
from sqlglot import exp
from sqlglot.tokens import TokenType
from src.t2s.safety.policy import SafetyPolicy
@dataclass
class ValidationVerdict:
allowed: bool
reasons: List[str] = field(default_factory=list)
rewritten_sql: str = ""
def _compute_ast_depth(node: exp.Expression) -> int:
"""Recursively calculate the maximum depth of an AST expression."""
max_child_depth = 0
for child in node.iter_expressions():
d = _compute_ast_depth(child)
if d > max_child_depth:
max_child_depth = d
return 1 + max_child_depth
class SQLValidator:
def __init__(self, policy: Optional[SafetyPolicy] = None) -> None:
self.policy = policy or SafetyPolicy.load()
def validate(
self,
sql: str,
allowed_tables: Optional[Set[str]] = None,
allowed_columns: Optional[Set[str]] = None,
) -> ValidationVerdict:
try:
return self._validate_internal(
sql=sql, allowed_tables=allowed_tables, allowed_columns=allowed_columns
)
except Exception as e:
# Fail-safe (deny) on any unexpected internal error or exception
return ValidationVerdict(
allowed=False,
reasons=[f"Validator internal error (fail-safe deny): {e}"],
rewritten_sql="",
)
def _validate_internal(
self,
sql: str,
allowed_tables: Optional[Set[str]] = None,
allowed_columns: Optional[Set[str]] = None,
) -> ValidationVerdict:
reasons: List[str] = []
if not sql or not sql.strip():
return ValidationVerdict(allowed=False, reasons=["Empty SQL query"], rewritten_sql="")
raw_sql = sql.strip()
# 1. Check max query length
if len(raw_sql) > self.policy.max_query_length:
return ValidationVerdict(
allowed=False,
reasons=[f"SQL query exceeds max length of {self.policy.max_query_length}"],
rewritten_sql="",
)
# 2. Input character policy before parsing: reject control characters and non-ASCII whitespace outside literals
non_literal_sql = re.sub(r"'[^']*'", "''", raw_sql)
for ch in non_literal_sql:
code = ord(ch)
if (code < 32 and ch not in ("\t", "\n", "\r")) or code == 127:
reasons.append(f"Forbidden control character detected: 0x{code:02x}")
break
elif code > 127 and (
ch.isspace()
or "\u2000" <= ch <= "\u200f"
or ch in ("\xa0", "\ufeff", "\u2028", "\u2029", "\u3000")
):
reasons.append(
f"Forbidden non-ASCII whitespace or format character detected: U+{code:04X}"
)
break
try:
tokens = sqlglot.tokenize(raw_sql, read="sqlite")
for t in tokens:
if (
t.comments
or t.token_type in (TokenType.COMMENT, TokenType.HINT)
or "COMMENT" in t.token_type.name
):
reasons.append("SQL comments are not allowed")
break
except Exception:
# Fallback if tokenizer fails: check for raw comment symbols
if "--" in raw_sql or "/*" in raw_sql:
reasons.append("SQL comments are not allowed")
# 3. Check for multiple statements
clean_stmt = raw_sql.rstrip(";").strip()
try:
stmts = sqlglot.parse(clean_stmt, read="sqlite")
if len(stmts) > 1:
reasons.append("Multiple SQL statements are strictly forbidden")
except Exception:
pass
# Fallback check for semicolon outside strings
if ";" in clean_stmt:
stripped_strings = re.sub(r"'[^']*'", "''", clean_stmt)
stripped_strings = re.sub(r'"[^"]*"', '""', stripped_strings)
if ";" in stripped_strings:
reasons.append("Multiple SQL statements are strictly forbidden")
# 4. Parse AST with sqlglot
try:
parsed = sqlglot.parse_one(clean_stmt, read="sqlite")
except Exception as e:
reasons.append(f"SQL parsing error: {e}")
return ValidationVerdict(allowed=False, reasons=reasons, rewritten_sql="")
if parsed is None:
reasons.append("Invalid or unparseable SQL AST")
return ValidationVerdict(allowed=False, reasons=reasons, rewritten_sql="")
# 5. Check root node type (must be Select, Union, Intersect, or Except)
if not isinstance(parsed, (exp.Select, exp.Union, exp.Intersect, exp.Except)):
reasons.append(
f"Root query type '{parsed.key}' is not allowed (must be SELECT/UNION/INTERSECT/EXCEPT)"
)
# 6. Check for forbidden AST expression nodes anywhere in tree (pure AST decision)
forbidden_expr_types = (
exp.Drop,
exp.Delete,
exp.Insert,
exp.Update,
exp.Alter,
exp.Create,
exp.Command,
exp.Pragma,
)
for expr in parsed.find_all(forbidden_expr_types):
reasons.append(f"Forbidden statement/keyword detected: {expr.key.upper()}")
# 7. Check max AST depth
ast_depth = _compute_ast_depth(parsed)
if ast_depth > self.policy.max_ast_depth:
reasons.append(
f"Query AST depth exceeds max allowed limit ({ast_depth} > {self.policy.max_ast_depth})"
)
# 8. Walk AST for table references
active_allowlist = allowed_tables or self.policy.table_allowlist
tables_found = set()
for table in parsed.find_all(exp.Table):
t_name = table.name.lower()
tables_found.add(t_name)
if t_name in self.policy.denied_tables:
reasons.append(f"Access to denied system/sensitive table '{t_name}' is blocked")
if active_allowlist and t_name not in {t.lower() for t in active_allowlist}:
reasons.append(f"Table '{t_name}' is not in the allowed database schema")
# 9. Walk AST for column references
for col in parsed.find_all(exp.Column):
c_name = col.name.lower()
if c_name in self.policy.denied_columns:
reasons.append(f"Access to denied sensitive column '{c_name}' is blocked")
for dc in self.policy.denied_columns:
if c_name == dc or c_name.startswith(f"{dc}_") or c_name.endswith(f"_{dc}"):
msg = f"Access to denied sensitive column '{c_name}' is blocked"
if msg not in reasons:
reasons.append(msg)
# 10. Walk AST for functions (enforce function allowlist + denylist)
allowed_funcs = {f.lower() for f in self.policy.allowed_functions}
for func in parsed.find_all(exp.Expression):
if isinstance(func, (exp.Anonymous, exp.Func)) and not isinstance(
func, (exp.And, exp.Or, exp.Not, exp.Binary, exp.Connector, exp.Case, exp.If)
):
if isinstance(func, exp.Anonymous):
f_name = (
func.this.name.lower()
if hasattr(func.this, "name")
else str(func.this).lower()
)
else:
f_name = func.key.lower()
if allowed_funcs and f_name not in allowed_funcs:
reasons.append(f"Function '{f_name}' is not in the allowed functions policy")
if f_name in self.policy.denied_functions:
reasons.append(f"Execution of function '{f_name}' is denied by policy")
for df in self.policy.denied_functions:
if re.search(r"\b" + re.escape(df) + r"\s*\(", clean_stmt, re.IGNORECASE):
msg = f"Execution of function '{df}' is denied by policy"
if msg not in reasons:
reasons.append(msg)
# 11. Check join complexity limits (per branch and total)
total_joins = len(list(parsed.find_all(exp.Join)))
if total_joins > self.policy.max_total_joins:
reasons.append(
f"Query exceeds max total joins ({total_joins} > {self.policy.max_total_joins})"
)
for select_branch in parsed.find_all(exp.Select):
joins = list(select_branch.find_all(exp.Join))
tables = list(select_branch.find_all(exp.Table))
branch_joins = max(len(joins), max(0, len(tables) - 1))
if branch_joins > self.policy.max_joins_per_branch:
reasons.append(
f"Query exceeds max allowed joins ({branch_joins} > {self.policy.max_joins_per_branch})"
)
# 12. Enforce max_row_limit
rewritten_sql = clean_stmt
if isinstance(parsed, exp.Select):
limit_exp = parsed.args.get("limit")
if limit_exp is None:
parsed = parsed.limit(self.policy.max_row_limit)
else:
try:
curr_limit = int(limit_exp.expression.this)
if curr_limit > self.policy.max_row_limit:
parsed = parsed.limit(self.policy.max_row_limit)
except Exception:
parsed = parsed.limit(self.policy.max_row_limit)
rewritten_sql = parsed.sql(dialect="sqlite")
if not rewritten_sql.endswith(";"):
rewritten_sql += ";"
unique_reasons = []
for r in reasons:
if r not in unique_reasons:
unique_reasons.append(r)
allowed = len(unique_reasons) == 0
return ValidationVerdict(
allowed=allowed, reasons=unique_reasons, rewritten_sql=rewritten_sql if allowed else ""
)