Skip to content

Modify a SQL AST and regenerate SQL safely

Use General SQL Parser (GSP) when an application must change SQL without brittle string replacement. GSP parses vendor SQL into a structured abstract syntax tree (AST), lets your code inspect and modify specific nodes, and regenerates a valid SQL statement from the changed tree.

The runnable demo on this page builds a small SQL policy and rewrite layer between user-submitted SQL and the database. It accepts one SELECT, removes a sensitive output column, adds a server-controlled tenant filter, regenerates the statement, and parses the result again before it can be executed.

View the Java source View the .NET source

What you will get

The Java and .NET projects are complete console applications with automated tests. They run offline, require no database connection, and use a bind placeholder for the tenant value. Open either project to run the complete implementation, inspect its tests, and adapt the policy to your application.

The problem this demo solves

Suppose a report builder, analytics endpoint, AI assistant, or customer-facing application accepts a query before sending it to a database. Passing that SQL straight to the database gives the application no structured place to enforce its own rules.

This demo applies three rules before database execution:

  1. The input must parse as exactly one SELECT statement.
  2. The result must not expose the internal_note column.
  3. Every accepted query must be restricted by o.tenant_id = ?, where trusted application code supplies the bind value.

The sample starts with this query:

1
2
3
4
5
6
7
SELECT o.order_id,
       o.customer_id,
       o.total_amount,
       o.internal_note
FROM sales.orders o
WHERE o.status = 'OPEN' OR o.status = 'PENDING'
ORDER BY o.created_at DESC;

The regenerated statement is equivalent to:

1
2
3
4
5
6
7
SELECT o.order_id,
       o.customer_id,
       o.total_amount
FROM sales.orders o
WHERE (o.status = 'OPEN' OR o.status = 'PENDING')
  AND o.tenant_id = ?
ORDER BY o.created_at DESC;

Two changes are visible: o.internal_note is gone, and the tenant predicate is present. The parentheses are just as important. Appending text carelessly could produce A OR B AND tenant, which SQL evaluates as A OR (B AND tenant) and could expose rows from another tenant. The demo creates a parenthesis AST node around the original OR expression before attaching the new AND node.

Why modify the AST instead of the SQL string?

SQL text is not a flat sequence of interchangeable words. The same identifier can appear in a projection, predicate, alias, comment, string literal, nested subquery, or vendor-specific construct. A regular expression cannot reliably tell those roles apart.

Requirement String or regular-expression rewrite GSP AST rewrite
Remove only an output column Can also alter literals, aliases, or nested text Removes a node from the result-column list
Add a tenant predicate Must reproduce SQL operator precedence manually Combines expression nodes and preserves grouping
Reject a second statement Delimiter splitting breaks on procedural SQL and literals Checks the parsed statement list
Support vendor dialects Requires custom text rules for each grammar Parses with the selected EDbVendor grammar
Produce auditable decisions Usually records text before and after Can record statement types, nodes, and policy outcomes

AST modification makes the transformation explicit: your policy targets a TResultColumnList, TWhereClause, or TExpression, not an accidental text match. GSP then serializes the modified tree with toScript() in Java or ToScript() in .NET.

How the pre-execution workflow works

flowchart LR
    A[User or generated SQL] --> B[Parse with the selected GSP dialect]
    B --> C{One allowed SELECT?}
    C -- No --> R[Reject and record the reason]
    C -- Yes --> D[Inspect statement, columns, and expressions]
    D --> E[Apply trusted policy changes to AST nodes]
    E --> F[Regenerate SQL from the AST]
    F --> G{Reparse and validate output?}
    G -- No --> R
    G -- Yes --> H[Bind trusted values and send to database driver]

GSP performs the parse, AST access, modification, and regeneration in the application process. No database metadata or connection is required for this demo. The application owns the policy decision and only hands the regenerated statement to its database layer after every check succeeds.

1. Parse and classify before changing anything

The demo creates a TGSqlParser for the intended database dialect and parses the complete input. It fails closed when parsing fails, when more than one statement is present, or when the statement is not a SELECT.

This first gate prevents a caller from hiding a second DELETE, UPDATE, DDL, or procedural statement after an apparently acceptable query. Production rules can narrow the accepted SELECT shape further by checking tables, functions, subqueries, set operators, joins, and clauses.

2. Inspect and modify the result-column list

The demo walks the TResultColumnList, identifies the restricted projection, and removes that result-column node. Other occurrences of the same text are not globally replaced. A production policy can use schema metadata and identifier resolution to distinguish identically named columns from different tables.

3. Build the new boolean expression as nodes

The existing WHERE condition is a logical_or_t expression. The demo makes that expression the child of a parenthesis_t node, parses the trusted tenant predicate into another expression, and joins the two with a logical_and_t node. If no WHERE clause exists, the same policy can add one through the AST.

The predicate text is controlled by the application. The tenant value remains ? so the database driver can bind it separately; user data is never concatenated into generated SQL.

4. Regenerate and validate again

Calling the script generator serializes the changed AST back to vendor SQL. The demo immediately feeds that output to a new parser instance and requires it to parse as one SELECT again. This second parse is a useful invariant at the boundary between transformation and execution: if regeneration or a custom rule produces an invalid statement, nothing reaches the database.

Clone and run the complete demo

Choose the edition used by your application. Both commands build against the published trial package and run the same policy scenario.

1
2
3
4
git clone --depth 1 https://github.com/sqlparser/gsp_demo_java.git
cd gsp_demo_java
mvn -q exec:java \
  -Dexec.mainClass=gudusoft.gsqlparser.demos.modifySqlAst.ModifySqlAst

The implementation is in ModifySqlAst.java, with its behavior tests in ModifySqlAstTest.java.

1
2
3
4
git clone --depth 1 https://github.com/sqlparser/gsp_demo_dotnet.git
cd gsp_demo_dotnet
dotnet run --project \
  src/demos/modifySqlAst/demos.modifySqlAst.csproj -c Release

The implementation is in modifySqlAst.cs, with its behavior tests in ModifySqlAstTests.cs.

The important console messages are the policy decisions, regenerated SQL, and:

1
Validation: regenerated SQL parsed successfully as one SELECT statement.

Run the focused tests after changing a policy rule:

1
mvn -q -Dtest=ModifySqlAstTest test

1
dotnet test tests/modifySqlAst/test.modifySqlAst.csproj -c Release

Where this pattern adds value

Check and rewrite customer SQL before execution

Put the policy gate in the request path before the repository, ORM raw-SQL method, JDBC command, or ADO.NET command. It can reject unsupported statement types, inspect the parsed structure, apply approved rewrites, and emit an audit record before the database sees the query.

This is useful for embedded query consoles, self-service analytics, database administration products, AI-generated SQL, and any application that accepts SQL from a less-trusted boundary. The benefit is a consistent application-level decision point rather than scattered string checks immediately before execute.

Enforce tenant or account scope

A multi-tenant service can add a tenant, organization, account, region, or workspace predicate to every eligible query. Because GSP exposes the boolean expression tree, the service can preserve AND/OR precedence and handle an existing WHERE clause deliberately.

For production use, validate table identity and aliases before adding a predicate, decide how subqueries and CTEs inherit scope, and keep the actual tenant value in a bound parameter supplied by trusted server state.

Remove or mask sensitive output columns

Report builders and data products can inspect the result-column AST before a query runs. A policy may remove restricted columns, replace an expression with a masking function, or reject SELECT * when the visible schema cannot be proven. This reduces accidental exposure and lets the application explain which projection violated policy.

Apply schema migrations without hand-editing SQL

Migration tools can rename tables and columns, replace deprecated functions, add schema qualifiers, or update clauses across stored query libraries. Node- level changes avoid modifying comments and string literals that merely contain the old name, and regenerated SQL can be reparsed and regression-tested before deployment.

Build query fixers, optimizers, and governance rules

An editor or CI rule can detect an undesirable AST shape and offer a safe fix: add a required predicate, remove a forbidden projection, normalize a function, or replace a vendor-specific construct. The original node location and policy decision can power diagnostics, previews, approval workflows, and change logs.

Integrate the pattern into your application

Keep the rewrite boundary small and explicit:

  1. Select EDbVendor from trusted configuration, not from the SQL text.
  2. Parse the complete request and reject syntax errors or disallowed statement counts and types.
  3. Evaluate allowlists for schemas, tables, functions, clauses, and query shape.
  4. Apply only server-owned rewrite rules to known AST nodes.
  5. Regenerate, reparse, and rerun the policy checks against the output.
  6. Bind tenant and user values through the database driver.
  7. Record the rule version, decisions, and a protected form of the original and rewritten SQL for diagnostics or audit.
  8. Execute with a least-privilege database identity and enforce time and resource limits.

The demo exposes rewrite(...) in Java and Rewrite(...) in .NET so the flow can be called from a web handler, query service, background job, IDE feature, or data product. Replace the sample rule constants with immutable policy configuration and return a domain result that distinguishes rejection from parser or infrastructure failure.

Security boundary and production hardening

An AST policy layer is valuable because it creates a structured, testable, and auditable decision point before execution. It is not, by itself, a complete SQL security system.

The AST gate can help with Keep these controls as well
Statement-type and statement-count restrictions Prepared statements and parameter binding
Table, column, function, and clause allowlists Least-privilege database users and roles
Tenant-filter and projection rewrites Database authorization and row-level security
Rejecting syntax or unsupported query shapes Query timeout, row, memory, and cost limits
Recording policy decisions and rewritten structure Authentication, audit storage, monitoring, and incident response

Before using a custom rewrite in production, add tests for nested subqueries, CTEs, set operations such as UNION, quoted and case-sensitive identifiers, aliases, wildcard projections, comments, functions, vendor-specific syntax, and statements with no WHERE clause. Fail closed whenever your code cannot prove that the required rule was applied to the intended node.

Next steps