Security Teams: First Scan

This page runs lexega-sql analyze on a folder of SQL Server (T-SQL) stored procedures, sends the results to GitHub code scanning or GitLab, and shows how to judge the noise on your own code. Everything before the key section runs without a key or an account.

The procedures#

The folder procs/ holds two versions of the same search, and a third file used in the key section below. procs/search_orders.sql concatenates the caller's input into the query and runs it with EXEC:

sql
CREATE PROCEDURE dbo.search_orders
    @customer_name NVARCHAR(100)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'SELECT order_id, total FROM dbo.orders WHERE customer_name = '''
             + @customer_name + N'''';
    EXEC (@sql);
END;

procs/search_orders_safe.sql runs the same query with sp_executesql and binds the input as a parameter:

sql
CREATE PROCEDURE dbo.search_orders_safe
    @customer_name NVARCHAR(100)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'SELECT order_id, total FROM dbo.orders WHERE customer_name = @name';
    EXEC sp_executesql @sql, N'@name NVARCHAR(100)', @name = @customer_name;
END;

Scan the folder#

bash
lexega-sql analyze procs/ -r --dialect mssql

-r reads every .sql file under procs/, and --dialect mssql reads them as T-SQL; without it, files are read as Snowflake SQL. On a folder, the terminal shows a summary: the files and statements analyzed, the findings by severity, the files with findings and the rules that matched. Without a key, the summary reports two findings: PROC-DYNSQL at high in procs/search_orders.sql, and DYNSQL-PARAM-TEXT at critical in procs/run_report.sql, which the key section below explains. To see each finding with its line, analyze a file:

bash
lexega-sql analyze procs/search_orders.sql --dialect mssql
text
Analysis Report
========================
Confidence: HIGH (full render, no placeholders)
Note: 57 of 957 rules use analysis that needs a license key and may stay silent or report less precisely.

Summary:
  Total findings: 1
  Critical: 0
  High: 1
  Medium: 0
  Low: 0
  Context: 0

Coverage:
  Analyzed: 4 SQL statements

Data Operations:
  Tables Read: 0
  DDL Operations: 1
  Cross-Database: No

findings:
  [HIGH] Stored procedure executes dynamic SQL with potential injection vector. SQL injection risk if inputs are not validated. Consider parameterized queries (e.g. USING clause / sp_executesql parameters).
    ↳ Line 8 • `PROC-DYNSQL` • "EXEC (@sql)" • procs/search_orders.sql:8:5

PROC-DYNSQL marks the EXEC on line 8 as dynamic SQL run inside a procedure: a risk to review, because without a key Lexega cannot see that the string holds the caller's input. The Note: line marks a run without a key: each statement is analyzed as written. Reading a Report explains every part of the report.

The parameterized version has no finding, even at medium:

bash
lexega-sql analyze procs/search_orders_safe.sql --dialect mssql --min-severity medium
text
Analysis Report
========================
Confidence: HIGH (full render, no placeholders)
Note: 57 of 957 rules use analysis that needs a license key and may stay silent or report less precisely.

Summary:
  Total findings: 0
  Critical: 0
  High: 0
  Medium: 0
  Low: 0
  Context: 0
...

analyze exits 0 whatever it finds. Failing a build on a finding is the job of a policy; see Policies, Exceptions and Decisions.

Send the results to code scanning#

--format sarif writes the findings to standard output as a SARIF 2.1.0 log. In a GitHub Actions job, upload it to code scanning:

yaml
permissions:
  contents: read
  security-events: write

steps:
  - uses: actions/checkout@v4

  - name: Install Lexega
    run: |
      curl -sSL -O https://github.com/Lexega/lexega/releases/latest/download/lexega-sql-linux-x64
      curl -sSL https://github.com/Lexega/lexega/releases/latest/download/CHECKSUMS.sha256 | grep ' lexega-sql-linux-x64$' | sha256sum -c -
      sudo install -m 755 lexega-sql-linux-x64 /usr/local/bin/lexega-sql

  - name: Scan procedures
    run: lexega-sql analyze procs/ -r --dialect mssql --min-severity medium --format sarif > lexega.sarif

  - name: Upload to code scanning
    uses: github/codeql-action/upload-sarif@v3
    with:
      sarif_file: lexega.sarif
      category: lexega-sql

--min-severity also filters the log, as it filters the report. In the log, the tool is lexega-sql. Each location of a finding is one result, with its ruleId, a level and the line of the statement. Each rule carries a security-severity score, which code scanning shows as the alert's severity. Each result's partialFingerprints value is computed from the rule, the file path and the statement's text (ignoring whitespace), not the line number, so an alert follows its statement when lines above it change. Without a key, the Note: sentence travels with the log as a notification of the run.

The scan step passes even when a statement could not be read. To fail the job instead, add --strict to the scan, and if: always() to the upload step so the log is still uploaded: with --strict the log still prints and the command exits 1. Limitations lists what Lexega does not read.

For GitLab, --format gl-sast writes GitLab's SAST report format, with one vulnerability per finding location, its severity and its rule ID. Publish it as a SAST report artifact:

yaml
sql-sast:
  before_script:
    - curl -sSL -O https://github.com/Lexega/lexega/releases/latest/download/lexega-sql-linux-x64
    - curl -sSL https://github.com/Lexega/lexega/releases/latest/download/CHECKSUMS.sha256 | grep ' lexega-sql-linux-x64$' | sha256sum -c -
    - install -m 755 lexega-sql-linux-x64 /usr/local/bin/lexega-sql
  script:
    - lexega-sql analyze procs/ -r --dialect mssql --min-severity medium --format gl-sast > gl-sast-report.json
  artifacts:
    reports:
      sast: gl-sast-report.json

Both formats are free. CI/CD Integration has the complete workflows, with triggers, and how to keep a key as a CI secret.

What a key adds#

Needs a trial or Team key. With a key, Lexega follows the input through variables and into the procedures it is passed to, and reports the concatenation that builds the string a statement executes.

The same scan of procs/search_orders.sql, with a key, reports the EXEC on line 8 at critical instead of high: the string it runs was built by concatenating @customer_name into @sql.

text
Semantic Analysis Report
========================
Confidence: HIGH (full render, no placeholders)
...
Summary:
  Total findings: 1
  Critical: 1
  High: 0
  Medium: 0
  Low: 0
  Context: 0
...
findings:
  [CRITICAL] Dynamic SQL argument is built via string concatenation or FORMAT(...) interpolation. SQL injection risk — switch to a parameterized query (USING clause / sp_executesql parameter binding).
    ↳ Line 8 • `DYNSQL-CONCAT` • "EXEC (@sql)" • procs/search_orders.sql:8:5

The input can also cross a procedure call. In procs/run_report.sql, report_by_region builds a query from @region and hands it to run_query, which executes it:

sql
CREATE PROCEDURE dbo.run_query
    @query NVARCHAR(MAX)
AS
BEGIN
    EXEC sp_executesql @query;
END;
GO

CREATE PROCEDURE dbo.report_by_region
    @region NVARCHAR(50)
AS
BEGIN
    DECLARE @q NVARCHAR(MAX) = N'SELECT * FROM dbo.sales WHERE region = ''' + @region + N'''';
    EXEC dbo.run_query @query = @q;
END;

Without a key, each statement is analyzed as written. The sp_executesql in run_query runs the procedure's own parameter @query as SQL, so whoever calls run_query chooses the statement; it is reported at critical (DYNSQL-PARAM-TEXT), and report_by_region has no finding of its own:

bash
lexega-sql analyze procs/run_report.sql --dialect mssql --min-severity medium
text
Analysis Report
========================
Confidence: HIGH (full render, no placeholders)
Note: 57 of 957 rules use analysis that needs a license key and may stay silent or report less precisely.
...
findings:
  [CRITICAL] Procedure or function runs one of its parameters as SQL: whoever calls it chooses the statement that runs. SQL injection risk — build the statement inside the routine and bind runtime values (USING clause / sp_executesql parameters).
    ↳ Line 5 • `DYNSQL-PARAM-TEXT` • "EXEC sp_executesql @query" • procs/run_report.sql:5:5

With a key, the call on line 14 is also reported, at critical, as the place where the concatenated query enters dynamic SQL:

text
Semantic Analysis Report
========================
Confidence: HIGH (full render, no placeholders)
...
Summary:
  Total findings: 2
  Critical: 2
  High: 0
  Medium: 0
  Low: 0
  Context: 0
...
findings:
  [CRITICAL] Dynamic SQL argument is built via string concatenation or FORMAT(...) interpolation. SQL injection risk — switch to a parameterized query (USING clause / sp_executesql parameter binding).
    ↳ Line 14 • `DYNSQL-CONCAT` • "EXEC dbo.run_query @query = @q" • procs/run_report.sql:14:5
  [CRITICAL] Procedure or function runs one of its parameters as SQL: whoever calls it chooses the statement that runs. SQL injection risk — build the statement inside the routine and bind runtime values (USING clause / sp_executesql parameters).
    ↳ Line 5 • `DYNSQL-PARAM-TEXT` • "EXEC sp_executesql @query" • procs/run_report.sql:5:5

What a Key Adds lists everything else a key changes.

Judge the noise#

Run the scan on a folder of your own procedures. It needs no key and no account, and nothing leaves the machine it runs on. Without a key, dynamic SQL built from quoted names, such as N'SELECT * FROM ' + QUOTENAME(@t), or from the script's own constants, is reported at critical (DYNSQL-CONCAT) where the EXEC concatenates it, and at high (DYNSQL, or PROC-DYNSQL in a procedure) where it is built in a variable first; with a key, a construction whose every value is quoted or a constant of the script's, and whose statements are the script's own text, is reported as DYNSQL-PARAM-CLEAN, context only. A text that different branches build differently, such as optional filters each appended under its own IF, is reported as a concatenation, DYNSQL-CONCAT, even when every piece is literal.

By default the report shows critical and high findings. --min-severity lowers the threshold to medium, low or info, and it changes only what the report shows. At info, the parameterized procedure has no finding at all: its EXEC and the new procedure are listed as context, entries for audit rather than risks:

bash
lexega-sql analyze procs/search_orders_safe.sql --dialect mssql --min-severity info
text
Analysis Report
========================
Confidence: HIGH (full render, no placeholders)
Note: 57 of 957 rules use analysis that needs a license key and may stay silent or report less precisely.
...
Context:
  [INFO] Stored procedure executed via EXEC. Audit trail: verify the procedure exists and caller has EXECUTE permission.
    ↳ Line 7 • `INFO-MSSQL-EXEC-PROC` • "EXEC sp_executesql @sql, N'****', @name = @customer_name" • procs/search_orders_safe.sql:7:5
  [INFO] Stored procedure created.
    ↳ Line 1 • `PROC-NEW` • "CREATE PROCEDURE dbo.search_orders_safe @customer_name NVARCHAR(100) AS BEGIN DE" • procs/search_orders_safe.sql:1:1

Any built-in rule can be raised, lowered, reworded or switched off by its ID in a rules file. This file raises PROC-DYNSQL to critical. Without a key, PROC-DYNSQL reports the EXEC (@sql) in procs/search_orders.sql, so that finding is reported at critical instead of high:

yaml
# lexega-rules.yml
rules:
  - id: PROC-DYNSQL
    risk_level: critical
bash
lexega-sql analyze procs/ -r --dialect mssql --custom-rules lexega-rules.yml

PROC-DYNSQL reports only dynamic SQL that the more specific dynamic SQL rules do not: procs/run_report.sql is reported by DYNSQL-PARAM-TEXT, and with a key a concatenated call is reported by DYNSQL-CONCAT, so this override changes neither. Rules files are free. Custom Rules covers overriding built-in rules and writing your own.

Something missing or wrong? Open an issue on GitHub or write to [email protected].