Solved

SQL Injection Antidote

Posted on 2008-10-27
13
343 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 143

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

636 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