Thinking outside the box

SQL Injection

2008-05-14T11:14:4702:00:00

Every now and then I see sites where commands are concatenated and sent to database server.
The author must really trust the user inputs!

For every system built this way, you can expect at least one attack with SQL injection. In some cases you might not be aware of the attack, and sometimes you are aware.

Here is an example of a "friendly" attack, that just promotes a site and when you click the link you execute a javascript who knows do what?

In this link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102737
and this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101673

there are examples of SQL injection attacks.

As I wrote in the first topic, "What if the attack could have encrypted all columns!".
That would be easy spotted in front-end application.

But what if the attack had scrambled all date columns? How long time would it then take to discover the SQL Injection attack?

I hope this learns all newbies, noobs and beginners to NEVER EVER concatenate string to send to database.
Always use parametrized queries as a first line of defence.

Lightning fast collapsed date ranges and missing date ranges

2008-05-13T16:16:4102:00:00

The last two days I have been involved in a rather interesting discussion.

The original poster wanted a fast way to get missing date ranges in a series of date pairs.
Naturally I posted the link to the Script Library topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88422
Traditional T-SQL proved to be very inefficient. Even when using the CTE approch which proved to be second fastest and still 100 to 1,600 times slower!

I started out with creating 1,000 date pairs with following code

-- Prepare sample data
CREATE TABLE #ProcessCellAllocation
              (
                     AllocationID INT IDENTITY(1, 1) NOT NULL,
                     ProcessCell VARCHAR(50) NOT NULL,
                     DateFrom DATETIME NOT NULL,
                     DateTo DATETIME,
                     Seq INT
              )

INSERT        #ProcessCellAllocation
                (
                     ProcessCell,
                     DateFrom
                )
SELECT        TOP 10000
              CHAR(65 + ABS(CHECKSUM(NEWID())) % 26),
              25000 + ABS(CHECKSUM(NEWID())) % 25000
FROM          syscolumns AS c1
CROSS JOIN    syscolumns AS c2

UPDATE #ProcessCellAllocation
SET    DateTo = DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 30, DateFrom)

CREATE CLUSTERED INDEX IX_DateFrom ON #ProcessCellAllocation (ProcessCell, DateFrom, DateTo) WITH FILLFACTOR = 95

The various techniques used to produce the wanted results were very inefficient.
Ultimately I come up with this idea, which produced both collapsed date ranges and missing date ranges.

I started out with clearing a preexisting Seq columns. I can clear all records or only a certain range of ProcessCells.

UPDATE #ProcessCellAllocation

SET Seq = NULL

WHERE ProcessCell BETWEEN 'A' AND 'F'

I then initialize some variables to speed up counting

DECLARE @Seq INT,

@ProcessCell VARCHAR(50),

@DateFrom DATETIME,

@DateTo DATETIME

SELECT TOP 1

@Seq = 0,

@ProcessCell = ProcessCell,

@DateFrom = DateFrom,

@DateTo = DateTo

FROM #ProcessCellAllocation

ORDER BY ProcessCell,

DateFrom

This is only for initializing the documented trick I use, the Clustered Index Update.
The code used for the actual update looks like this

UPDATE #ProcessCellAllocation

SET @Seq = CASE

WHEN DateFrom > @DateTo THEN @Seq + 1

WHEN ProcessCell > @ProcessCell THEN @Seq + 1

ELSE @Seq

END,

@DateFrom = CASE

WHEN DateTo > @DateTo THEN DateFrom

WHEN ProcessCell > @ProcessCell THEN DateFrom

ELSE @DateFrom

END,

@DateTo = CASE

WHEN DateTo > @DateTo THEN DateTo

WHEN ProcessCell > @ProcessCell THEN DateTo

ELSE @DateTo

END,

Seq = @Seq,

@ProcessCell = ProcessCell

Now all work is done, and we can either show the collapgsed date ranges with this

-- Get the collapsed date ranges

SELECT ProcessCell,

MIN(DateFrom) AS DateFrom,

MAX(DateTo) AS DateTo

FROM #ProcessCellAllocation

GROUP BY ProcessCell,

Seq

ORDER BY Seq

Or we can get the missing date ranges with this

-- Get the missing date ranges

SELECT a.ProcessCell,

a.DateFrom,

b.DateTo

FROM (

SELECT ProcessCell,

Seq,

MAX(DateTo) AS DateFrom

FROM #ProcessCellAllocation

GROUP BY ProcessCell,

Seq

) AS a

INNER JOIN (

SELECT ProcessCell,

Seq,

MIN(DateFrom) AS DateTo

FROM #ProcessCellAllocation

GROUP BY ProcessCell,

Seq

) AS b ON b.ProcessCell = a.ProcessCell

WHERE a.Seq = b.Seq - 1

ORDER BY a.Seq

This technique is very fast!

For 1,000 date pairs the algorithm runs in 80 ms.
For 10,000 date pairs the algorithm runs in 360 ms.
For 100,000 date pairs the algorithm runs in 900 ms.
For 1,000,000 date pairs the algorithm runs in 2250 ms.

Index pages

2008-04-30T09:38:3302:00:00

SQL Server 2005 introduced the new DMV views. They are great and a real improvement to debug and optimize queries.

I find sys.dm_db_index_physical_stats very useful and often write this type of code

page_count

FROM sys.dm_db_index_physical_stats(DB_ID('MyDB'), OBJECT_ID('MyTable'), NULL, NULL, NULL)

to find out if the query optimizer has choosen the "right" index for the query.

This can be done in SQL Server 2000 too!

Use the DBCC SHOWCONTIG command. Maybe most of you have only used this with tables too see table fragmentation?

Well, you can use it for indexes too.

Use

DBCC SHOWCONTIG ('MyDB..MyTable') WITH ALL_INDEXES, TABLERESULTS

SELECT index_id,

SP3 for Microsoft SQL Server 2005

2008-04-22T16:07:2802:00:00

http://blogs.technet.com/dataplatforminsider/archive/2008/04/15/sql-server-2005-sp3-coming-soon.aspx

Cumulative update package 7 for SQL Server 2005 Service Pack 2 available now

2008-04-17T22:18:3402:00:00

Find it here here
http://support.microsoft.com/default.aspx/kb/949095/

Cumulative update package 7 for SQL Server 2005 Service Pack 2

2008-03-28T08:14:5101:00:00

Soon available here
http://support.microsoft.com/default.aspx/kb/949095/

Some XML search approaches

2008-03-26T11:17:2901:00:00

I just played around with some different techniques to fetch relevant data from XML content.

DECLARE @XMLString XML,

@Search VARCHAR(50)

SELECT @XMLString = '

<FirstName>Kevin</FirstName>

</Customer>

<FirstName>Steve</FirstName>

<City type="acb">Philadelphia</City>

</Customer>

</Customers>',

@Search = 'Camp Hill'

-- Get all customers living in Camp Hill

SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,

cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName,

cust.coldef.value('City[1]','VARCHAR(20)') AS City,

cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ

FROM @XMLString.nodes('/Customers/Customer') AS cust(coldef)

WHERE cust.coldef.exist('City/text()[.= sql:variable("@Search")]') = 1

-- Get all customers living in a City containing the text "adel"

SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,

cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName,

cust.coldef.value('City[1]','VARCHAR(20)') AS City,

cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ

FROM @XMLString.nodes('/Customers/Customer') AS cust(coldef)

WHERE cust.coldef.exist('City [contains(.,"adel")]') = 1

-- Get all customers living in a City of type "acb"

SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,

cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName,

cust.coldef.value('City[1]','VARCHAR(20)') AS City,

cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ

FROM @XMLString.nodes('/Customers/Customer') AS cust(coldef)

WHERE cust.coldef.exist('City[@type="acb"]') = 1

-- Get all customers living in a City with a Type containing an "c"

SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,

cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName,

cust.coldef.value('City[1]','VARCHAR(20)') AS City,

cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ

FROM @XMLString.nodes('/Customers/Customer') AS cust(coldef)

WHERE cust.coldef.exist('City [contains(@type, "c")]') = 1

Search all code for specific keyword

2008-03-14T14:05:0901:00:00

This is an updated version for SQL 2005 and later to search all code for a specific keyword

SELECT p.RoutineName,
'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec]
FROM (
SELECT OBJECT_NAME(so.ID) AS RoutineName,
(SELECT TOP 100 PERCENT '' + sc.TEXT FROM SYSCOMMENTS AS sc WHERE sc.ID = so.ID ORDER BY sc.COLID FOR XML PATH('')) AS Body
FROM SYSOBJECTS AS so
WHERE so.TYPE IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'V', 'X')
) AS p
WHERE p.Body LIKE '%YourKeyWordHere%'

The types are

C = CHECK constraint
D = Default or DEFAULT constraint
FN = Scalar function
IF = In-lined table-function
P = Stored procedure
R = Rule
RF = Replication filter stored procedure
TF = Table function
TR = Trigger
V = View
X = Extended stored procedure

Change schema for all tables

2008-03-05T01:31:2701:00:00

I just helped a guy here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98346 with schema
and thought that someone could benefit from this code

exec

sp_MSforeachtable "PRINT '? modify'; ALTER SCHEMA new_schema TRANSFER ?; IF @@ERROR = 0 PRINT '? modified'; PRINT ''"

Efficient pagination for large set of data?

2008-02-19T16:12:2001:00:00

This is what I pondered about today. Maybe I also will have some time to test it.

CREATE PROCEDURE dbo.uspPaginate
(
@PageNumber INT,
@RecordsPerPage TINYINT = 50
)
AS

SET NOCOUNT ON

DECLARE @MaxRows INT

SET @MaxRows = @PageNumber * @RecordsPerPage

SELECT SomeColumns
FROM (
SELECT TOP (@RecordsPerPage)
SomeColumns
FROM (
SELECT TOP (@MaxRows)
SomeColumns
FROM YourTable
ORDER BY SomeCase ASC/DESC
)
ORDER BY SomeCase DESC/ASC
)
ORDER BY SomeCase ASC/DESC

Topic is here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97550