ASP : Capturing SQL Injection attempts

if instr(Request.ServerVariables("QUERY_STRING"),"'") <> 0 THEN response.redirect "injectionattempt.asp?" & Request.ServerVariables("QUERY_STRING")
if instr(Request.ServerVariables("QUERY_STRING"),";") <> 0 THEN response.redirect "injectionattempt.asp?" & Request.ServerVariables("QUERY_STRING")
if instr(Request.ServerVariables("QUERY_STRING"),",") <> 0 THEN response.redirect "injectionattempt.asp?" & Request.ServerVariables("QUERY_STRING")

I use these lines at the top of pages that pass variables via the query string (the url itself). They search for ' , and ; which are characters which could break a sql query and enable someone to add a command into the sql being executed.

By calling injectionattempt.asp in this way I can capture the event and send myself an email letting me know this has occurred.

Determine SQL Authentication Method (2005)

Query to determine authentication method in use between client & server -

SELECT AUTH_SCHEME
FROM   SYS.DM_EXEC_CONNECTIONS
WHERE  SESSION_ID = @@SPID;

Possible return values are -

Login Type	Authentication Scheme
SQL	SQL
Windows
NTLM KERBEROS DIGEST BASIC NEGOTIATE

SQL 2005 : RANK() vs DENSE_RANK()

Using AdventureWorks to demonstrate RANK() vs DENSE_RANK() >

RANK() Skips line numbers after a tie in rank eg;
in these results 5, 9, 10, 11, 12 & 15 are missing >

select
RANK() OVER (ORDER BY count(*) desc) as DepartmentRank,
D.GroupName,
D.Name,
Count(*) as EmployeeCount
from
HumanResources.EmployeeDepartmentHistory  EDH
inner join HumanResources.Department D
on  EDH.DepartmentID = D.DepartmentID
where EndDate is Null
group by D.GroupName, D.Name
order by count(*) desc;

DENSE_RANK() Does not skips line numbers after a tie in rank.

select
DENSE_RANK() OVER (ORDER BY count(*) desc) as DepartmentRank,
D.GroupName,
D.Name,
Count(*) as EmployeeCount
from
HumanResources.EmployeeDepartmentHistory  EDH
inner join HumanResources.Department D
on  EDH.DepartmentID = D.DepartmentID
where EndDate is Null
group by D.GroupName, D.Name
order by count(*) desc;

Overriding Column Collation

This can be useful when joining across databases or in where clauses.
It effects columns where collation an issue i.e. text content.
Simply make both columns involved in the join/where clause the same collation

SELECT
   COLUMNLIST
FROM TABLE1 t1
INNER JOIN TABLE2 t2
ON t1.CHARCOLUMN COLLATE SQL_Latin1_General_CP1_CS_AS = t2.CHARCOLUMN COLLATE SQL_Latin1_General_CP1_CS_AS

replication : sp_get_distributor

Determining Replication setup using sp_get_distributor -

results on publisher
installed	distribution server	distribution db installed	is distribution publisher	has remote distribution publisher
1	SQL_DIST01	0	0	0
results on distributor
installed	distribution server	distribution db installed	is distribution publisher	has remote distribution publisher
1	SQL_DIST01	1	1	1
results on subscriber
installed	distribution server	distribution db installed	is distribution publisher	has remote distribution publisher
0	NULL	0	0	0

CROSS JOIN Example : Multiplying data rows

This example uses a CROSS JOIN to multiply data rows to a large result set.
The result will contain every combination of accountcode, month and year hence this technique is good for generating dimension tables.

-- create 3 temporary tables for the purpose of this demonstration

if object_id('tempdb..#accountcodes') is not null
 begin
 drop table #accountcodes
 end

create table #accountcodes (companycode char(10))

 insert into #accountcodes (companycode) values ('008')
 insert into #accountcodes (companycode) values ('009')
 insert into #accountcodes (companycode) values ('010')
 insert into #accountcodes (companycode) values ('011')


if object_id('tempdb..#years') is not null
 begin
 drop table #years
 end

create table #years (yearvalue int)

 insert into #years (yearvalue) values (2008)
 insert into #years (yearvalue) values (2007)
 insert into #years (yearvalue) values (2006)
 insert into #years (yearvalue) values (2005)
 insert into #years (yearvalue) values (2004)
 insert into #years (yearvalue) values (2003)

if object_id('tempdb..#months') is not null
 begin
 drop table #months
 end

create table #months (monthvalue int)

 insert into #months (monthvalue) values (1)
 insert into #months (monthvalue) values (2)
 insert into #months (monthvalue) values (3)
 insert into #months (monthvalue) values (4)
 insert into #months (monthvalue) values (5)
 insert into #months (monthvalue) values (6)
 insert into #months (monthvalue) values (7)
 insert into #months (monthvalue) values (8)
 insert into #months (monthvalue) values (9)
 insert into #months (monthvalue) values (10)
 insert into #months (monthvalue) values (11)
 insert into #months (monthvalue) values (12)


-- finally, perform the cross joins to get the final results.
-- there should be (4 x 6 x 12) 288 rows returned.

select * from #accountcodes cross join #years cross join #months

USP_DropTableConstraints_2005

Drops all constraints of the specified type from a table.
Takes database name, schema name, table name and constraint type as parameters.
Valid constraint types are ; 'PRIMARY KEY', 'FOREIGN KEY', 'CHECK'

Usage :
EXEC USP_DropTableConstraints_2005 'AdventureWorksTarget', 'Person', 'Contact', 'CHECK'

CREATE PROCEDURE USP_DropTableConstraints_2005
 @dbname VARCHAR(128),
 @schemaname VARCHAR(128),
 @tablename VARCHAR(128),
@constrainttype VARCHAR(128)
       
AS
-- USP_DropTableConstraints_2005 by sql solace
DECLARE  @sqlstring NVARCHAR(500)

SET @constrainttype = UPPER(LTRIM(RTRIM(@constrainttype)))

WHILE EXISTS (SELECT *
            FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
            WHERE  CONSTRAINT_CATALOG = @dbname
            AND    TABLE_SCHEMA = @schemaname
            AND    TABLE_NAME = @tablename
     AND    CONSTRAINT_TYPE = @constrainttype)
BEGIN
  SELECT @sqlstring = 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] DROP CONSTRAINT ' + CONSTRAINT_NAME
  FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  WHERE  CONSTRAINT_CATALOG = @dbname
  AND    TABLE_SCHEMA = @schemaname
  AND    TABLE_NAME = @tablename
  AND    CONSTRAINT_TYPE = @constrainttype
  PRINT @sqlstring
  EXECUTE sp_executesql  @sqlstring
END
GO