"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

June 08, 2011

TSQL Tip of the Day

Tip #1

I had good overview on SQL Injection. Cross side scripting I have to admit I need to revise. Good Number of interesting posts I read.

SQL Injection
What it is ?
User provided input is directly passed to Database without verifying the query parameters. User would be able to inject his query and execute against the database
Why it occurs ?
No input parameter validation
How to prevent ?
Validate parameters sent by user before passing it to database layer
Best Practices - Giving SQL Injection the Respect it Deserves
  • Use of parameterized queries
  • Use Procedures
  • Grant only execute permission for SP. No DDL operations for users
Example Queries
Select * from Earnings Where Name = 'Ram' or 1=1--
Select * from Earnings Where Name = 'Ram';Select * from Earnings

More Examples
 
 
Additional Tip - Cross Side Scripting

Cross-site scripting (XSS) is a technique for injecting scripting code into Web pages from a (usually malicious) Web site.  Link

How to prevent ?
Microsoft KB - Checking Input parameters, URL, cover all possible input sources (query parameters, body parameters of POST request, HTTP headers).

I was bit confused with Phinshing. How Phishing and CSS correlate
Phishing is a deliberate misrepresentation of a Web site for the purpose of collecting personal information from a user (bank account number, SSN, CC number, etc.). Link

More Reads
Free Edition of Acunetix Web Vulnerability Scanner
Validator.NET v1.0 - Tool helps eliminate common vulnerabilities such as SQL Injection and Cross-Site Scripting.(Mcafee Free Tool)
Codeplex - Ethical Hacking ASP.NET
SQLbits - Understanding and Preventing SQL Injection Attacks

Tip #2

Identity VS SCOPE_IDENTITY

This link is awesome. I wanted to give this example a try

USE APPDB
--STEP 1
IF OBJECT_ID ('TestTable1') IS NOT NULL DROP TABLE TestTable1
CREATE TABLE TestTable1
(
Number INTIDENTITY(1,1) PRIMARY KEY,
A VARCHAR(20) NOT NULL,
B VARCHAR(20) NOT NULL,
C VARCHAR(20) NOT NULL,
D VARCHAR(20) NOT NULL
)

--STEP 2
--Insert Records into this table
DECLARE @I INT
SET @I = 1000
WHILE 1 = 1
BEGIN
SET @I = @I + 1
INSERT INTO TestTable1(A,B,C,D)
VALUES ((CONVERT(CHAR(5),@I)+'A'),(CONVERT(CHAR(5),@I)+'B'),
(CONVERT(CHAR (5),@I)+'C'),(CONVERT(CHAR(5),@I)+'D'))
IF @I=10000
BREAK;
END

--STEP 3
--Audit Table
IF OBJECT_ID ('AuditInsertTestTable1') IS NOT NULL DROP TABLEAuditInsertTestTable1
CREATE TABLE AuditInsertTestTable1
(
Number INTIDENTITY(1,1) PRIMARY KEY,
A VARCHAR(20) NOT NULL,
B VARCHAR(20) NOT NULL,
C VARCHAR(20) NOT NULL,
D VARCHAR(20) NOT NULL
)

--STEP 4
--INSERT TRIGGER
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'trigTestAudit' AND type = 'TR')
   DROP TRIGGER trigTestAudit
GO
CREATE TRIGGER trigTestAudit
ON TestTable1
FOR INSERT
AS
BEGIN
        INSERT INTO AuditInsertTestTable1(A,B,C,D)
        SELECT D.A, D.B,D.C, D.D FROM INSERTED D
END               



--STEP 5
--Procedure to Insert Record  
CREATE PROCEDURE INSERTTEST (@A VARCHAR(20),
 @B VARCHAR(20),
 @C VARCHAR(20),
 @D VARCHAR(20)
 )
 AS
 BEGIN
BEGIN TRY
 INSERT INTO TestTable1(A,B,C,D)
 VALUES(@A,@B,@C,@D)
 SELECT 'Identity Value is '
 SELECT  @@IDENTITY;
 SELECT 'SCOPE Identity Value is '
 SELECT  SCOPE_IDENTITY();
END TRY
BEGIN CATCH
 RAISERROR ('INSERT', 0, 1) WITH NOWAIT
END CATCH
 END
 GO
--STEP 6
SELECT * FROM TestTable1
 
--STEP 7
--Execute this Procedure
DECLARE @A VARCHAR(20), @B VARCHAR(20), @C VARCHAR(20), @D VARCHAR(20)
SELECT @A='1004 A', @B='1004 B', @C = '1004 C', @D= '1004 D'
EXEC dbo.INSERTTEST @A, @B, @C, @D


--STEP 8
Please find output value.




As you can clearly see. SCOPE IDENTITY returns value in the scope of procedure. Identity returns value inserted in trigger. Now I will remember this clear answer :)


  --Audit Table Entry
SELECT * FROM AuditInsertTestTable1

Happy Reading!!

No comments: