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
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:
(
@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:
/*
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 .
Leave a Reply