|
|
Suppose that we execute on the server side SQL string that
came from the client (for example input from ASP page):
USE [colombo]
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[sp_myquery_exec]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_myquery_exec]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ==============================================================
Author: [ amper ]
Create date: [ 2007-02-25 ]
Description: [run select statement composed on the client side]
================================================================ */
CREATE PROCEDURE dbo.sp_myquery_exec @strSQL nvarchar(250)
AS
BEGIN
/* check recived string for possible sql injection */
IF LOWER(@strSQL) LIKE LOWER(N'%EXEC%')
OR LOWER(@strSQL) LIKE LOWER(N'%INSERT%')
OR LOWER(@strSQL) LIKE LOWER(N'%UPDATE%')
OR LOWER(@strSQL) LIKE LOWER(N'%DELETE%')
OR LOWER(@strSQL) LIKE LOWER(N'%TRUNCATE%')
OR LOWER(@strSQL) LIKE LOWER(N'%DROP%')
OR LOWER(@strSQL) LIKE LOWER(N'%ALTER%')
OR LOWER(@strSQL) LIKE LOWER(N'%GRANT%')
OR LOWER(@strSQL) LIKE LOWER(N'%REVOKE%')
OR LOWER(@strSQL) LIKE LOWER(N'%ADMIN%')
OR LOWER(@strSQL) LIKE LOWER(N'%USERS%')
OR LOWER(@strSQL) LIKE LOWER(N'%--%')
OR LOWER(@strSQL) LIKE LOWER(N'%/*%*/%')
OR LOWER(@strSQL) LIKE LOWER(N'%;%')
BEGIN
RAISERROR('command text is suspect for SQL Injection
attempt',16,1);
RETURN;
END
EXEC sp_executesql @strSQL;
END
GO
GRANT EXECUTE ON OBJECT::[dbo].[sp_myquery_exec] TO george;
GO
|
Execution of:
|
EXEC sp_myquery_exec
'select TOP 3 empno, fact, createdate FROM EmployeeFacts';
|
Will return results like this:
| empno | fact | createdate |
| 5 | maggi wants to go to vacation... | 18/04/99 |
| 6 | in April voich was late to work... | 24/05/99 |
| 10 | aiva was a best sales person... | 15/01/00 |
While Execution of:
|
EXEC sp_myquery_exec
'DELETE FROM EmployeeFacts';
|
Will return:
|
Msg 50000, Level 16, State 1, Procedure sp_myquery_exec, Line 22
command text is suspect for SQL Injection attempt
|
|