Stored Procedures for SQL Injections

[John_Accuwebhosting]

Hello All,

Below mentioned stored procedures will help you to solve SQL injection issues.

To search for the String:
==================

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: SQL Server information @ Narayana Vyas Kondreddi's website: A good resource for all your SQL Server needs!
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

GO


================================================== ==


To replace the string:
================

CREATE PROC SearchAndReplace
(
@SearchStr nvarchar(1000),
@ReplaceStr nvarchar(1000)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi, Updated by: Blake Nall
-- Site: SQL Server information @ Narayana Vyas Kondreddi's website: A good resource for all your SQL Server needs!
-- Tested on: SQL Server 2005 not supported by 2000 or 7
-- Date modified: 7/21/2008
-- Updated by Blake of blaken.net to support ntext and text fields

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @tColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @tColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END




WHILE (@TableName IS NOT NULL) AND (@tColumnName IS NOT NULL)
BEGIN
SET @tColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('ntext', 'text')
AND QUOTENAME(COLUMN_NAME) > @tColumnName
)

IF @tColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @tColumnName
+ ' = REPLACE(cast(' + @tColumnName + ' AS NVARCHAR(Max)), '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @tColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END




END

SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurrence(s)' AS 'Outcome'
END
GO

================================================== ======

Hope this will help someone!

Thanks,

[kilter]

Re: Stored Procedures for SQL Injections

The use of stored procedures is to combat injection of ad-hoc SQL statements. And the additional suggestion of using ‘execute only’ permissions on procedures is because I want to make sure that the stored procedures that I have cannot be replaced or altered by the SQL Injection attack. I do not want an attacker using SQL Injection to read my existing code, but this is a secondary consideration. The attacks I see could usually care less what logic you have, but really want to introduce their own functionality, either by introducing an ad-hoc query or by introducing a stored procedure. Use of stored procedures mitigates the first issue, and ‘execute only’ stops the alteration of the code. I am adding that you want to periodically check that the database user does not have create procedures permissions in their role in addition to the existing stored procedures being execute only. This combats the ‘Injection’ of new stored procedures to launch additional attacks.

But database administration and database platform security is oft left out of the conversation, and it should not be, as there are other simple tips that have similar benefits to helping reduce the possibility of SQL Injection. I have made some comments to several of the OWASP & WASC members here in San Jose, and that given the symbiotic relationship between database platforms and web application, may want to include some additional database platform security discussions with the application security discussions that they have today.

My final suggestion on the topic of stored procedures has to do with external stored procedures. Most relational databases have the option of using external links and stored procedures to reference OS level code. This is in essence a program outside the database that can either run OS level functions, and often can use database functionality as well. I do not want to go into a long discussion here about the types of attacks that can be conducted through external code, or the dozens of issues pertaining to permissions, but simply comment that in using stored procedures for web applications, resist the urge to use external stored procedures or links. They can be very dangerous, and I typically advocate checking that the user rights do not include use of these external resources.