# 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

```sql
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

```sql
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

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

### Safer pattern

```sql
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

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

### Safer pattern

```sql
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.

## Related pages

* [API Design and Contract Security](/architecture-api-crypto-and-identity/index/api-design-and-contract-security.md)
* [Security Quality Gates and Release Blocking](/devsecops-cicd-and-supply-chain/index-1/security-quality-gates-and-release-blocking.md)
* [Compliance-to-Engineering Evidence Pass](/metrics-audit-risk-evidence-and-compliance/index-1/compliance-to-engineering-evidence-pass.md)

***

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.product-security.expert/application-security-and-secure-sdlc/index-4/sql-vulnerability-examples-and-fixes.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.