January 28, 2009 at 9:23 pm
Please post your views on this:
alter procedure usp_Test
(
@query nvarchar(4000)
)
as
begin
declare @Pos1 int
declare @Pos2 int
select @Pos1 = 0, @Pos2 = 0
select @Pos1 = charindex(';', @Query, 1)
select @Pos2 = charindex('--', @Query, 1)
if @Pos1 > 0 or @Pos2 > 0
raiserror('Input string contrains ; or --. It could be SQL Injection Attack', 16, 1)
else
exec (@Query)
end
go
January 29, 2009 at 3:19 am
This is really bad - You are allowing anyone to run anything they want - DON'T DO IT !!!!
January 29, 2009 at 3:38 am
Not really a good idea to prevent attacks, you should really be using paramatized SQL in your procedures, rather than executing a Dynamic SQL statement.
Also there are other ways of injecting attacks, without using ';' or '--', such as using HEX strings in the attack
January 29, 2009 at 6:02 am
Also there are other ways of injecting attacks, without using ';' or '--', such as using HEX strings in the attack
Is this enough to take care of HEX string?
select @Pos3 = charindex('0x', @Query, 1)
if @Pos1 > 0 or @Pos2 > 0 or @Pos3 > 0
raiserror('Input string contrains ;, -- or 0x. It could be SQL Injection Attack', 16, 1)
else
exec (@Query)
end
January 29, 2009 at 6:05 am
Why are you going down that route? What is being asked of you?
January 29, 2009 at 6:13 am
It's not sufficient. Consider this malicious parameter that has neither ;, nor --, nor any hex string at all.
declare @query varchar(max)
declare @parameter varchar(200)
set @parameter = '''' + CHAR(13) + CHAR(10) + ' drop database myDB ' + CHAR(13) + CHAR(10) + ' print '' '
set @query = 'select * from sys.objects where name = ''' + @parameter + ''''
print @query
exec (@query)
The only way you're going to secure agaisnt SQL injection is to not accept untrusted string values and execute them. The best way is to only use parameterised queries or stored procedures.
Any IT security professional will tell you that blacklisting process is doomed to failure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2009 at 6:27 am
The original code that I posted is just a simplied sample.
The actual code could be some thing like this:
declare @query nvarchar(4000)
select @query = 'SELECT ColumnName FROM tbl_TableName WHERE ' + @WhereCondition
execute(@query)
@WhereCondition is generated by front end application. It is written by others. I can't change it.
In SQL Server I wish to prevent SQL Injection Attack. What are the steps I need to take?
January 29, 2009 at 6:32 am
Surely you have only a finite number of fields they could use in the where clause so just have parameters for each of those - You are really asking for trouble doing the query your way
January 29, 2009 at 6:33 am
It doesn't matter, you're still vulnerable to attacks.
Check this out for a better view of the problem and solutions :
http://jumpstarttv.com/hacking-sql-server_76.aspx?searchid=15442
January 30, 2009 at 12:43 pm
Suresh B. (1/29/2009)
The original code that I posted is just a simplied sample.The actual code could be some thing like this:
declare @query nvarchar(4000)select @query = 'SELECT ColumnName FROM tbl_TableName WHERE ' + @WhereCondition
execute(@query)
@WhereCondition is generated by front end application. It is written by others. I can't change it.
In SQL Server I wish to prevent SQL Injection Attack. What are the steps I need to take?
As a general problem, there is no solution to this other than rewriting the Client AND the Server code to work together more securely.
However, for simple cases like this, there are sometimes point solutions that can be implemented. Here is how I would do it (but ONLY after I had first tried very, very hard to fix the client code!):
1) Insure that Public has NO access to your database.
2) Create a SQL Login and User in the database called "tbl_TableName_Reporter". It should only have the "Public" roles.
3) Grant it SELECT-only access to the table:Grant Select on OBJECT::dbo.vwNum16 to tbl_TableName_Reporter
4) Change your code above to be as follows:declare @query nvarchar(4000)
select @query = 'SELECT ColumnName FROM tbl_TableName WHERE ' + @WhereCondition
Execute(@query) As USER='tbl_TableName_Reporter'
Repeat this, with a different user name, for every stored procedure/table that is like this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 2, 2009 at 2:54 am
I am thankful to all you for your valueable suggestions.
February 2, 2009 at 9:29 am
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply