Announcement

Collapse
No announcement yet.

Stored Procedures for SQL Injections

Collapse

Ads on showthread pages

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Stored Procedures for SQL Injections

    WordPress Hosting on SSD Servers
    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,

  • #2
    ASP.Net Hosting starts @ $3.18/Mo.
    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.

    Comment

    Working...
    X
    Sign up for a News Letter Click here to sign up