SQL Injection Antidote

Hi experts

in reference to the EE articlle 23639452
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:

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)
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)
SELECT colTableName
FROM @tblInjectedTable
OPEN curSQLInjectedTable
FETCH NEXT FROM curSQLInjectedTable INTO @tblTable
SET @iCount = 2
INSERT INTO @tblInjectedColumn(colColumnName)
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
SELECT @colColumnName = colColumnName FROM @tblInjectedColumn WHERE colRecordID = @iCount
SET @WhereCmd = @WhereCmd + 'OR ins.[' + @colColumnName + '] LIKE ''%<script%'''
SET @iCount = @iCountTo + 1
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
CLOSE curSQLInjectedTable
DEALLOCATE curSQLInjectedTable

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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
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 ?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

humer2000Author Commented:
Hi Angel III
Thx for the explanation
can u give me please a step by step trigger installation
Take a look at:

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"
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 !
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.
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 ?
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:
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.
humer2000Author Commented:
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.
humer2000Author Commented:
please consider my solution as an accepeted solution, because it reall the only and unique solution to prevent sql injection
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.