PS Product SecurityKnowledge Base

🗄️ SQL Vulnerability Examples and Fixes

Use this page when: reviewing stored procedures, report queries, database-side filtering, and privilege design for the application role. SQL mistakes often look small, but they become systemic because many services reuse them.

How to read these examples

  • Vulnerable snippet shows the unsafe habit.
  • Safer pattern shows the direction you want in production code.
  • Why it matters ties the defect to attacker value and business impact.
  • Review cue is phrased so it can become a pull-request comment or checklist item.

Example 1 — Dynamic SQL injection in stored procedure

Vulnerable snippet

CREATE PROCEDURE dbo.SearchUsers
    @email nvarchar(255)
AS
BEGIN
    DECLARE @sql nvarchar(max) =
        N'SELECT id, role FROM dbo.Users WHERE email = ''' + @email + N'''';
    EXEC(@sql);
END

Safer pattern

CREATE PROCEDURE dbo.SearchUsers
    @email nvarchar(255)
AS
BEGIN
    DECLARE @sql nvarchar(max) =
        N'SELECT id, role FROM dbo.Users WHERE email = @email';
    EXEC sp_executesql @sql, N'@email nvarchar(255)', @email = @email;
END

Why it matters

  • Dynamic SQL built from user input turns the stored procedure into a second injection surface even if the application layer looks clean.

Business impact

  • Bulk data disclosure, destructive queries, and bypass of assumptions made by calling services.

Review cue

  • When dynamic SQL is unavoidable, parameterize values and allowlist non-value fragments such as sort columns.

Example 2 — Missing tenant or owner filter in reporting query

Vulnerable snippet

CREATE VIEW dbo.InvoiceExport AS
SELECT id, tenant_id, owner_user_id, total, status
FROM dbo.Invoices;

Safer pattern

CREATE PROCEDURE dbo.GetInvoicesForUser
    @tenant_id int,
    @owner_user_id int
AS
BEGIN
    SELECT id, total, status
    FROM dbo.Invoices
    WHERE tenant_id = @tenant_id
      AND owner_user_id = @owner_user_id;
END

Why it matters

  • A technically correct query can still be a security defect if it ignores business scope such as tenant, customer, or owner.

Business impact

  • Cross-tenant leakage, privacy incidents, and major contractual or regulatory fallout.

Review cue

  • Treat row scope as a security control. Reporting and export SQL must encode tenant and ownership rules explicitly.

Example 3 — Over-privileged application role

Vulnerable snippet

CREATE LOGIN app_user WITH PASSWORD = 'StrongPassword!';
EXEC sp_addrolemember 'db_owner', 'app_user';

Safer pattern

CREATE USER app_user FOR LOGIN app_user;
GRANT SELECT, INSERT, UPDATE ON SCHEMA::app TO app_user;
GRANT EXECUTE ON dbo.GetInvoicesForUser TO app_user;
DENY ALTER, CONTROL, TAKE OWNERSHIP TO app_user;

Why it matters

  • An injection or logic flaw becomes catastrophic when the application role can alter schema, users, or security settings.

Business impact

  • Full database compromise, destructive tampering, hard-to-recover outages, and evidence integrity loss.

Review cue

  • Design DB roles for least privilege. Review app-role grants whenever an injection or authz finding appears.

Author attribution: Ivan Piskunov, 2026 - Educational and defensive-engineering use.