Solved

SQL Injection Antidote

Posted on 2008-10-27
13
307 Views
Last Modified: 2009-10-12
Hi experts

in reference to the EE articlle 23639452
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23639452.html
ShekharVarshney wrote a trigger to restrict the SQL injection further on the data tables residing in the database.

The method to do was as given below:

BEGIN
DECLARE @tblInjectedTable TABLE (colTableName varchar(255))
DECLARE @tblInjectedColumn TABLE (colRecordID INT IDENTITY(1,1), colColumnName varchar(255))
DECLARE @tblTable varchar(255), @colColumnName varchar(255), @Cmd NVARCHAR(4000), @iCount SMALLINT, @iCountTo SMALLINT, @WhereCmd NVARCHAR(4000)
INSERT INTO @tblInjectedTable(colTableName)
SELECT DISTINCT a.name
FROM sysobjects a, syscolumns b
WHERE a.id = b.id AND a.xtype = 'u' AND b.length>99 AND
(b.xtype = 99 OR
b.xtype = 35 OR
b.xtype = 231 OR
b.xtype = 167)
DECLARE curSQLInjectedTable CURSOR FOR
SELECT colTableName
FROM @tblInjectedTable
OPEN curSQLInjectedTable
FETCH NEXT FROM curSQLInjectedTable INTO @tblTable
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @iCount = 2
INSERT INTO @tblInjectedColumn(colColumnName)
SELECT DISTINCT b.name
FROM sysobjects a, syscolumns b
WHERE a.id = b.id AND a.name = @tblTable AND a.xtype = 'u' AND b.length>99 AND
(b.xtype = 99 OR
b.xtype = 35 OR
b.xtype = 231 OR
b.xtype = 167)
SELECT @iCountTo = COUNT(*) FROM @tblInjectedColumn
SELECT @colColumnName = colColumnName FROM @tblInjectedColumn WHERE colRecordID = 1
SET @WhereCmd = 'WHERE ins.[' + @colColumnName + '] LIKE ''%<script%'''
WHILE @iCount  <= @iCountTo
BEGIN
SELECT @colColumnName = colColumnName FROM @tblInjectedColumn WHERE colRecordID = @iCount
SET @WhereCmd = @WhereCmd + 'OR ins.[' + @colColumnName + '] LIKE ''%<script%'''
SET @iCount = @iCountTo + 1
END
SET @Cmd = 'CREATE TRIGGER t' + @tblTable + ' ON [' + @tblTable + '] FOR INSERT, UPDATE AS
BEGIN DECLARE @iCount BIGINT SET @iCount = 0 SELECT @iCount = COUNT(*) FROM inserted ins '
+ @WhereCmd + ' IF @iCount > 0 ROLLBACK TRANSACTION END'
exec sp_executesql @Cmd
FETCH NEXT FROM curSQLInjectedTable INTO @tblTable
END
CLOSE curSQLInjectedTable
DEALLOCATE curSQLInjectedTable
END


Ok, please correct if i am wrong, this script prevents sql injection from being inserted in the database or what exactly?

The second problem i have with this script when i type in Qyery analyser against in test database, i get the following error


(60 row(s) affected)


(3 row(s) affected)

Server: Msg 2714, Level 16, State 5, Procedure t_xLGConstValues, Line 65535
There is already an object named 't_xLGConstValues' in the database.

(3 row(s) affected)

Server: Msg 207, Level 16, State 3, Procedure taffiliatelog, Line 2
Invalid column name 'idXML'.
Server: Msg 207, Level 16, State 1, Procedure taffiliatelog, Line 2
Invalid column name 'ItemLabel'.

Thanks for ur assistance.



0
Comment
Question by:humer2000
13 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 22812184
the script actually generates triggers against all the tables, and gives them a standard name.
it's those triggers trying to prevent the sql injection on the back end (ie in the database)

>Server: Msg 2714, Level 16, State 5, Procedure t_xLGConstValues, Line 65535
>There is already an object named 't_xLGConstValues' in the database.

that means that the script tries to create a trigger with a name, that name t_xLGConstValues being used already.


>Server: Msg 207, Level 16, State 3, Procedure taffiliatelog, Line 2
>Invalid column name 'idXML'.
>Server: Msg 207, Level 16, State 1, Procedure taffiliatelog, Line 2
>Invalid column name 'ItemLabel'.
not sure where those can come from.

0
 
LVL 22

Assisted Solution

by:dportas
dportas earned 100 total points
ID: 22812691
Triggers are a poor way to protect against SQL injection except maybe as an emergency measure when you first discover a vulnerability.

Better approaches include: avoid dynamic SQL; make sure your procs are parameterised properly; validate inputs and properly escape your outputs; use CAPTCHAS.
0
 
LVL 1

Author Comment

by:humer2000
ID: 22812835
I have secured all my ASP files
I also installed dotdefender
i blocked port 1433 over my firewall
eventhough a hacker is till able to inject sql in all fields of my tables, by puting scripts like <script src=http://jsani.cn></script>
Actually i am out of ideas and don't know what to do so if triggers can be considered as additional parameters, why not using them ?
0
 
LVL 1

Author Comment

by:humer2000
ID: 22812893
Hi Angel III
Thx for the explanation
can u give me please a step by step trigger installation
thx
0
 
LVL 2

Assisted Solution

by:devshb
devshb earned 300 total points
ID: 22839604
Take a look at:
http://www.sqlinjectionscanner.com/

They've got a free data scanner; you can use that to double-check if there's any injected data hanging around in the database.

Securing your code/database is obviously a good idea, but hackers will almost always find any vulnerabilities you might have, so it's a good idea to have a reliable full-scan tool available which is dedicated to the task of checking against injected data/xss attacks in the data.

ie click on the download free version of the "ASP SQL Injection Scanner Tool"
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:humer2000
ID: 22839674
ok i will test the scanner
I don't believe that injections i have are from my code
injections i have look like a stored procedure because hacker is able to inject <script src="http://www.hanrou.cn"></script> in varchar fields (> 50 characters) in almost all fields in my database in less than one minute !
0
 
LVL 2

Assisted Solution

by:devshb
devshb earned 300 total points
ID: 22839825
Remember that if the code is vulnerable to injections, then a hacker can easily use a function/procedure to change all columns in all tables in one shot (or automate a script on their side to achieve the same thing), so it is possible that they can effect all columns/tables in one shot without leaving behind a physical procedure in the database.

I agree with dportas though, his/her comments were spot-on I reckon.

Also have a look through the "resources" section on that http://www.sqlinjectionscanner.com/ site because there's lots of useful info there about different techniques/solutions etc.
0
 
LVL 1

Author Comment

by:humer2000
ID: 22840640
well devshb, i can agree with u if i found traces in IIS logs, but the problem is that there is no any injection trace in IIS logs, no traces in SQL logs, no traces in DotDefender logs neither.
so i really wonder how he did it
My last hope is to run sql profiler and trace all transactions, the problem with the profiler that i might see the injection, but it does not give the source or the file name used for the injection.
Do u have cool ideas on this ?
0
 
LVL 2

Assisted Solution

by:devshb
devshb earned 300 total points
ID: 22840858
If the data's being altered (as opposed to, say, physical files being effected), then it's more likely to be an injection/xss-attack via code vulnerabilities than a general virus being on your server (or on your pc) I think.

Hackers can hide the string from the logs by encoding it during the injection, so that when you look at the raw log files you often won't see any reference to the final injected value as the log files will hold only encoded values.

Some values might not be encoded; eg try looking for:
DECLARE
in your log files; almost everything else might be encoded and impossible to find, but usually the "DECLARE" part of an injection will be found in the log files. That's often the place to start because it'll help you track what files they've been using against you (ie which files they've found that are vulnerable).

So, searching for key sql commands/words such as "DECLARE" or "BEGIN" in the log files can often help point you in the right direction. You'd need to search for them in the raw log files though; searching through generated stats reports wouldn't be good enough.
0
 
LVL 1

Author Comment

by:humer2000
ID: 25519595
Hi
The only software that you can install on your windows server that blocks all types of SQL injection is Dot Defender. It cost 3.000 $ but it's worthy if you have critical applications hosted on your server.
0
 
LVL 1

Author Comment

by:humer2000
ID: 25519609
please consider my solution as an accepeted solution, because it reall the only and unique solution to prevent sql injection
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 40
Server 2008 Cluster Fail-over Errors 5 44
How to convert JSON file to csv? 7 52
Updating variable table 9 17
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now