Yet another SQL Server enthusiast

2014-02-10

How to avoid SQL injections: one simple solution

Filed under: Uncategorized — ---- @ 8:30 AM

SQL injections are one of the top threats for web applications. This kind of attack is possible if application concatenates and executes strings received from the end-users. This article shows a simple technique to avoid this attacks: [at database level] instead of receiving (and concatenating) strings (for example column’s or table’s name) from the client app it receives only integers. Every column will have an INT identifier associated.

Example:

If the column Name included into Person.PhoneNumberType could have the ID 4

Code Snippet
CREATE TABLE dbo.AllowedTable (
    IDAllowedTable INT PRIMARY KEY,
    TableSchema SYSNAME NOT NULL,
    TableName SYSNAME NOT NULL,
        UNIQUE (TableSchema,TableName),
    Caption NVARCHAR(50) NOT NULL – This is what end-user will see
);
INSERT    INTO dbo.AllowedTable
        (IDAllowedTable, TableSchema, TableName, Caption)
VALUES    (1, N'Person', N'AddressType', 'Type of address'),
        (2, N'Person', N'PhoneNumberType', 'Type of phone number');

CREATE TABLE dbo.AllowedColumn (
    IDAllowedColumn INT PRIMARY KEY, – This is what client-all will send to SQL Server
    IDAllowedTable INT NOT NULL REFERENCES dbo.AllowedTable(IDAllowedTable),
    ColumnName SYSNAME NOT NULL,
        UNIQUE(IDAllowedTable, ColumnName),
    IsPKColumn BIT NOT NULL,
    Caption NVARCHAR(50) NOT NULL – This is what end-user will see
);
– Every table has a simple PK (single column)
CREATE UNIQUE INDEX IUF_AllowedColumn_IDAllowedTable_WhereIsPKColumnIs1
ON dbo.AllowedColumn(IDAllowedTable)
WHERE IsPKColumn = 1;
GO
INSERT    INTO dbo.AllowedColumn
        (IDAllowedColumn, IDAllowedTable, ColumnName, IsPKColumn, Caption)
VALUES    (1, 1, N'AddressTypeID', 1, 'ID'),
        (2, 1, N'Name', 0, 'Name'),
        (3, 2, N'PhoneNumberTypeID', 1, 'ID'),
        (4, 2, N'Name', 0, 'Name'),
        (5, 2, N'ModifiedDate', 0, 'The last modification date');
GO

then if the client app needs a list of all values from this column it could execute a stored procedure having an INT parameter instead of [N]VARCHAR parameter:

Code Snippet
EXEC dbo.ExecuteDynamicQuery @IDColumn = 4;

Code Snippet
CREATE PROCEDURE dbo.ExecuteDynamicQuery
(
    @IDColumn INT – Client app will send only the ID of object (the id of column in this example)
)
AS
BEGIN
    DECLARE    @IDTable INT, @SecondColumnName SYSNAME;
    
    SELECT    @IDTable    = ac.IDAllowedTable,
            @SecondColumnName = ac.ColumnName
    FROM    dbo.AllowedColumn ac
    WHERE    ac.IDAllowedColumn = @IDColumn;

    IF @IDTable IS NULL – This will check if the source table exists
        RAISERROR('Error', 16, 1); – Object not found
    ELSE
    BEGIN
        DECLARE  @PkColumnName SYSNAME;
        SELECT    @PkColumnName = ac.ColumnName
        FROM    dbo.AllowedColumn ac
        WHERE    ac.IDAllowedTable = @IDTable
        AND        ac.IsPKColumn = 1;

        DECLARE @SqlStatement NVARCHAR(MAX);
        SELECT    @SqlStatement =
                N'SELECT ' + QUOTENAME(@PkColumnName)
                + N' , ' + QUOTENAME(@SecondColumnName)
                + N' FROM '  + QUOTENAME(at.tableSchema) + N'.' + QUOTENAME(at.TableName)
        FROM    dbo.AllowedTable at
        WHERE    at.IDAllowedTable = @IDTable;

        PRINT @SqlStatement;

        EXEC sp_executesql @SqlStatement;
    END
END;
GO

First, this stored procedure will validate the value of parameter (the column’s ID: @IDColumn INT) and then if the requested ID exists will generate and execute a SQL statement.

Usage:

Code Snippet
EXEC dbo.ExecuteDynamicQuery @IDColumn = 100;
/*
Msg 50000, Level 16, State 1, Procedure ExecuteDynamicQuery, Line 16
Error
*/
EXEC dbo.ExecuteDynamicQuery @IDColumn = NULL;
/*
Msg 50000, Level 16, State 1, Procedure ExecuteDynamicQuery, Line 16
Error
*/
EXEC dbo.ExecuteDynamicQuery @IDColumn = 2;
/*
Executed @SqlStatement:
SELECT [AddressTypeID] , [Name] FROM [Person].[AddressType]
*/
EXEC dbo.ExecuteDynamicQuery @IDColumn = 4;
/*
Executed @SqlStatement:
SELECT [PhoneNumberTypeID] , [Name] FROM [Person].[PhoneNumberType]
*/

Note: Microsoft has published a comprehensive article on this subject here .

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: