Cleaning Up SQL Injection Attack : b.js & preventing SQL Injection

Hello Friends,

My website is infected with the SQL Injection and I have try to create clean MS SQL database using the SP .
I hope this will help to you clean up your database and protect with SQL injection in your website.

Please go to the below link and create http://codegroups.com/blog/index.php/cleaning-up-sql-injection-attack-bjs/

create PROCEDURE FindReplace
 (
 @TABLE  VARCHAR(200),
 @Field  VARCHAR(200),
 @WHERE VARCHAR(100),
 @Find  VARCHAR(500),
 @REPLACE  VARCHAR(500)
 )
 AS
 DECLARE @query VARCHAR(8000)
 SET @query  = ‘UPDATE ‘ +  @TABLE +
               ‘ SET ‘ +  @Field + ‘= REPLACE(CONVERT(varchar(8000),’
               + @Field + ‘),”’ +  @Find + ”’,”’ + @REPLACE  +”’)’
 IF(@WHERE <> ”)
         SET @query = @query + ‘ WHERE ‘+@WHERE
       
 EXECUTE (@query)
 GO

Code (markup):

———-

create Procedure RemoveStringFinal
 @FIND  VARCHAR(500),
 @REPLACE  VARCHAR(500)

as

DECLARE @TABLE_NAME VARCHAR(500)
DECLARE @COLUMN_NAME VARCHAR(500)
DECLARE @DATA_TYPE VARCHAR(500)
DECLARE db_cursor CURSOR FOR
  select  TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE

WHILE @@FETCH_STATUS = 0 
BEGIN 
       if @DATA_TYPE = ‘varchar’ or @DATA_TYPE = ‘text’ or @DATA_TYPE = ‘ntext’ or @DATA_TYPE = ‘nvarchar’
          begin

     print @TABLE_NAME
            print @COLUMN_NAME
     print @DATA_TYPE
  
              EXEC FindReplace @TABLE_NAME,@COLUMN_NAME,”,@FIND,@REPLACE 

          end
      

       FETCH NEXT FROM db_cursor INTO @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE
END 

CLOSE db_cursor 
DEALLOCATE db_cursor

Code (markup):

———–

–EXEC FindReplace @TABLE_NAME,@COLUMN_NAME,”,’<script src=http://www.en-us18.com/b.js></script>’,’ ‘

Two SP created for Cleaning up SQL Injection database.

1 SP for FindReplace

2 sp - RemoveStringFinal

Run both the Query in Query Analyzer and

Exec RemoveStringFinal ‘<script src=http://www.en-us18.com/b.js></script>’,”

Enter your Attack JS url in above sp and replace with blank space ” i.e Exec RemoveStringFinal ‘<script src=http://www.en-us18.com/b.js></script>’,”

if your data have <script src=http://www.en-us18.com/b.js></script> string in your database then you run above sp with ‘Exec RemoveStringFinal ‘<script src=http://www.en-us18.com/b.js></script>’,”’

I will post soon for How to Protect our database website with SQL Injection.

SQL Injection Protection

<%

”’ Code for preventing SQL Injection

array_split_item = Array(”–”, “;”, “/*”, “*/”, “@@”, “@”,

                  “char”, “nchar”, “varchar”, “nvarchar”,

                  “alter”, “begin”, “cast”, “create”, “cursor”,

                  “declare”, “delete”, “drop”, “end”, “exec”,

                  “execute”, “fetch”, “insert”, “kill”, “open”,

                  “select”, “sys”, “sysobjects”, “syscolumns”,

                  “table”, “update”, “<script”, “</script>”, “‘”)

for each item in Request.QueryString

    for array_counter = lbound(array_split_item) to ubound(array_split_item)

       

        item_postion1 = InStr(lcase(Request(item)),array_split_item(array_counter))

        ‘Response.Write(array_split_item(array_counter) & “<br>”)

       

        if item_postion1 > 0  then

            Response.Write(”Command cannot be executed.”)

            Response.End()

        end if

    next

next

%>

Code (markup):

Add Above code in your connection file

or

Make one include file and add in all the files

C#

string[] array_split_item = new string[] { “–”, “;”, “/*”, “*/”, “@@”, “@”, “char”, “nchar”, “varchar”, “nvarchar”, “alter”, “begin”, “cast”, “create”, “cursor”, “declare”, “delete”, “drop”, “end”, “exec”, “execute”, “fetch”, “insert”, “kill”, “open”, “select”, “sys”, “sysobjects”, “syscolumns”, “table”, “update”, “<script”, “</script>”, “‘” };
        int pos = 0;
        string strReq = “”;

        foreach (string var in Request.QueryString)
        {
            foreach (string strItem in array_split_item)
            {

                strReq=Request.QueryString[var].ToString();
                pos = strReq.ToLower().IndexOf(strItem.ToLower())+1;
           
                if (pos>0)
                {
                    Response.Write(”this is very bed”);
                    Response.End();
                   
                }
            }
           
        }

Code (markup):

 

Hi Shailesh,

My database also effected with SQL injection attack.
Most of the tables are updated.

Added following script in many columns.
<script src=http://www.bn<script src=http://www.heiheinn.cn/k.js></script>

Will use your stored procedures to clear it. I look for a solution to permanently protect the database from these attacks.

 

Yes your database is clean up useing this SP.

but you also need to update your code in your website.

May i know which script language are you using ?

if you are using asp then please add below code in your conn.asp [connection file]

<%

”’ Code for preventing SQL Injection

array_split_item = Array(”–”, “;”, “/*”, “*/”, “@@”, “@”,

                  “char”, “nchar”, “varchar”, “nvarchar”,

                  “alter”, “begin”, “cast”, “create”, “cursor”,

                  “declare”, “delete”, “drop”, “end”, “exec”,

                  “execute”, “fetch”, “insert”, “kill”, “open”,

                  “select”, “sys”, “sysobjects”, “syscolumns”,

                  “table”, “update”, “<script”, “</script>”, “‘”)

for each item in Request.QueryString

    for array_counter = lbound(array_split_item) to ubound(array_split_item)

       

        item_postion1 = InStr(lcase(Request(item)),array_split_item(array_counter))

        ‘Response.Write(array_split_item(array_counter) & “<br>”)

       

        if item_postion1 > 0  then

            Response.Write(”Command cannot be executed.”)

            Response.End()

        end if

    next

next

%>

Code (markup):

 

Thanks for the code. But I can not get it to work.

When I try to run the FindReplace procedure as a new query in MS SQl Management studio Express, I get the following errors:

Msg 102, Level 15, State 1, Procedure FindReplace, Line 11
Incorrect syntax near '‘'.
Msg 102, Level 15, State 1, Procedure FindReplace, Line 14
Incorrect syntax near '”'.
Msg 102, Level 15, State 1, Procedure FindReplace, Line 15
Incorrect syntax near '‘'.

My database is a MS SQL version 9.0 SP2

 

shailesh,

I also use ASP. Will use the code in the connection file.
Actually I also get the same error message, when I run the stored procedure.

Thanks

 

Try using this:

function secure($string) {

$string = strip_tags($string);

$string = htmlspecialchars($string);

$string = trim($string);

$string = stripslashes($string);

$string = mysql_real_escape_string($string);

return $string;

}

PHP:

 

Hi,

Please find the attached file and run in your Query Analyzer.

Let me if you have any query or error.

Regards,
Shailesh

 

Set permission denied on the system tables: sysobjects and syscolumns of your db. In enterprise managers, click on the tables open "authorization" and deny averything for everyone. This should be enough to block the attack.
- Catch the queystring in each page and parse it for terms like "EXEC" or "DECLARE" (you may want to add more like "<script" and so on)m if u find those terms, redirect to nowhere.

 

I am getting the same errors in my ms sql 2005. The text file you uploded gives me a digital point error page. Thanks

 

I also get the same errors I suspect that there are some errors with the publishing of the code, when I added the check to the db connection file I had to change and remove some chars to get it to work and it didnt like the line breaks at all.


aah it was the same problem I had with the asp page, i replaced all ” and ’ with '' and '

tnx worked very nice now ill just have to wait and see if the filter works

 

I have replaced all the quotes see below. I am still getting errors in the script. This is a sql 2005 database.

create PROCEDURE FindReplace
(
@TABLE VARCHAR(200),
@Field VARCHAR(200),
@WHERE VARCHAR(100),
@Find VARCHAR(500),
@REPLACE VARCHAR(500)
)
AS
DECLARE @query VARCHAR(8000)
SET @query = 'UPDATE ' + @TABLE +
' SET ' + @Field + '= REPLACE(CONVERT(varchar(8000),'
+ @Field + '),"' + @Find + "',"' + @REPLACE +"')'
IF(@WHERE <> ")
SET @query = @query + ' WHERE '+@WHERE

EXECUTE (@query)
GO

error is as follows
Msg 156, Level 15, State 1, Procedure FindReplace, Line 15
Incorrect syntax near the keyword 'SET'. and the " are messing up the argument highlighting.

any help would be appreciated.

 

I was unable to get the SP to work either after tweaking it. I found this, a reverse of the injection code, and it appears to have worked flawlessly.

http://blogs.msdn.com/jay_akhawri/archive/2008/06/25/latest-sql-injection-of-script-components.aspx

use <hacked_table>

DECLARE @T varchar(255), @C varchar(255);
DECLARE Table_Cursor CURSOR FOR
SELECT a.name, b.name
FROM sysobjects a, syscolumns b
WHERE a.id = b.id AND a.xtype = 'u' AND
(b.xtype = 99 OR
b.xtype = 35 OR
b.xtype = 231 OR
b.xtype = 167);
OPEN Table_Cursor;
FETCH NEXT FROM Table_Cursor INTO @T, @C;
WHILE (@@FETCH_STATUS = 0) BEGIN
  EXEC(
    'update ['+@T+'] set ['+@C+'] = left(
            convert(varchar(8000), ['+@C+']),
            len(convert(varchar(8000), ['+@C+'])) - 6 -
            patindex(''%tpircs<%'',
                      reverse(convert(varchar(8000), ['+@C+'])))
            )
      where ['+@C+'] like ''%<script%</script>'''
      );
  FETCH NEXT FROM Table_Cursor INTO @T, @C;
END;

CLOSE Table_Cursor;
DEALLOCATE Table_Cursor;

Code (markup):

 

Recently one of my client get "sql injected" with this "b.js"

I successfully used this SP for MSSQL 2005 (copyright reference are inside):

--To replace all occurences of 'America' with 'USA':
EXEC SearchAndReplace 'America', 'USA'
GO


CREATE PROC SearchAndReplace
(
	@SearchStr nvarchar(100),
	@ReplaceStr 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 and replace it with another string
	-- Written by: Narayana Vyas Kondreddi
	-- Site: http://vyaskn.tripod.com
	-- Tested on: SQL Server 7.0 and SQL Server 2000
	-- Date modified: 2nd November 2002 13:50 GMT

	SET NOCOUNT ON

	DECLARE @TableName nvarchar(256), @ColumnName 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 @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	
	END

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

PHP:

It search and replace ALL the database wich could be a bit heavy depending on the size of your DB, I found it pretty effective (killed more than 80 000 bad "script" tags in 16sec)

Just to mention "ntext" doesn't work you have to convert to varchar(max).

Hope it helps.

 

trilianleo ,
Download SP and Sql Injection protection file from the below link http://www.2shared.com/file/3501087/a4ccccc7/SQL.html and file name is sql.zip. I try to upload in dp but it's not working .

 

my companies asp site has been attacked with this sql injection attack. it happened a few weeks back and i deleted all the entries in the forms, but then it happened again and again.
the thing is i'm not sure how to go about doing the sp that you mentioned. my knowledge of sql and asp is very limited and unfortuneatley im the only one here to deal with it.
the only way i can find to communicate with the database to do a find and replace is thru adovbs inc files. is this possible?
below is the code from the page with the forms that keep getting attacked. is there anyway i can protect these? i didnt create this site so i'm not sure what to do.
sorry if this is a very basic question but i'm only learning!

<%@ language=VBScript%>
<% Response.Expires=0 %>
<!-- #INCLUDE File="adovbs.inc" -->
<html>
<head>
<title>Edco : Add Product To Database</title>
<link rel="stylesheet" href="/global/global.css">
</head>

<SCRIPT LANGUAGE="JavaScript">
<!--
function checkrequired()
{
     if (!document.addproduct.product.value.length ||
     	 !document.addproduct.title.value.length)
{
		return false;
	}
	return true;
}



function checkans()
{
	if (checkrequired())
	{
   	    document.addproduct.submit();
	}
	else
	{
		alert('Please ensure you have entered all details');
	}
}


//-->
</script>



<body topmargin="0" leftmargin="0" marginwidth="0" marginheight="0">

<table cellpadding="0" cellspacing="0" marginwidht="0" marginheight="0" border="0" width="800" height="595">
<tr><td valign="top" colspan="3" width="800">
<!-- Open top Navigation Bar -->
<!--#include virtual="/global/topnav.txt"-->
<!-- Close top Navigation Bar -->
</td></tr>
<tr><td valign="top" width="168">
<!-- Open Left Navigation Bar -->
&nbsp;
<!-- Close top Navigation Bar -->
</td>
<td valign="top" width="3" background="/images/border.gif" height="100%">&nbsp;</td>
<td valign="top" width="629">
<!-- Open Content -->
<table cellpadding="0" cellspacing="0" marginwidth="0" marginheight="0" border="0">
<tr><td width="30">&nbsp;</td>
<td width="599" class="bodytext">
<table width="599" cellspacing="0" cellpadding="0" border="0">
<tr><td class="heading">&nbsp;</td></tr>
<tr><td class="heading" align="center">edco.ie - Product Administration </td></tr>
<tr><td class="heading">&nbsp;</td></tr>
</table>

<table width="599" border="0" cellspacing="0" cellpadding="0" marginwidth="0" marginheight="0" leftmargin="0" topmargin="0">

  <tr>
    <td width="50">&nbsp;</td>
    <td class="bodytext"><br>
<%
'If using a SQL Server database use the below connection string
strConn = "DSN=edcodsn;DATABASE=edco;UID=edcosql;PWD=53ur4MTS;"

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConn


 set RS = Server.CreateObject("ADODB.Recordset")

	RS.Open "SELECT subject_id, subject FROM subject ORDER BY subject", conn

%>

Code (markup):

any help would be really appreciated

 

Hi shaileshk

Where should the C# code for SQL Injection Protection be placed? I have the project code but I am not too familiar with C# and asp.net.

Please if you could give a little more detail about how to use the code in a C# project.

 

hi Godi,

You can add in your page load event in master page
if you are not using master page then you can add this script in all the page load event.

You also use httphandler for this.if you dont know http Handler then use above techniques.

Let me know if you have any query.

 

Hi,

Please save below code in sql.asp

<%

”’ Code for preventing SQL Injection

array_split_item = Array(”–”, “;”, “/*”, “*/”, “@@”, “@”,

                  “char”, “nchar”, “varchar”, “nvarchar”,

                  “alter”, “begin”, “cast”, “create”, “cursor”,

                  “declare”, “delete”, “drop”, “end”, “exec”,

                  “execute”, “fetch”, “insert”, “kill”, “open”,

                  “select”, “sys”, “sysobjects”, “syscolumns”,

                  “table”, “update”, “<script”, “</script>”, “‘”)

for each item in Request.QueryString

    for array_counter = lbound(array_split_item) to ubound(array_split_item)

       

        item_postion1 = InStr(lcase(Request(item)),array_split_item(array_counter))

        ‘Response.Write(array_split_item(array_counter) & “<br>”)

       

        if item_postion1 > 0  then

            Response.Write(”Command cannot be executed.”)

            Response.End()

        end if

    next

next

%>

Code (markup):

Include this file in your all pages

<!--#Include file=sql.asp--> in first line of the all pages

you can find same file from the above posting link.

 

I do understand how huge this subject is

Shit - if I understand what is involved, I will be the first to detail a step by step for the audience that needs it. Currently the information all over the net that I have found and read is documented for "HACKER".

THE FIRST PERSON THAT CAN CONVEY AN ARTICULATE STEP BY STEP. will get the most traffic



Example (Do I have to)
ENTER my hosting CPANEL
Access phpMyAdmin...unclear of the rest.
upon further understand "my god" .... I will make this simple...


NOT available is a step by step. A document that outlines each step involved to clean and correct any XSS or SQL injection issues. I am hoping some members on this site can or will be able to convey clear directions to help with the cleaning up of a database and protect from SQL injection in a website.

 

THANKS ...rep added