DEV Community: GIAN FRANCO AROCUTIPA AROCUTIPA

Testing Management in CI/CD: GitHub Actions vs GitLab CI vs Jenkins with Real Pipelines

GIAN FRANCO AROCUTIPA AROCUTIPA — Mon, 06 Jul 2026 01:00:27 +0000

Abstract

An automated test suite is only as good as the system that runs it: if tests execute only when a developer remembers to run them, the contract they protect is enforced only sometimes. This article compares three of the most widely used CI/CD testing management tools — GitHub Actions, GitLab CI/CD and Jenkins — by giving them identical work: executing the same real API test suite (Jest + Supertest, 8 integration tests, 100% coverage, from my previous article) on every push. Instead of comparing marketing pages, we compare the three real pipeline files, side by side, in one public repository. The comparison covers configuration style, hosting model, dependency caching, artifacts and ecosystem, and closes with a practical decision guide. Full demo repo with all three pipelines: https://github.com/GianfrancoArocutipa/ci-testing-comparative.

The experiment: one suite, three pipelines

Comparing CI tools is usually apples to oranges — different projects, different jobs. To make it fair, this repository gives all three tools the exact same job:

Check out the code.
Install dependencies with npm ci.
Run npx jest --coverage --ci (8 API tests against an Express app).
Keep the coverage report as an artifact.
Fail the build if any test fails — blocking the merge.

The repo contains all three configurations at once, which is itself a real-world pattern: teams migrating between CI providers often run them in parallel for a while.

.github/workflows/ci.yml   → GitHub Actions
.gitlab-ci.yml             → GitLab CI/CD
Jenkinsfile                → Jenkins

Pipeline 1 — GitHub Actions

Configuration lives in .github/workflows/, one YAML file per workflow, triggered by events (push, pull request, schedule, manual dispatch):

name: CI — API Tests

on:
  push:
    branches: [main]
  pull_request:
    branches: [main]

jobs:
  test:
    runs-on: ubuntu-latest        # GitHub-hosted runner, zero setup
    strategy:
      matrix:
        node-version: [20, 22]    # same suite on two Node versions

    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: ${{ matrix.node-version }}
          cache: npm              # dependency cache in one line
      - run: npm ci
      - run: npx jest --coverage --ci
      - uses: actions/upload-artifact@v4
        if: always()
        with:
          name: coverage-node-${{ matrix.node-version }}
          path: coverage/

What stands out: the Marketplace. actions/checkout, actions/setup-node and actions/upload-artifact are reusable building blocks maintained by others — caching node_modules, something that takes real thought elsewhere, is literally cache: npm. The matrix is also the most elegant of the three: two lines and the suite runs on Node 20 and 22 in parallel. Runners are hosted by GitHub with a generous free tier for public repos, and failed checks block the pull request natively.

Pipeline 2 — GitLab CI/CD

One single file at the repo root, organized around stages and a Docker-first philosophy — every job declares the container image it runs in:

image: node:22          # every job runs inside this Docker image

stages:
  - test

cache:                   # cache keyed to the lockfile
  key:
    files:
      - package-lock.json
  paths:
    - .npm/

api-tests:
  stage: test
  script:
    - npm ci --cache .npm --prefer-offline
    - npx jest --coverage --ci
  coverage: '/All files[^|]*\|\s*([\d\.]+)/'   # GitLab parses coverage % from the log
  artifacts:
    when: always
    paths:
      - coverage/
    expire_in: 1 week

What stands out: the coverage: keyword — a regex that extracts the coverage percentage straight from the job log and displays it on every merge request, with no plugin. Artifacts have first-class expiration policies. The Docker-first model makes environments extremely reproducible: "it runs in node:22" is the whole story. The trade-off is that caching is more manual than GitHub's one-liner, and the reusable-component catalog is smaller than GitHub's Marketplace.

Pipeline 3 — Jenkins

The veteran. Configuration is a Jenkinsfile in Groovy, and the fundamental difference is the hosting model: you run the server yourself.

pipeline {
    agent {
        docker { image 'node:22' }   // requires Docker on the Jenkins node
    }

    stages {
        stage('Install') {
            steps { sh 'npm ci' }
        }
        stage('Test') {
            steps { sh 'npx jest --coverage --ci' }
        }
    }

    post {
        always {
            archiveArtifacts artifacts: 'coverage/**', allowEmptyArchive: true
        }
        failure {
            echo 'API contract broken — merge should be blocked.'
        }
    }
}

What stands out: total control. Jenkins runs on your own infrastructure — a hard requirement in banks, hospitals or any environment where code cannot leave the network — and its ~1,900 plugins integrate with practically everything ever built. The post block gives fine-grained control over what happens on success, failure or always. The price is operational: you install it, update it, secure it and scale it. There is no free hosted tier because there is no hosted anything; caching, PR blocking and secrets management all require configuration that the other two give you out of the box.

Side-by-side comparison

	GitHub Actions	GitLab CI/CD	Jenkins
Config	YAML per workflow	Single YAML	Groovy `Jenkinsfile`
Hosting	GitHub-hosted (or self)	GitLab-hosted (or self)	Self-hosted only
Setup effort	None	None	Server install + maintenance
Dependency cache	`cache: npm` (1 line)	Manual `cache:` block	Plugin / manual
Test matrix	Native, 2 lines	`parallel:matrix`	Plugin / scripted
Coverage on PR/MR	Via actions/artifacts	Native `coverage:` regex	Plugin (Cobertura, etc.)
Reusability	Marketplace (20k+ actions)	CI/CD components catalog	~1,900 plugins
Free tier	2,000 min/mo (∞ public)	400 compute min/mo	Free software, your hardware
Best fit	Code already on GitHub	GitLab shops, Docker-first teams	On-premise / regulated environments

(Free-tier numbers as of their current published plans — check each vendor's pricing page.)

Other tools in this space follow the same patterns: CircleCI and Travis CI resemble the hosted-YAML model, Bitbucket Pipelines is GitLab-style inside Atlassian, TeamCity is Jenkins-like with a commercial polish, and Tekton brings the Jenkins philosophy natively into Kubernetes.

So which one should you use?

The honest answer from running the same suite on all three: the tool matters less than the gate. All three ran the 8 tests, produced the coverage report and failed correctly when I broke a status code on purpose. The decision is about context, not capability: if your code lives on GitHub, Actions is the frictionless default — the matrix and marketplace saved the most lines in this experiment. If your organization runs GitLab, its CI is deeply integrated and the MR coverage display is genuinely nice. If your constraints say nothing leaves our network, Jenkins remains the standard answer, and its plugin ecosystem still covers cases the hosted tools don't.

Limitations worth knowing

This comparison covers the testing-management core (run suite, cache, artifacts, block merge) on a small Node project; it does not measure large-monorepo performance, deployment features (CD), or enterprise concerns like compliance controls and audit logs, where the tools differ much more. Free-tier limits and pricing also change frequently — verify them before committing a team.

Conclusion

We gave three CI/CD tools identical work — a real API test suite — and compared the actual configuration files instead of feature lists. GitHub Actions won on ergonomics, GitLab CI on integrated reporting, Jenkins on control and sovereignty. Whatever you choose, the destination is the same and non-negotiable: your tests must run on every push, automatically, and a red build must block the merge. Everything else is syntax.

Demo repository (all three pipelines): https://github.com/GianfrancoArocutipa/ci-testing-comparative(#)

Applying API Testing Frameworks: Jest + Supertest with Real-World Examples

GIAN FRANCO AROCUTIPA AROCUTIPA — Mon, 06 Jul 2026 00:10:21 +0000

Abstract

APIs are contracts: a client sends a request and expects a specific status code, specific headers and a specific body shape. When that contract breaks silently, every consumer breaks with it. This article demonstrates the complete application of an API testing stack — Jest as the test runner and Supertest as the HTTP assertion library — to a real Express REST API for a vehicle-workshop management system. The suite covers the five things every API test should verify: status codes, response bodies, headers, authentication and validation, plus state changes across requests. The result: 8 integration tests running in 0.82 seconds with 100% code coverage, without starting a server or opening a port, fully automated in a GitHub Actions pipeline that runs on every push against two Node.js versions. Full demo repo: github.com/YOUR_USER/api-testing-demo (replace with your repo link).

Why a framework instead of manual testing?

You can test an API by hand with Postman or curl — once. The problem is the 500th time, after every refactor, at 2 a.m. before a release. API testing frameworks turn those manual checks into code: repeatable, versioned, fast, and executable by a machine on every commit. Among the tools in the ecosystem (REST Assured for Java, pytest for Python, Karate, SoapUI), the standard pairing in the Node.js world is Jest + Supertest, and it has a trick the others don't: it talks to your Express app in memory, with no server process and no network at all.

The API under test

A small REST API for a vehicle workshop: list vehicles, fetch one by plate, and register new ones. The key design decision for testability is that app.js exports the app without calling listen():

// src/app.js (summary)
function createApp() {
  const app = express();
  app.use(express.json());

  // In-memory store — fresh per instance, so every test starts clean
  const vehicles = [
    { plate: 'ABC-123', owner: 'Maria Lopez', model: 'Toyota Hilux', status: 'in_service' },
    { plate: 'XYZ-789', owner: 'Jose Quispe', model: 'Kia Rio', status: 'ready' },
  ];

  app.get('/api/vehicles', ...);        // list + ?status= filter
  app.get('/api/vehicles/:plate', ...); // 200 or 404
  app.post('/api/vehicles', ...);       // 401 without API key, 400 invalid, 409 duplicate, 201 created

  return app;
}
module.exports = { createApp };

Supertest receives that app object directly — it handles the HTTP layer internally. No port conflicts, no "is the server up yet?" flakiness.

The five assertions every API test should make

1. Status codes and headers. The most basic contract:

test('returns 200 with the vehicle list and count', async () => {
  const res = await request(app).get('/api/vehicles');

  expect(res.status).toBe(200);
  expect(res.headers['content-type']).toMatch(/application\/json/);
  expect(res.body.count).toBe(2);
});

2. Response body shape. toEqual for exact matches, toMatchObject for partial ones:

test('returns 200 and the full vehicle object when it exists', async () => {
  const res = await request(app).get('/api/vehicles/ABC-123');

  expect(res.status).toBe(200);
  expect(res.body).toEqual({
    plate: 'ABC-123',
    owner: 'Maria Lopez',
    model: 'Toyota Hilux',
    status: 'in_service',
  });
});

3. Error paths. A 404 is part of the contract too — clients depend on its shape:

test('returns 404 with an error message for an unknown plate', async () => {
  const res = await request(app).get('/api/vehicles/ZZZ-999');

  expect(res.status).toBe(404);
  expect(res.body.error).toMatch(/not found/);
});

4. Authentication. Headers are set with .set():

test('returns 401 when the API key is missing', async () => {
  const res = await request(app).post('/api/vehicles').send(newVehicle);
  expect(res.status).toBe(401);
});

5. State changes across requests. The most real-world test of all: create a resource, then fetch it back to prove it actually persisted:

test('creates the vehicle: 201, Location header and persisted state', async () => {
  const res = await request(app)
    .post('/api/vehicles')
    .set('x-api-key', API_KEY)
    .send(newVehicle);

  expect(res.status).toBe(201);
  expect(res.headers.location).toBe('/api/vehicles/DEF-456');

  const check = await request(app).get('/api/vehicles/DEF-456');
  expect(check.status).toBe(200); // the state really changed
});

Validation (400 with the full error list) and conflicts (409 on duplicate plates) complete the suite. One detail that prevents 90% of flaky test suites: a beforeEach that rebuilds the app, so every test gets a fresh, isolated state — order never matters.

Running it

npm test

Real output:

PASS tests/vehicles.test.js
  GET /api/vehicles
    ✓ returns 200 with the vehicle list and count (69 ms)
    ✓ filters by status with a query parameter (8 ms)
  GET /api/vehicles/:plate
    ✓ returns 200 and the full vehicle object when it exists (6 ms)
    ✓ returns 404 with an error message for an unknown plate (5 ms)
  POST /api/vehicles
    ✓ returns 401 when the API key is missing (14 ms)
    ✓ creates the vehicle: 201, Location header and persisted state (9 ms)
    ✓ returns 400 listing every validation error (7 ms)
    ✓ returns 409 when the plate is already registered (5 ms)

Test Suites: 1 passed, 1 total
Tests:       8 passed, 8 total
Time:        0.82 s

Eight full HTTP request/response cycles in under a second. And npm run test:coverage confirms the suite exercises every line:

File      | % Stmts | % Branch | % Funcs | % Lines
----------|---------|----------|---------|--------
All files |     100 |      100 |     100 |     100
 app.js   |     100 |      100 |     100 |     100

Automation: tests on every push with GitHub Actions

Tests you run manually are tests someone will eventually skip. This workflow runs the entire suite on every push and pull request, on a matrix of Node 20 and 22, and fails the build if any contract is broken:

# .github/workflows/api-tests.yml
name: API Tests — Jest + Supertest

on:
  push:
    branches: [main]
  pull_request:
    branches: [main]

jobs:
  api-tests:
    runs-on: ubuntu-latest
    strategy:
      matrix:
        node-version: [20, 22]
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: ${{ matrix.node-version }}
          cache: npm
      - run: npm ci
      - run: npx jest --coverage --ci

If a teammate's refactor changes a status code or drops a field from a response, the pipeline goes red and the merge is blocked — the broken contract never reaches the clients.

Limitations worth knowing

Supertest tests the app in isolation: it won't catch problems in your reverse proxy, TLS setup or real network latency, and here the datastore is in-memory (in production suites you'd point tests at a disposable test database, e.g. with Docker). It's also JavaScript-only — for cross-language teams, contract-testing tools like Pact or collection runners like Newman complement it. Integration tests like these are the middle of the testing pyramid: they don't replace unit tests below or a few end-to-end smoke tests above.

Conclusion

With two dev dependencies and one design decision — exporting the app without listen() — we built a suite that verifies status codes, bodies, headers, auth, validation and persistence in 0.82 seconds at 100% coverage, and wired it into CI so the API contract is enforced on every commit forever. If your API doesn't have tests like these yet, this is one of the highest return-on-effort practices in backend development.

Demo repository (code + CI workflow): github.com/YOUR_USER/api-testing-demo

Applying SAST to Infrastructure as Code: Scanning Terraform with Checkov

GIAN FRANCO AROCUTIPA AROCUTIPA — Sun, 05 Jul 2026 22:16:58 +0000

Abstract

Infrastructure as Code (IaC) has turned cloud infrastructure into source code — and, therefore, into a valid target for Static Application Security Testing (SAST). This article demonstrates the complete application of Checkov, an open-source IaC static analysis tool listed in the OWASP Source Code Analysis Tools catalog, to a Terraform project describing a small cloud system (S3, RDS and EC2). The initial scan detected 27 failed security checks — including a publicly readable bucket, a database exposed to the internet with a hardcoded password, and an instance vulnerable to SSRF credential theft — in roughly ten seconds, without an AWS account and without executing terraform. The configuration was then hardened to 0 failed checks (61 passed), with the three non-applicable policies suppressed through inline, documented justifications. Finally, the scan was integrated into a GitHub Actions pipeline that blocks any pull request reintroducing a misconfiguration, illustrating the DevSecOps principle of security as an automated gate. The full demo code and CI workflow are available at https://github.com/GianfrancoArocutipa/checkov-iac-demo.

SAST for infrastructure? Yes.

Static Application Security Testing (SAST) is usually explained with application code: find SQL injection in PHP, XSS in JavaScript. But since Infrastructure as Code became the standard — Terraform, Pulumi, OpenTofu, CloudFormation — your infrastructure is also source code. And it fails in its own ways: a public S3 bucket, a database exposed to the internet, an SSH port open to the world. These misconfigurations are behind a huge share of real cloud breaches, and the beautiful part is that they are visible in the code before anything is deployed. That is exactly what IaC SAST tools do: parse your .tf files and compare every resource against hundreds of security policies — no cloud account needed, no terraform apply, no risk.

Why Checkov?

Checkov (by Prisma Cloud) is open source, ships 1,000+ built-in policies for AWS, Azure and GCP, and supports Terraform, OpenTofu, CloudFormation, Kubernetes, Dockerfiles and more. It installs with a single pip install, needs no configuration to start, and exports SARIF reports that plug directly into GitHub Code Scanning. It also does something many scanners don't: graph-based checks (IDs starting with CKV2_) that reason about relationships between resources — for example, "this bucket has no aws_s3_bucket_public_access_block resource attached to it".

The victim: a workshop system in Terraform

The demo describes the infrastructure of a vehicle-workshop management system: an S3 bucket for diagnostic reports, a PostgreSQL RDS database, and an EC2 app server. Here are the highlights of the vulnerable version:

# S3 bucket for vehicle diagnostic reports
resource "aws_s3_bucket" "reports" {
  bucket = "workshop-diagnostic-reports"
  acl    = "public-read" # anyone on the internet can read customer reports
}

# SSH open to the entire internet
resource "aws_security_group" "app" {
  ingress {
    from_port   = 22
    to_port     = 22
    protocol    = "tcp"
    cidr_blocks = ["0.0.0.0/0"] # brute-force attacks welcome
  }
}

# The database
resource "aws_db_instance" "workshop" {
  engine              = "postgres"
  username            = "admin"
  password            = "SuperSecret123!" # hardcoded secret in source control
  publicly_accessible = true              # database exposed to the internet
  storage_encrypted   = false             # customer data stored in plaintext
}

Every one of those comments is a real incident waiting to happen — and every one of them maps to items in the OWASP Top 10 world (security misconfiguration, cryptographic failures, secrets in code).

Step 1 — Install Checkov

pip install checkov

That's the entire setup. No API keys, no cloud credentials, no Terraform binary.

Step 2 — Scan the vulnerable code

checkov -d terraform/vulnerable --compact

The result, in about ten seconds:

terraform scan results:

Passed checks: 16, Failed checks: 27, Skipped checks: 0

Check: CKV_AWS_24: "Ensure no security groups allow ingress from 0.0.0.0:0 to port 22"
        FAILED for resource: aws_security_group.app
        File: /main.tf:31-49
Check: CKV_AWS_20: "S3 Bucket has an ACL defined which allows public READ access."
        FAILED for resource: aws_s3_bucket.reports
        File: /main.tf:22-25
Check: CKV_AWS_17: "Ensure all data stored in RDS is not publicly accessible"
        FAILED for resource: aws_db_instance.workshop
        File: /main.tf:55-67
Check: CKV_AWS_16: "Ensure all data stored in the RDS is securely encrypted at rest"
        FAILED for resource: aws_db_instance.workshop
        File: /main.tf:55-67
Check: CKV_AWS_79: "Ensure Instance Metadata Service Version 1 is not enabled"
        FAILED for resource: aws_instance.app
        File: /main.tf:73-82
...

27 failed checks on ~80 lines of Terraform. Each finding has a stable ID (CKV_AWS_24), a human-readable policy, the exact resource and the line range. Note the last one: Checkov even catches that the EC2 instance allows IMDSv1 — the metadata service version abused in real SSRF attacks (like the Capital One breach) to steal cloud credentials. That's not a check most humans remember during code review.

Step 3 — Harden the code

The fixed version remediates every class of finding:

# Secrets: injected via variable, never hardcoded
variable "db_password" {
  type      = string
  sensitive = true
}

# S3: private, versioned, encrypted with a rotating KMS key
resource "aws_s3_bucket_public_access_block" "reports" {
  bucket                  = aws_s3_bucket.reports.id
  block_public_acls       = true
  block_public_policy     = true
  ignore_public_acls      = true
  restrict_public_buckets = true
}

# Network: SSH only from the admin network, egress restricted to HTTPS
ingress {
  description = "SSH from the admin network only"
  from_port   = 22
  to_port     = 22
  protocol    = "tcp"
  cidr_blocks = [var.admin_cidr]
}

# EC2: encrypted disk + IMDSv2 enforced (blocks SSRF credential theft)
metadata_options {
  http_endpoint = "enabled"
  http_tokens   = "required"
}

Plus: RDS gets storage_encrypted, publicly_accessible = false, deletion protection, Multi-AZ, IAM authentication, CloudWatch logs and forced SSL in transit.

Step 4 — Handle the checks that don't apply (the honest way)

Three checks kept failing even on the hardened code: cross-region replication, S3 event notifications and bucket access logging. They are operational policies that make sense for production at scale, not for this demo. Deleting them from the report would be lying to yourself — instead, Checkov supports inline, documented suppressions placed inside the resource:

resource "aws_s3_bucket" "reports" {
  #checkov:skip=CKV_AWS_18:Access logging is centralized at the org level
  #checkov:skip=CKV_AWS_144:Cross-region replication not required for a single-region demo
  #checkov:skip=CKV2_AWS_62:Event notifications not needed; no downstream consumers
  bucket = "workshop-diagnostic-reports"
}

Every exception is now version-controlled, justified and visible in the report as skipped — an auditor can review each decision. The final scan:

terraform scan results:

Passed checks: 61, Failed checks: 0, Skipped checks: 3

From 27 failed to 0 failed, with every exception documented.

Step 5 — Automate it: scan on every push with GitHub Actions

A manual scan is a scan someone will eventually skip. This workflow runs Checkov on every push and pull request, fails the build on any finding, and uploads a SARIF report so results appear in GitHub's Security → Code scanning tab as inline annotations on the exact Terraform lines:

# .github/workflows/sast-checkov.yml
name: SAST — Checkov IaC Scan

on:
  push:
    branches: [main]
  pull_request:
    branches: [main]

permissions:
  contents: read
  security-events: write

jobs:
  checkov-iac-scan:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-python@v5
        with:
          python-version: '3.12'

      - run: pip install checkov

      - run: >
          checkov -d terraform
          --output cli --output sarif
          --output-file-path console,results.sarif

      - uses: github/codeql-action/upload-sarif@v3
        if: always()
        with:
          sarif_file: results.sarif

If a teammate opens a PR that re-exposes the database or opens SSH to the world, the pipeline goes red and the merge is blocked. The insecure infrastructure never gets deployed, because it never even gets merged.

Limitations worth knowing

Checkov analyzes the code, not the running cloud: drift (someone clicking in the AWS console) is invisible to it, and values only known at runtime can produce blind spots. Policy checks can also feel noisy at first — that's what documented skips and severity filtering are for. IaC SAST is one layer: it complements — not replaces — cloud posture management and runtime monitoring.

Conclusion

Ten seconds of static analysis found 27 misconfigurations — public buckets, exposed databases, plaintext storage, IMDSv1 — before a single resource existed in the cloud. We fixed them all, documented the exceptions, and wired the scan into CI so it runs on every commit forever. If your infrastructure lives in Terraform, Pulumi or OpenTofu, there is no cheaper security win than this.

Demo repository (code + CI workflow): https://github.com/GianfrancoArocutipa/checkov-iac-demo(#)

Applying SAST to a PHP Application with Psalm's Taint Analysis

GIAN FRANCO AROCUTIPA AROCUTIPA — Sun, 05 Jul 2026 05:53:56 +0000

TL;DR — I took a small, intentionally vulnerable PHP application (a vehicle-workshop search and report system), ran Psalm — an open-source static analysis tool listed in the OWASP Source Code Analysis Tools catalog — with taint analysis enabled, and it caught SQL Injection, XSS and Path Traversal without executing a single line of code. Then I automated the scan with GitHub Actions so vulnerable code can never be merged again. Full demo repo: https://github.com/GianfrancoArocutipa/psalm-sast-demo(#)

What is SAST, in one paragraph?

Static Application Security Testing (SAST) analyzes the source code of an application — not the running app — looking for patterns that lead to vulnerabilities. Think of it as a security-focused code review performed by a machine: it runs in seconds, it never gets tired, and it can be wired into your CI pipeline so every commit is inspected before it reaches production. This is the "shift-left" idea at the heart of DevSecOps: find the bug when it costs 5 minutes to fix, not when it is being exploited.

Why Psalm?

Psalm, created by Vimeo, is best known as a PHP type checker, but since version 3 it ships a powerful taint analysis engine. Taint analysis tracks how untrusted data (a source, like $_GET) flows through the program until it reaches a dangerous function (a sink, like an SQL query or echo). If tainted data reaches a sink without passing through a sanitizer, Psalm raises an error and prints the entire data-flow path.

I chose it because it is open source, PHP-native, extremely fast, and produces SARIF reports that integrate natively with GitHub Code Scanning.

The victim: a small workshop app

The demo simulates two endpoints of a vehicle-workshop management system. Here is the vulnerable search endpoint:

<?php
// src/vulnerable/search.php
$pdo = new PDO('mysql:host=localhost;dbname=workshop', 'app', 'secret');

$term = $_GET['q'];                                            // tainted source

$stmt = $pdo->query(
    "SELECT plate, owner, model FROM vehicles WHERE plate LIKE '%$term%'"
);                                                             // SQL sink 💥

echo "<h2>Results for: " . $term . "</h2>";                    // HTML sink 💥

foreach ($stmt as $row) {
    echo "<li>{$row['plate']} — {$row['owner']} ({$row['model']})</li>";
}

Two classic OWASP Top 10 issues in 15 lines: the query string is concatenated into SQL (A03: Injection) and echoed back raw (XSS). A third endpoint, report.php, reads a file whose name comes from $_GET['file'] — textbook Path Traversal:

$file = $_GET['file'];                        // tainted source
header('Content-Type: application/pdf');
readfile('/var/app/reports/' . $file);        // file sink 💥  ?file=../../etc/passwd

Step 1 — Install and configure Psalm

composer require --dev vimeo/psalm
vendor/bin/psalm --init src 4

--init generates a psalm.xml config pointing at the src directory with error level 4 (relaxed typing — we only care about security here).

Step 2 — Run the taint analysis

vendor/bin/psalm --taint-analysis --no-cache

Psalm answers in about two seconds (output trimmed):

ERROR: TaintedSql
  at src/vulnerable/search.php:17:5
  Detected tainted SQL (see https://psalm.dev/244)
    $_GET['q'] - src/vulnerable/search.php:14:9
    $term - src/vulnerable/search.php:14:1
    concat - src/vulnerable/search.php:17:5
    call to PDO::query - src/vulnerable/search.php:16:15

ERROR: TaintedHtml
  at src/vulnerable/search.php:20:6
  Detected tainted HTML (see https://psalm.dev/245)
    $_GET['q'] → $term → concat → echo

ERROR: TaintedFile
  at src/vulnerable/report.php:15:10
  Detected tainted file handling (see https://psalm.dev/255)
    $_GET['file'] → $file → concat → call to readfile

This is the killer feature: Psalm does not just say "line 17 is dangerous" — it reconstructs the full path the attacker-controlled value travels, from the superglobal to the sink. In a real codebase that flow might cross five files and three function calls; Psalm follows it anyway.

Step 3 — Fix the code and watch the errors disappear

The remediation is exactly what the taint model expects: cut the flow or sanitize it.

<?php
// src/fixed/search.php
$term = (string) ($_GET['q'] ?? '');

// SQLi fix: prepared statement — data never touches the SQL grammar
$stmt = $pdo->prepare(
    'SELECT plate, owner, model FROM vehicles WHERE plate LIKE :term'
);
$stmt->execute([':term' => '%' . $term . '%']);

// XSS fix: encode before it reaches HTML
$safeTerm = htmlspecialchars($term, ENT_QUOTES, 'UTF-8');
echo "<h2>Results for: {$safeTerm}</h2>";

For the path traversal, the fixed version never lets the user control a path at all — it validates a strict report ID (RPT-2026-00042) with a regex and maps it internally. Re-running the scan:

------------------------------
No errors found!
------------------------------
Psalm was able to infer types for 100% of the codebase

Psalm knows PDO::prepare + bound parameters and htmlspecialchars are sanitizing boundaries, so the taint chain is broken and the errors vanish.

Step 4 — Automate it: SAST on every push with GitHub Actions

A scan you run manually is a scan you will eventually forget. This workflow runs the taint analysis on every push and pull request, fails the build if anything is found, and uploads a SARIF report so findings show up in GitHub's Security → Code scanning tab as inline PR annotations:

# .github/workflows/sast-psalm.yml
name: SAST — Psalm Taint Analysis

on:
  push:
    branches: [main]
  pull_request:
    branches: [main]

permissions:
  contents: read
  security-events: write

jobs:
  psalm-taint-analysis:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - uses: shivammathur/setup-php@v2
        with:
          php-version: '8.2'
          tools: composer

      - run: composer install --no-progress --prefer-dist

      - run: vendor/bin/psalm --taint-analysis --no-cache --report=results.sarif

      - uses: github/codeql-action/upload-sarif@v3
        if: always()
        with:
          sarif_file: results.sarif

The result: if a teammate opens a PR reintroducing string concatenation into a query, the pipeline goes red, the finding is annotated on the exact line of the diff, and the merge is blocked. That is DevSecOps in practice — security as a gate, not as an afterthought.

Limitations worth knowing

No SAST tool is magic. Psalm's taint analysis can produce false positives (e.g., a custom sanitizer it does not recognize — you can teach it with @psalm-taint-escape annotations) and false negatives (dynamic constructs like $$var or eval confuse static analysis). It also cannot find business-logic flaws, misconfigurations or vulnerable dependencies — those need DAST, SCA and human review. SAST is one layer of defense, not the whole wall.

Conclusion

In under ten minutes we installed an open-source SAST tool, found three real OWASP Top 10 vulnerability classes with full data-flow traces, fixed them, and locked the door with a CI pipeline. If your project touches PHP, there is very little excuse not to have psalm --taint-analysis running on every commit.

Demo repository (code + CI workflow): https://github.com/GianfrancoArocutipa/psalm-sast-demo(#)

SQL Meets AI - Inside the Rule Engine of an AI-Powered SQL Validation Solution

GIAN FRANCO AROCUTIPA AROCUTIPA — Thu, 02 Jul 2026 02:46:36 +0000

AI database solutions are usually presented as one single idea: type a question in English, get a SQL query back. Text-to-SQL is impressive, but it is only one corner of a much larger space. In real teams, AI can help databases in at least four different ways:

Generation — turning natural language into SQL (text-to-SQL).
Validation — reviewing SQL before it touches the database.
Optimization — suggesting indexes, rewriting slow queries, explaining execution plans.
Governance — enforcing read-only policies, blocking dangerous statements, auditing access.

This article focuses on the second and fourth categories, using a real, runnable project as the case study: an AI SQL Validation Agent built with Node.js and TypeScript.

Full source code here:

👉 github.com/cs2026086510-a11y/sql-ai-validator-agent

My teammate Cristian Soto already wrote a great walkthrough of the REST API layer and the Codex Skill (read it here). So instead of repeating that, this article goes inside the engine: how the validation rules actually work, why string literals must be stripped before anything else, how SQL dialect differences are detected, and how this kind of agent fits into real-world workflows like CI pipelines and LLM tool-calling.

Why "AI validation" instead of just running the query?

A database will always tell you when a query is broken — after you run it. That has three problems in real projects:

The error arrives too late. In a CI pipeline or a code review, you want feedback before merge, not in production logs.
The error is cryptic. syntax error at or near "FORM" is accurate but unhelpful; a junior developer still has to figure out what went wrong.
The database has no policy opinion. It will happily execute DROP TABLE users; if the credentials allow it.

An AI-style validation agent flips the model: SQL is treated as untrusted input, analyzed statically, and returned with a structured, human-readable review — the same way a senior developer would comment on a pull request.

Developer / CI / LLM
        |
        v
   REST API (Express + Zod)
        |
        v
   Rule Engine  ←  this article
        |
        v
   Explanation Agent
        |
        v
   Structured JSON review

The agent never connects to a database. That is a feature, not a limitation: it can run anywhere (CI runners, editors, serverless functions) with zero credentials and zero risk.

Step zero: never trust raw SQL text

Here is the most underrated part of the whole engine. Before any rule runs, the SQL string is normalized and its string literals are stripped:

const stringLiteralPattern = /'(?:''|[^'])*'|"(?:\\"|[^"])*"/g;

export function normalizeSql(sql: string): string {
  return sql.replace(/\s+/g, " ").trim();
}

export function stripStringLiterals(sql: string): string {
  return sql.replace(stringLiteralPattern, "''");
}

Why does this matter? Consider this perfectly valid query:

SELECT 'FORM is mentioned inside a string' AS note;

A naive validator that scans for the typo FORM would flag it as an error — a false positive. By replacing every literal with an empty '' first, keyword checks only ever see structural SQL, never user data. The regex even handles escaped quotes ('' inside single-quoted strings), which is the classic edge case that breaks homemade SQL scanners.

This is the same principle behind SQL injection defense: data and structure must never be confused. Any AI database tool — including LLM-backed ones — needs this separation, otherwise a malicious string literal could manipulate the analysis (a form of prompt injection).

The rule engine: deterministic checks, structured output

Every rule in src/validation/rules.ts follows the same contract — it either stays silent or emits a structured error object:

if (/\bFORM\b/i.test(literalSafeSql)) {
  errors.push({
    code: "TYPO_FORM",
    message: "Possible typo detected.",
    suggestion: "FROM",
    severity: "error"
  });
}

The interesting rules are the ones that need actual parsing logic, not just a regex. Two examples:

Detecting unclosed quotes with a state machine

You cannot detect an unclosed quote with a single regex, because single quotes, double quotes, escaped quotes and doubled quotes ('') all interact. The engine walks the string character by character, tracking quote state:

export function hasClosedQuotes(sql: string): boolean {
  let singleQuoteOpen = false;
  let doubleQuoteOpen = false;

  for (let index = 0; index < sql.length; index += 1) {
    const current = sql[index];
    const next = sql[index + 1];

    if (current === "'" && !doubleQuoteOpen) {
      if (next === "'") { index += 1; continue; } // escaped '' inside a literal
      singleQuoteOpen = !singleQuoteOpen;
    }
    if (current === '"' && !singleQuoteOpen) {
      doubleQuoteOpen = !doubleQuoteOpen;
    }
  }
  return !singleQuoteOpen && !doubleQuoteOpen;
}

This is a tiny state machine — the same technique real SQL lexers use, just scoped down to one job.

Guessing a missing comma without a full parser

SELECT id name email FROM users; is one of the most common beginner mistakes. The engine extracts the SELECT list and applies a heuristic: multiple bare identifiers, no commas, and no alias keywords like AS or DISTINCT that would legitimize the pattern:

function looksLikeMissingComma(sql: string): boolean {
  const match = /^\s*SELECT\s+(.+?)\s+FROM\s+/i.exec(sql);
  if (!match) return false;

  const selectList = match[1].trim();
  if (!selectList || selectList === "*" || selectList.includes(",")) return false;

  const parts = selectList.split(/\s+/).filter(Boolean);
  if (parts.length < 2) return false;

  const aliasMarkers = new Set(["AS", "DISTINCT"]);
  return !parts.some((part) => aliasMarkers.has(part.toUpperCase()));
}

Notice the design philosophy: the rule prefers staying silent over being noisy. SELECT id AS user_id FROM users passes untouched. A validator that cries wolf gets disabled within a week — precision matters more than recall for developer tools.

Dialects: the same SQL is valid and invalid at the same time

One of the most valuable things an AI database solution can teach (or enforce) is that SQL is not one language. The same pagination intent is written four different ways:

Engine	Syntax
PostgreSQL / MySQL	`SELECT * FROM users LIMIT 5;`
SQL Server	`SELECT TOP 5 * FROM users;`
Oracle	`SELECT * FROM users FETCH FIRST 5 ROWS ONLY;`
ANSI standard	`FETCH FIRST 5 ROWS ONLY`

The engine encodes these differences as engine-specific rules. For example:

if (engine === "postgresql" && /\bTOP\s+\d+\b/i.test(sql)) {
  errors.push({
    code: "ENGINE_SPECIFIC",
    message: "TOP is not valid PostgreSQL SELECT syntax.",
    suggestion: "Use LIMIT in PostgreSQL.",
    severity: "error"
  });
}

if (engine === "oracle" && /\bLIMIT\s+\d+\b/i.test(sql)) {
  errors.push({
    code: "ENGINE_SPECIFIC",
    message: "LIMIT is not valid Oracle syntax.",
    suggestion: "Use FETCH FIRST n ROWS ONLY.",
    severity: "error"
  });
}

There is a nice detail in ANSI mode: dialect-specific syntax is reported as a warning, not an error. The query might run fine on your engine — the agent is just telling you it is not portable. Severity levels turn a binary validator into a nuanced reviewer.

Governance: the read-only firewall

The agent also enforces policy. A tokenizer splits the (literal-stripped) SQL and checks every token against a denylist:

const forbiddenStatements = [
  "ALTER", "CREATE", "DELETE", "DROP", "EXEC",
  "EXECUTE", "INSERT", "MERGE", "TRUNCATE", "UPDATE"
];

const forbidden = upperTokens.find((token) => forbiddenStatements.includes(token));

Combined with the multiple-statement check, this blocks the classic piggyback attack pattern before it exists:

SELECT * FROM users; DROP TABLE users;   -- rejected: MULTIPLE_STATEMENTS + FORBIDDEN_STATEMENT

This is exactly the kind of layer you want in front of any LLM that generates SQL: even if the model hallucinates a destructive statement, the deterministic validator kills it before execution.

Real-world integration examples

1. CI/CD quality gate (GitHub Actions)

Fail the build if any .sql file in the repo has blocking errors:

- name: Validate SQL files
  run: |
    for f in $(git ls-files '*.sql'); do
      body=$(jq -n --arg q "$(cat $f)" '{engine:"postgresql", query:$q}')
      valid=$(curl -s -X POST http://localhost:3000/api/validate \
        -H "Content-Type: application/json" -d "$body" | jq '.valid')
      if [ "$valid" != "true" ]; then
        echo "❌ SQL validation failed in $f"; exit 1
      fi
    done

2. Pull request review bot

Because the API returns structured JSON (code, message, suggestion, explanation), a bot can post readable review comments with zero extra parsing:

🤖 SQL Review — migration_2026_07.sql
Issue: TOP is not valid PostgreSQL SELECT syntax.
Suggestion: Use LIMIT in PostgreSQL.

3. Tool for an LLM agent

This is where "AI database solutions" gets truly interesting. A text-to-SQL model should never self-certify its own output. Instead, register the validator as a tool:

{
  "name": "validate_sql",
  "description": "Validate a SQL statement before execution. Returns structured errors and a corrected version when possible.",
  "input_schema": {
    "type": "object",
    "properties": {
      "engine": { "enum": ["ansi", "mysql", "postgresql", "oracle", "sqlserver"] },
      "query": { "type": "string" }
    },
    "required": ["engine", "query"]
  }
}

The workflow becomes: LLM generates SQL → deterministic validator reviews it → only validated SQL is ever considered for execution. The probabilistic and deterministic layers check each other — which is the safest architecture we know today for AI + databases.

What I would improve next

A real parser (AST-based). Regex heuristics are perfect for teaching and for a first line of defense, but nested subqueries, CTEs and window functions need a genuine grammar. Libraries like node-sql-parser would be the natural next step.
Line/column positions. IDE diagnostics and PR comments become 10× more useful when they point at the exact character.
Configurable policy. INSERT should be blocked in a dashboard context but allowed in a migrations/ folder.
An optional LLM explanation layer — but always after the deterministic rules, explaining structured findings rather than inventing them.

Conclusion

The AI database conversation is dominated by generation, but validation and governance are where AI-style tooling delivers immediate, low-risk value: no credentials, no execution, deterministic and testable behavior, and output structured enough for humans, CI systems and LLMs to consume alike.

If text-to-SQL is the accelerator, a validation agent is the brake and the seatbelt. Any serious AI database solution needs both.

Repository: github.com/cs2026086510-a11y/sql-ai-validator-agent
Companion article (API + Codex Skill): Building an AI SQL Validation Agent with Codex and a REST API by Cristian Soto