Solved

SQL Injection Antidote

Posted on 2008-10-27
13
334 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

751 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