SQL Injection avoidance in SQL Server

Intro about SQL Injection :
             
         [ Content taken from my company documentations]

        The expansion of the Internet has made web applications become a part of everyday life. As a result the numbers of incidents which exploit web application vulnerabilities are increasing. A large percentage of these incidents are SQL Injection attacks which are a serious security threat to databases with potentially sensitive information. Therefore, much research has been done to detect and prevent these attacks and it resulted in a decline of SQL Injection attacks. However, there are still methods to bypass them and these methods are too complex to implement in real web applications. 

         [ Content taken from my company documentations]

A SQL Injection attack takes place when an attacker tries to gain access to a database by supplying special input to the web-site, which in turn sends the innocent input as an SQL-query to the Database Management System. The growing use of web-applications for business purposes has given motivation to attackers to explore the possibilities and exploit these type of attacks.

Am using  a user defined stored procedure which will take care of validating the input values provided by the users.

[code]



CREATE FUNCTION dbo.SQLInjectionCheck_UserDefinedFun
(@VenkatString varchar(max))

RETURNS BIT
AS
BEGIN
DECLARE @Suspect_ValBit bit

SET @VenkatString = ' ' + @VenkatString
IF (PATINDEX('% xp_%' , @VenkatString ) <> 0 OR
PATINDEX('% sp_%' , @VenkatString ) <> 0 OR
PATINDEX('% DROP %' , @VenkatString ) <> 0 OR
PATINDEX('% GO %' , @VenkatString ) <> 0 OR
PATINDEX('% INSERT %' , @VenkatString ) <> 0 OR
PATINDEX('% UPDATE %' , @VenkatString ) <> 0 OR
PATINDEX('% DBCC %' , @VenkatString ) <> 0 OR
PATINDEX('% SHUTDOWN %' , @VenkatString )<> 0 OR
PATINDEX('% ALTER %' , @VenkatString )<> 0 OR
PATINDEX('% CREATE %' , @VenkatString ) <> 0OR
PATINDEX('%;%' , @VenkatString )<> 0 OR
PATINDEX('% EXECUTE %' , @VenkatString )<> 0 OR
PATINDEX('% BREAK %' , @VenkatString )<> 0 OR
PATINDEX('% BEGIN %' , @VenkatString )<> 0 OR
PATINDEX('% CHECKPOINT %' , @VenkatString )<> 0 OR
PATINDEX('% BREAK %' , @VenkatString )<> 0 OR
PATINDEX('% COMMIT %' , @VenkatString )<> 0 OR
PATINDEX('% TRANSACTION %' , @VenkatString )<> 0 OR
PATINDEX('% CURSOR %' , @VenkatString )<> 0 OR
PATINDEX('% GRANT %' , @VenkatString )<> 0 OR
PATINDEX('% DENY %' , @VenkatString )<> 0 OR
PATINDEX('% ESCAPE %' , @VenkatString )<> 0 OR
PATINDEX('% WHILE %' , @VenkatString )<> 0 OR
PATINDEX('% OPENDATASOURCE %' , @VenkatString )<> 0 OR
PATINDEX('% OPENQUERY %' , @VenkatString )<> 0 OR
PATINDEX('% OPENROWSET %' , @VenkatString )<> 0 OR
PATINDEX('% EXEC %' , @VenkatString )<> 0)
BEGIN
SELECT @Suspect_ValBit = 1
END
ELSE
BEGIN
SELECT @Suspect_ValBit = 0
END
RETURN (@Suspect_ValBit)
END
GO

 -----------     -----------    
SELECT dbo.SQLInjectionCheck_UserDefinedFun
('SELECT * FROM HumanResources.Department')

--------------------- The result is "0"--------------------- 

SELECT dbo.SQLInjectionCheck_UserDefinedFun
(';SHUTDOWN')

--------------------- The result is "1"--------------------- 

SELECT dbo.SQLInjectionCheck_UserDefinedFun
('DROP HumanResources.Department')

--------------------- The result is "1"--------------------- 

[/code]


Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech,
www.kaashivinfotech.com
A very best Inplant Training and Internship Providers in Chennai

Ntile feature in SQL Server

I got a peculiar query from one of the user to fetch data from the tables.

 Here is the query, He needs to fetch 50% bottom data to the top and 50% top data to the bottom  of the table.      

  I've used Ntile option to manipulate the desired output.


create table #t(col1 varchar(6),col2 varchar(6))
--drop table #t
insert into #t values('a','s')
insert into #t values('a','g')
insert into #t values('b','h')
insert into #t values('b','r')
insert into #t values('s','j')
insert into #t values('s','k')
insert into #t values('e','o')
insert into #t values('e','p')
insert into #t values('q','x')
insert into #t values('q','c')
select * from #t

Select col1,col2 From (
    Select NTILE(2) Over (Order by col1 Desc) as HalfNumber, *
    From #t) as NtileTable
where HalfNumber = 1
union all
Select col1,col2 From (
    Select NTILE(2) Over (Order by col1 Desc) as HalfNumber, *
    From #t) as NtileTable
where HalfNumber = 2

Cheers,


Venkatesan Prabu .J
Head, KaaShiv InfoTech
A very best Inplant Training and Internship Providers in Chennai

Execute Stored Procedure inside another Stored Procedure

A small piece of code to execute procedure inside another procedure


create table venkat_Table( id int,
val varchar(1000) )

insert into venkat_table values (100,
'Venkat From KaaShiv InfoTech')

create procedure VenkatFirstProc
as begin
  select * from venkat_Table
end 


create Procedure VenkatSecondProc
as begin

  exec VenkatFirstProc
  
  end
  
  exec VenkatSecondProc

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech