Reading a Report

This page goes through a lexega-sql report from top to bottom: what each section means, how severities and --min-severity decide what is shown, and which output format suits which reader. It ends with the options that show why a rule fired or did not.

The text report#

Save this as release_42.sql:

sql
GRANT SELECT ON TABLE analytics.customers TO ROLE PUBLIC;

DELETE FROM analytics.order_staging;

ALTER TABLE analytics.customers DROP COLUMN segment_code;

DROP VIEW analytics.v_customer_segments;

DROP SEQUENCE analytics.order_seq;
bash
lexega-sql analyze release_42.sql

This run was made without a key:

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: 3
  Critical: 2
  High: 1
  Medium: 0
  Low: 0
  Context: 0

Coverage:
  Analyzed: 5 SQL statements

Data Operations:
  Tables Read: 0
  Tables Written (DML): 1
  DDL Operations: 3
  Cross-Database: No

findings:
  [CRITICAL] Avoid granting privileges to PUBLIC. Every user receives what PUBLIC is granted, which may expose data unintentionally.
    ↳ Line 1 • `GRT-TO-PUBLIC` • "PUBLIC" • release_42.sql:1:51
  [CRITICAL] Unbounded write operation detected - the statement has no WHERE clause or its WHERE clause matches every row. This affects ALL rows in the target table(s).
    ↳ Line 3 • `DML-WRITE-UNBOUNDED` • "DELETE FROM analytics.order_staging" • release_42.sql:3:1
  [HIGH] ALTER TABLE DROP COLUMN detected. Column data and dependent objects may be lost or broken.
    ↳ Line 5 • `TBL-COL-DROP` • "ALTER TABLE analytics.customers DROP COLUMN segment_code" • release_42.sql:5:1

From the top:

  • Confidence says how completely the SQL could be read. It is covered below.
  • Note appears only on a run without a key and counts the rules that ran at reduced depth. What a Key Adds explains it.
  • Summary counts the findings in this report by severity. The counts cover what the report shows, after --min-severity.
  • Coverage counts the statements analyzed, and the ones that were not.
  • Data Operations counts the tables the SQL reads and writes and its DDL statements, and says whether it reaches across databases.
  • findings lists each finding: its severity and message, then a ↳ line with the line the statement starts on, the rule ID, the text the finding points at, and the file, line and column. Every rule ID is in the Built-in Rule Reference.

On a terminal the text report is coloured by severity; --color never or the NO_COLOR environment variable turns that off.

Severities and the minimum shown#

A rule has one of five levels: critical, high, medium, low and info. A rule at low or above raises findings. A rule at info describes what a change does without judging it, and reports context: its entries are listed apart under "Context:", are not counted with the findings (the summary's Context: line counts them), and are not SARIF results, GitLab SAST vulnerabilities or review comments. The report shows findings at or above --min-severity, which defaults to high, and context only with --min-severity info. The file above has two more findings that the default hides:

bash
lexega-sql analyze release_42.sql --min-severity low
text
...
Summary:
  Total findings: 5
  Critical: 2
  High: 1
  Medium: 0
  Low: 2
  Context: 0
...
  [LOW] DROP SEQUENCE removes a sequence generator. Columns using this sequence for defaults will break.
    ↳ Line 9 • `SEQ-DROP` • "DROP SEQUENCE analytics.order_seq" • release_42.sql:9:1
  [LOW] DROP VIEW removes a view. Dependent queries and applications will break. Check for CASCADE to identify cascading object removal.
    ↳ Line 7 • `VIEW-DROP` • "DROP VIEW analytics.v_customer_segments" • release_42.sql:7:1

--min-severity only filters what the report shows and counts. A policy decides on every finding, shown or not; Policies, Exceptions and Decisions has the details.

Severity says how serious a finding is. Whether it blocks anything is up to your policy.

Coverage and confidence#

A statement Lexega cannot parse is not analyzed, and the report says so. Save this as partial.sql:

sql
DELETE FROM analytics.order_staging;

FROBNICATE analytics.widgets WITH VIGOR;
bash
lexega-sql analyze partial.sql
text
Analysis Report
========================
Confidence: MEDIUM
...
Coverage:
  Analyzed: 1 SQL statements
  Unrecognized: 1 (could not parse, no analysis)
...

Unrecognized counts the statements that were skipped. Confidence is HIGH when every statement was read and nothing a template left unrendered affects the analysis. It drops to MEDIUM or LOW when statements were skipped or only partly read, when a template left placeholders where they affect the analysis, or when a template could not be rendered. The JSON report lists each skipped statement under skipped_details, with its line number and its first text, masked: for partial.sql, "statement_prefix": "**** ****.**** WITH ****;".

To make skipped SQL fail the run instead, pass --strict; see the CLI Reference.

Output formats#

--format picks what is printed:

FormatWho reads it
textA person at a terminal. The default for analyze.
jsonA script or another tool. Every field is described below.
yamlThe same content as json, for tools that prefer YAML.
sarifGitHub code scanning and other SARIF viewers. See CI/CD Integration.
markdownA pull request comment. The default for review.
gl-sastThe GitLab security dashboard. See CI/CD Integration.
bothThe JSON report, a line ---, then the text report.

--report-out writes the report to a file or a cloud storage location as well, in JSON unless --report-artifact-format says yaml or sarif.

For a folder (analyze models/ -r), the text report is a batch summary followed by each file's findings, on standard output; the progress lines go to standard error. A script reading a folder's results should use --format json.

Fields a script reads#

The JSON report for one file looks like this, cut to the fields a script usually reads. one.sql holds the single statement DELETE FROM analytics.order_staging;:

bash
lexega-sql analyze one.sql --format json
json
{
  "schema_version": 2,
  "engine_version": "3.0.0",
...
  "summary": {
    "total_reported_findings": 1,
    "critical_count": 1,
    "high_count": 0,
    "medium_count": 0,
    "low_count": 0,
    "info_count": 0,
...
    "statements_parsed": 1,
    "statements_analyzed": 1,
    "statements_skipped": 0,
...
    "render_completeness": "full",
    "analysis_confidence": "high",
    "analysis_depth": {
      "rules_total": 957,
      "rules_limited": 57
    }
  },
  "findings": [
    {
...
      "message": "Unbounded write operation detected - the statement has no WHERE clause or its WHERE clause matches every row. This affects ALL rows in the target table(s).",
      "risk_level": "Critical",
      "matched_rule": "DML-WRITE-UNBOUNDED",
...
      "evidence": [
        {
...
          "line_number": 1,
          "column_number": 1,
...
          "statement_preview": "DELETE FROM analytics.order_staging",
          "source_file": "one.sql",
          "location": "one.sql:1:1"
        }
      ]
    }
  ],
...
  "skipped_details": [],
...
}
  • summary.total_reported_findings and the *_count fields are the counts after --min-severity; info_count counts the context entries, which total_reported_findings does not.
  • summary.statements_analyzed and statements_skipped are the coverage counts, and analysis_confidence is high, medium or low.
  • summary.analysis_depth is present only on a run without a key.
  • Each entry in findings has the rule ID in matched_rule, the severity in risk_level (Critical, High, Medium or Low) and the message. With --min-severity info, context lists the entries of info-level rules in the same shape, with risk_level Info. Its evidence entries give the location: source_file, line_number, column_number, and both together in location.

For a folder or a review, the JSON report on standard output has a batch_summary with totals across files (files_processed, files_with_findings, findings.total_reported, findings.by_level, files_blocked) and a files list whose entries hold each file's report, in the shape above, under result.report. With --policy, an entry also holds the file's decision, and the report beside it is the one the policy decided on: every finding and context entry of the file, whatever --min-severity shows. batch_summary counts what the run shows. The batch_summary.json that --report-out writes holds each file's report under per_file[].report, as the run shows it; the decisions are in the --decision-out folder.

Why a rule fired, or did not#

--explain-findings lists, statement by statement, every rule that matched, whatever --min-severity is set to. It prints this list instead of the report:

bash
lexega-sql analyze release_42.sql --explain-findings
text
Finding Explanation (by Statement)
==================================

Line 1:
  GRANT SELECT ON TABLE analytics.customers TO ROLE PUBLIC

  matched rules:
    - GRT-TO-PUBLIC

Line 3:
  DELETE FROM analytics.order_staging

  matched rules:
    - DML-WRITE-UNBOUNDED
...
Line 9:
  DROP SEQUENCE analytics.order_seq

  matched rules:
    - SEQ-DROP

--verbose prints the report and then, for each statement, every rule that matched and every rule that did not, with the conditions that failed. It is long: one block per rule per statement. For the file orders_report.sql from What a Key Adds, run without a key:

bash
lexega-sql analyze orders_report.sql --verbose
text
...
Rule Evaluation Explanation (--verbose)
=======================================

Statement: SELECT c.customer_id, c.name, o.order_id, o.total FROM customers c LEFT JOIN ord…
  span: [0..145]
  rejected (955):
...
    - Q-JOIN-LEFT-FILT
        why: @any_of[0].query.scopes.0.where_predicates.0.null_effects path did not resolve
        why: @any_of[0].query.scopes.0.where_predicates each: 0 witnesses among 1 candidate(s)
        why: @any_of[0].query.scopes each: 0 witnesses among 1 candidate(s)
        … 4 more rejection path(s) (see JSON output for full trace)
...

Each why: line names a condition of the rule and what the SQL had instead. With --format json, the trace is in the report's statement_explanations. Writing conditions like these is covered in Custom Rules.

The list of fields rules can use#

--rule-syntax prints a short guide to rule syntax: the combinators, the comparison operators, the quantifiers over lists, and the steps for finding the fields a rule can test. It needs no file:

bash
lexega-sql analyze --rule-syntax

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