Open-source AI Security Operations Center: alert fusion, LLM-agent triage, MITRE ATT&CK investigation, and a replayable decision ledger for every agent step. Self-hostable, runs with no API keys, MIT licensed. Ships an MCP server for Claude, Cursor and Continue.
Python agentic-aiai-securityalert-triageblue-teamcybersecuritydetection-engineeringincident-responselanggraphllm-agentsmcpmitre-attackmodel-context-protocolpurple-teamsecurity-operationsself-hostedsiemsoarsocthreat-detectionthreat-intelligence
#!/usr/bin/env python3
"""Refuse a `:name::type` cast inside a SQLAlchemy ``text()`` statement.
SQLAlchemy finds bind parameters in a ``text()`` string with
``(?<![:\\w\\x5c]):(\\w+)(?!:)``. The trailing ``(?!:)`` is there so the
Postgres ``::`` cast operator is not mistaken for a parameter — but it does
not skip the construct, it *backtracks one character* and declares a
different, shorter name. So ``:payload::jsonb`` declares ``payloa``,
``:urls::text[]`` declares ``url``, and the ``.bindparams(payload=...)`` that
follows raises ``ArgumentError``.
Three properties make this worth a gate rather than a code review note:
* It raises on the ``.bindparams()`` call, which in every instance found sat
*outside* the handler's ``try``. So the route did not degrade to its 503
path — it 500'd, and the statement never reached a connection.
* It is invisible to a reading eye. ``:payload::jsonb`` is what the Postgres
documentation shows, and the truncated name never appears in the source.
* It survived because the tests covering those routes read the handler's
source text instead of calling it. A substring assertion cannot fail on
this, because the substring is present and correct.
Four route handlers were dead this way: ``compliance.collect_evidence``,
``phishing.submit``, ``phishing.retriage`` and ``knowledge_base.ingest``.
``knowledge_base.query_kb`` carried the same spelling, was found, and was
fixed to ``CAST(:kinds AS text[])`` — while the sibling INSERT twenty lines
above it kept the broken form. A gate is what closes that gap.
The fix is always ``CAST(:name AS type)``, which is standard SQL, means the
same thing to Postgres, and leaves the parameter name intact.
Usage:
python3 scripts/check_text_bind_casts.py
python3 scripts/check_text_bind_casts.py --self-test
"""
from __future__ import annotations
import ast
import re
import subprocess
import sys
from pathlib import Path
# `scripts/` is on sys.path when this file is run as a program, but not when a
# test loads it by path with importlib. gate_toolkit sits beside it either way.
sys.path.insert(0, str(Path(__file__).resolve().parent))
from gate_toolkit import SELF_TEST_FLAG, repo_root, self_test_main
# Verbatim from `sqlalchemy.sql.elements.TextClause._bind_params_regex`. If a
# future SQLAlchemy changes it, this gate reports names that release does not,
# which is the safe direction: it can complain about a statement that works,
# never stay quiet about one that raises.
SQLA_BIND_RE = re.compile(r"(?<![:\w\x5c]):(\w+)(?!:)")
# A `:name` whose next two characters are `::`. The lookbehind mirrors
# SQLAlchemy's so a `::` that is not preceded by a parameter (an `a::b` cast of
# a column, say) is not reported.
CAST_ADJACENT_RE = re.compile(r"(?<![:\w\\]):(\w+)::")
# A bind parameter tested directly for NULL: `:name IS NULL`, or the negated
# form. Postgres has nothing to infer the parameter's type from, so asyncpg
# answers `could not determine data type of parameter $N` — and only when the
# value bound is actually `None`, which is why this survives every test that
# supplies a value. A `CAST(:name AS TEXT) IS NULL` is already typed and is
# not matched.
UNTYPED_NULL_TEST_RE = re.compile(r"(?<![:\w\\]):(\w+)\s+IS\s+(?:NOT\s+)?NULL", re.IGNORECASE)
# The historical prototype subtree is reference-only and is `paths-ignore`d by
# CodeQL for the same reason.
SKIP_PREFIXES = ("plans/",)
def _tracked_python_files(root: Path) -> list[Path]:
out = subprocess.run(
["git", "ls-files", "-z", "*.py"],
cwd=root,
capture_output=True,
text=True,
check=True,
).stdout
return [root / rel for rel in out.split("\0") if rel and not rel.startswith(SKIP_PREFIXES)]
def _static_string(node: ast.AST) -> str | None:
"""Return the statement text when it can be read without executing anything.
Handles the three spellings that appear in this tree: a plain literal, an
implicit or explicit concatenation of literals, and an f-string whose
interpolations are irrelevant to where the colons fall. A `text()` built
from a variable is skipped — reporting on it would need dataflow, and the
statements this defect lives in are all inline literals.
"""
if isinstance(node, ast.Constant):
return node.value if isinstance(node.value, str) else None
if isinstance(node, ast.BinOp) and isinstance(node.op, ast.Add):
left, right = _static_string(node.left), _static_string(node.right)
return None if left is None or right is None else left + right
if isinstance(node, ast.JoinedStr):
parts = []
for value in node.values:
if isinstance(value, ast.Constant) and isinstance(value.value, str):
parts.append(value.value)
else:
# An interpolated fragment. Substitute a placeholder that
# carries no colon so it cannot invent or mask a match.
parts.append(" ")
return "".join(parts)
return None
def _is_text_call(node: ast.Call) -> bool:
func = node.func
if isinstance(func, ast.Name):
return func.id == "text"
if isinstance(func, ast.Attribute):
return func.attr == "text"
return False
def violations(source: str, rel: str) -> list[str]:
"""Report every bind parameter inside a `text()` that needs a cast.
Two signatures, one family and one fix. `:name::type` declares a
different, shorter parameter name and raises on `.bindparams()`;
`:name IS NULL` declares the right name and raises on the connection,
but only when the value bound is `None`. Both are written
`CAST(:name AS <type>)`.
"""
try:
tree = ast.parse(source)
except SyntaxError as exc:
return [f"{rel}: could not parse ({exc})"]
found: list[str] = []
for node in ast.walk(tree):
if not isinstance(node, ast.Call) or not _is_text_call(node) or not node.args:
continue
statement = _static_string(node.args[0])
if statement is None:
continue
declared = set(SQLA_BIND_RE.findall(statement))
for match in CAST_ADJACENT_RE.finditer(statement):
intended = match.group(1)
if intended in declared:
# SQLAlchemy resolved this name anyway (it also appears in the
# statement somewhere a cast does not follow it), so the
# bindparams call will not raise. Still worth nothing here.
continue
truncated = next((d for d in declared if intended.startswith(d) and d != intended), "no parameter")
found.append(
f"{rel}:{node.lineno}: `:{intended}::` declares `{truncated}`, so `.bindparams({intended}=...)` raises. "
f"Write `CAST(:{intended} AS <type>)`."
)
for match in UNTYPED_NULL_TEST_RE.finditer(statement):
name = match.group(1)
found.append(
f"{rel}:{node.lineno}: `:{name} IS NULL` gives Postgres no type to infer, so asyncpg raises "
f"`could not determine data type` whenever `{name}` is bound to None — the branch a test that "
f"always supplies a value never reaches. Write `CAST(:{name} AS <type>) IS NULL`."
)
return found
def main() -> int:
root = repo_root()
files = _tracked_python_files(root)
# Every statement this gate is about lives in a service. `scripts/` alone
# is a runnable copy of the gate over none of its subject, and reporting
# OK there is a verdict about a tree that is not this one.
services = [p for p in files if p.relative_to(root).parts[:1] == ("services",)]
if not services:
print(
"check_text_bind_casts: no tracked Python files under services/ — refusing to report a clean tree",
file=sys.stderr,
)
return 1
problems: list[str] = []
for path in files:
try:
source = path.read_text(encoding="utf-8")
except (OSError, UnicodeDecodeError):
continue
if "text(" not in source:
continue
problems.extend(violations(source, str(path.relative_to(root))))
if problems:
print("A bind parameter inside a text() statement needs an explicit cast:\n", file=sys.stderr)
for problem in problems:
print(f" {problem}", file=sys.stderr)
print(f"\n{len(problems)} statement(s) would raise rather than run.", file=sys.stderr)
return 1
print(
f"OK: {len(files)} tracked Python file(s) scanned ({len(services)} under services/), "
"no `:name::type` and no untyped `:name IS NULL` inside a text() statement"
)
return 0
_CLEAN = 'q = text("INSERT INTO t (a, b) VALUES (CAST(:payload AS jsonb), :other)").bindparams(payload=x, other=y)'
_BROKEN = 'q = text("INSERT INTO t (a, b) VALUES (:payload::jsonb, :other)").bindparams(payload=x, other=y)'
_NOT_SQL = 'if ":iam::" in resource_id and ":policy/" in resource_id:\n kind = "policy"'
_CONCAT = 'q = text("SELECT * FROM t" " WHERE k = ANY(:kinds::text[])")'
# The second signature. `_NULL_BROKEN` is the exact statement depth 8.1
# found in `resolve_saml_connection`; `_NULL_CLEAN` is its fix. The
# `NOT NULL` case is here because the same inference failure applies, and
# `_NULL_COLUMN` is the shape that must keep passing — a *column* tested
# for NULL is ordinary SQL and carries no parameter at all.
_NULL_BROKEN = 'q = text("SELECT id FROM c WHERE (:i IS NULL OR issuer = :i)").bindparams(i=issuer)'
_NULL_CLEAN = 'q = text("SELECT id FROM c WHERE (CAST(:i AS TEXT) IS NULL OR issuer = CAST(:i AS TEXT))")'
_NULL_NEGATED = 'q = text("SELECT id FROM c WHERE :since IS NOT NULL")'
_NULL_COLUMN = 'q = text("SELECT id FROM c WHERE deleted_at IS NULL AND issuer = :i")'
def _rule_self_test() -> list[tuple[str, bool]]:
return [
("the CAST() spelling is not reported", not violations(_CLEAN, "f.py")),
("an injected `:payload::jsonb` is reported", any("payloa" in v for v in violations(_BROKEN, "f.py"))),
("an ARN literal outside a text() call is not reported", not violations(_NOT_SQL, "f.py")),
("a `::` cast in a concatenated statement is reported", any(":kinds::" in v for v in violations(_CONCAT, "f.py"))),
("an untyped `:i IS NULL` is reported", any(":i IS NULL" in v for v in violations(_NULL_BROKEN, "f.py"))),
("`CAST(:i AS TEXT) IS NULL` is not reported", not violations(_NULL_CLEAN, "f.py")),
("an untyped `:since IS NOT NULL` is reported", any(":since IS NULL" in v for v in violations(_NULL_NEGATED, "f.py"))),
("a column tested for NULL is not reported", not violations(_NULL_COLUMN, "f.py")),
]
if __name__ == "__main__":
if SELF_TEST_FLAG in sys.argv[1:]:
sys.exit(self_test_main(Path(__file__).name, extra=_rule_self_test()))
sys.exit(main())