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
SQL Injection in Stored Procedure & Preventing from the same
Link1, Link2, Link3, Parameters, Perms and Procs: Are You Really Protected from Injection?
SQL Server SQL Injection from start to end, SQL Injections
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 1IF 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 tableDECLARE @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),
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:
Post a Comment