SQL Injection Prevention for SQL Server Developers: A Practical, Hands-On Guide

-- ❌ VULNERABLE: Never build queries this way
-- Imagine @UserInput comes directly from a web form

DECLARE @UserInput NVARCHAR(100);
SET @UserInput = 'Tailspin Toys';  -- Normal, harmless input

-- The application builds this string by concatenation:
DECLARE @Query NVARCHAR(MAX);
SET @Query = 'SELECT CustomerID, CustomerName, PhoneNumber '
           + 'FROM Sales.Customers '
           + 'WHERE CustomerName = ''' + @UserInput + '''';

EXEC (@Query);
-- This works fine... but watch what happens next.

-- ☠️ CATASTROPHIC: What the database actually executes
SELECT CustomerID, CustomerName, PhoneNumber 
FROM Sales.Customers 
WHERE CustomerName = ''; DROP TABLE Sales.Orders; --'

-- Breaking this down:
-- 1. WHERE CustomerName = ''        → Empty string, returns nothing
-- 2. ;                              → Ends the first statement
-- 3. DROP TABLE Sales.Orders;       → DESTROYS the Orders table
-- 4. --                             → Comments out the trailing quote

-- ✅ SAFE: Stored procedure with parameterized input
CREATE PROCEDURE Sales.usp_SearchCustomerByName
    @CustomerName NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        CustomerID,
        CustomerName,
        PhoneNumber,
        DeliveryAddressLine1,
        DeliveryPostalCode
    FROM Sales.Customers
    WHERE CustomerName LIKE '%' + @CustomerName + '%';
END;
GO

-- Calling it — the attacker's payload does NOTHING harmful
EXEC Sales.usp_SearchCustomerByName 
    @CustomerName = N''; DROP TABLE Sales.Orders; --';

-- Result: SQL Server searches for a customer literally named
-- "'; DROP TABLE Sales.Orders; --" — finds nothing, returns no rows.
-- The Orders table remains completely untouched.

-- ✅ SAFE: Multi-parameter stored procedure for order search
CREATE PROCEDURE Sales.usp_SearchOrders
    @CustomerID    INT            = NULL,
    @StartDate     DATE           = NULL,
    @EndDate       DATE           = NULL,
    @MinOrderTotal DECIMAL(18,2)  = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        o.OrderID,
        o.CustomerID,
        c.CustomerName,
        o.OrderDate,
        o.ExpectedDeliveryDate,
        SUM(ol.Quantity * ol.UnitPrice) AS OrderTotal
    FROM Sales.Orders o
    INNER JOIN Sales.Customers c  ON o.CustomerID = c.CustomerID
    INNER JOIN Sales.OrderLines ol ON o.OrderID   = ol.OrderID
    WHERE 
        (@CustomerID    IS NULL OR o.CustomerID             = @CustomerID)
        AND (@StartDate IS NULL OR o.OrderDate              >= @StartDate)
        AND (@EndDate   IS NULL OR o.OrderDate              <= @EndDate)
    GROUP BY 
        o.OrderID, o.CustomerID, c.CustomerName, 
        o.OrderDate, o.ExpectedDeliveryDate
    HAVING 
        (@MinOrderTotal IS NULL 
         OR SUM(ol.Quantity * ol.UnitPrice) >= @MinOrderTotal)
    ORDER BY o.OrderDate DESC;
END;
GO

-- Safe usage — all values are bound as parameters
EXEC Sales.usp_SearchOrders 
    @CustomerID    = 85,
    @StartDate     = '2016-01-01',
    @EndDate       = '2016-12-31',
    @MinOrderTotal = 500.00;

-- ✅ SAFE: Dynamic SQL with sp_executesql parameterization
DECLARE @CustomerName NVARCHAR(100) = N'Tailspin Toys';  -- User input
DECLARE @SqlStatement NVARCHAR(MAX);
DECLARE @ParameterDef  NVARCHAR(MAX);

-- Build the query structure (no user VALUE concatenated here)
SET @SqlStatement = N'
    SELECT 
        CustomerID, 
        CustomerName, 
        PhoneNumber
    FROM Sales.Customers
    WHERE CustomerName LIKE N''%'' + @CustName + N''%''';

-- Define parameter types
SET @ParameterDef = N'@CustName NVARCHAR(100)';

-- Execute — the value is passed SEPARATELY, never concatenated
EXEC sp_executesql 
    @stmt      = @SqlStatement,
    @params    = @ParameterDef,
    @CustName  = @CustomerName;   -- Value bound here, not in the string

-- ✅ SAFE: Dynamic sort column with allowlist validation
CREATE PROCEDURE Sales.usp_GetCustomersSorted
    @SortColumn NVARCHAR(50) = 'CustomerName',
    @SortOrder  NVARCHAR(4)  = 'ASC'
AS
BEGIN
    SET NOCOUNT ON;

    -- Step 1: ALLOWLIST validation — only known-safe columns permitted
    IF @SortColumn NOT IN ('CustomerID', 'CustomerName', 'PhoneNumber', 'PostalCode')
    BEGIN
        RAISERROR('Invalid sort column specified.', 16, 1);
        RETURN -1;
    END;

    -- Step 2: Validate sort order strictly
    IF @SortOrder NOT IN ('ASC', 'DESC')
    BEGIN
        RAISERROR('Sort order must be ASC or DESC.', 16, 1);
        RETURN -1;
    END;

    -- Step 3: Build dynamic SQL with VALIDATED structural elements only
    DECLARE @Sql NVARCHAR(MAX);
    SET @Sql = N'
        SELECT CustomerID, CustomerName, PhoneNumber, DeliveryPostalCode
        FROM Sales.Customers
        ORDER BY ' + @SortColumn + N' ' + @SortOrder;
        -- ^^^^^ Safe: these values passed allowlist validation above

    EXEC sp_executesql @Sql;
END;
GO

-- ✅ SAFE: Stored procedure with strict input validation
CREATE PROCEDURE Purchasing.usp_GetSupplierOrders
    @SupplierID INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate: SupplierID must be a positive integer
    -- (The INT data type already blocks string injection attempts,
    --  but explicit validation adds clarity and custom error handling)
    IF @SupplierID IS NULL OR @SupplierID <= 0
    BEGIN
        RAISERROR('SupplierID must be a positive integer.', 16, 1);
        RETURN -1;
    END;

    -- Validate: Confirm the supplier actually exists
    IF NOT EXISTS (SELECT 1 FROM Purchasing.Suppliers WHERE SupplierID = @SupplierID)
    BEGIN
        RAISERROR('Supplier not found.', 16, 1);
        RETURN -1;
    END;

    -- Safe query execution
    SELECT 
        po.PurchaseOrderID,
        s.SupplierName,
        po.OrderDate,
        po.ExpectedDeliveryDate,
        po.IsOrderComplete
    FROM Purchasing.PurchaseOrders po
    INNER JOIN Purchasing.Suppliers s ON po.SupplierID = s.SupplierID
    WHERE po.SupplierID = @SupplierID
    ORDER BY po.OrderDate DESC;
END;
GO

-- ✅ SECURITY SETUP: Create a restricted application login

-- Create a dedicated login for your application
CREATE LOGIN [WWI_WebApp] WITH PASSWORD = 'StrongPassword123!';

-- Create a database user mapped to that login
USE WideWorldImporters;
CREATE USER [WWI_WebApp] FOR LOGIN [WWI_WebApp];

-- Grant ONLY the permissions your app actually needs
-- Read access to customer and order data
GRANT SELECT ON Sales.Customers     TO [WWI_WebApp];
GRANT SELECT ON Sales.Orders        TO [WWI_WebApp];
GRANT SELECT ON Sales.OrderLines    TO [WWI_WebApp];

-- Execute permission on specific stored procedures only
GRANT EXECUTE ON Sales.usp_SearchCustomerByName TO [WWI_WebApp];
GRANT EXECUTE ON Sales.usp_SearchOrders         TO [WWI_WebApp];

-- Explicitly DENY dangerous permissions
DENY ALTER  ON DATABASE :: WideWorldImporters TO [WWI_WebApp];
DENY DROP   ON DATABASE :: WideWorldImporters TO [WWI_WebApp];

-- ✅ SAFE: Wrap logic in error handling that hides internals
CREATE PROCEDURE Sales.usp_GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        -- Validate input
        IF @CustomerID IS NULL OR @CustomerID <= 0
            RAISERROR('Invalid customer identifier.', 16, 1);

        -- Business logic
        SELECT 
            o.OrderID,
            o.OrderDate,
            o.ExpectedDeliveryDate,
            ol.StockItemID,
            si.StockItemName,
            ol.Quantity,
            ol.UnitPrice
        FROM Sales.Orders o
        INNER JOIN Sales.OrderLines ol    ON o.OrderID     = ol.OrderID
        INNER JOIN Warehouse.StockItems si ON ol.StockItemID = si.StockItemID
        WHERE o.CustomerID = @CustomerID
        ORDER BY o.OrderDate DESC;

    END TRY
    BEGIN CATCH
        -- Log the full error internally (for DBA/developer review)
        -- In production, write to an audit table or use a logging framework
        INSERT INTO dbo.ErrorLog (ErrorMessage, ErrorLine, ErrorProc, ErrorTime)
        VALUES (ERROR_MESSAGE(), ERROR_LINE(), ERROR_PROCEDURE(), GETDATE());

        -- Return a GENERIC message to the caller — never internal details
        RAISERROR('An error occurred while processing your request. '
                  'Please contact support if the problem persists.', 16, 1);
    END CATCH
END;
GO

-- ❌ VULNERABLE: Concatenation INSIDE a stored procedure
CREATE PROCEDURE Sales.usp_BadSearch_NEVERDOTHIS
    @Name NVARCHAR(100)
AS
BEGIN
    DECLARE @Sql NVARCHAR(MAX);
    -- This defeats the purpose of using a stored procedure entirely
    SET @Sql = 'SELECT * FROM Sales.Customers WHERE CustomerName = ''' 
               + @Name + '''';
    EXEC (@Sql);  -- ← No parameterization, fully vulnerable
END;

-- ✅ SAFE: Direct parameterized query in stored procedure
CREATE PROCEDURE Sales.usp_GoodSearch
    @Name NVARCHAR(100)
AS
BEGIN
    SELECT CustomerID, CustomerName 
    FROM Sales.Customers 
    WHERE CustomerName LIKE '%' + @Name + '%';
    -- @Name is a parameter here — SQL Server handles it safely
END;

-- ❌ VULNERABLE: EXEC with concatenated string
DECLARE @Sql NVARCHAR(MAX);
SET @Sql = 'SELECT * FROM Sales.Orders WHERE CustomerID = ' + CAST(@Input AS NVARCHAR);
EXEC (@Sql);  -- No parameterization possible

-- ✅ SAFE: sp_executesql with proper parameter binding
SET @Sql = N'SELECT * FROM Sales.Orders WHERE CustomerID = @CustID';
EXEC sp_executesql @Sql, N'@CustID INT', @CustID = @Input;

-- ❌ VULNERABLE: Using stored data to build a new query (second-order injection)
-- Imagine SupplierName was previously manipulated via injection
DECLARE @SupplierName NVARCHAR(100);
SELECT TOP 1 @SupplierName = SupplierName FROM Purchasing.Suppliers WHERE SupplierID = 1;

DECLARE @Sql NVARCHAR(MAX);
SET @Sql = 'SELECT * FROM Purchasing.PurchaseOrders WHERE SupplierName = ''' 
           + @SupplierName + '''';
EXEC (@Sql);  -- If SupplierName contains injected SQL, it executes here

-- ✅ SAFE: Always parameterize, even with internal data
SET @Sql = N'SELECT * FROM Purchasing.PurchaseOrders WHERE SupplierName = @SName';
EXEC sp_executesql @Sql, N'@SName NVARCHAR(100)', @SName = @SupplierName;