Wednesday, May 14, 2008 #

SQL Injection

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.

 

posted @ Wednesday, May 14, 2008 11:14 AM | Feedback (0)

Tuesday, May 13, 2008 #

Lightning fast collapsed date ranges and missing date ranges

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.
posted @ Tuesday, May 13, 2008 4:16 PM | Feedback (0)

Wednesday, April 30, 2008 #

Index pages

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,
posted @ Wednesday, April 30, 2008 9:38 AM | Feedback (0)

Tuesday, April 22, 2008 #

SP3 for Microsoft SQL Server 2005

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

 

posted @ Tuesday, April 22, 2008 4:07 PM | Feedback (1)

Thursday, April 17, 2008 #

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

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

posted @ Thursday, April 17, 2008 10:18 PM | Feedback (0)

Friday, March 28, 2008 #

Cumulative update package 7 for SQL Server 2005 Service Pack 2

Soon available here
http://support.microsoft.com/default.aspx/kb/949095/
posted @ Friday, March 28, 2008 8:14 AM | Feedback (1)

Wednesday, March 26, 2008 #

Some XML search approaches

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

 

DECLARE       @XMLString XML,
       @Search VARCHAR(50)
 
SELECT @XMLString = '
                           <Customers>
                                  <Customer>
                                         <FirstName>Kevin</FirstName>
                                         <LastName>Goff</LastName>
                                         <City type="aca">Camp Hill</City>
                                  </Customer>
                                  <Customer>
                                         <FirstName>Steve</FirstName>
                                         <LastName>Goff</LastName>
                                         <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
 
posted @ Wednesday, March 26, 2008 11:17 AM | Feedback (0)

Friday, March 14, 2008 #

Search all code for specific keyword

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

posted @ Friday, March 14, 2008 2:05 PM | Feedback (4)

Wednesday, March 05, 2008 #

Change schema for all tables

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 ''"
posted @ Wednesday, March 05, 2008 1:31 AM | Feedback (0)

Tuesday, February 19, 2008 #

Efficient pagination for large set of data?

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

 

posted @ Tuesday, February 19, 2008 4:12 PM | Feedback (2)