|
|
|
SSC-Addicted
 
Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 458,
Visits: 2,554
|
|
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
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Friday, July 16, 2010 3:54 PM
Points: 906,
Visits: 164
|
|
| This is really bad - You are allowing anyone to run anything they want - DON'T DO IT !!!!
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: 2 days ago @ 10:16 AM
Points: 1,854,
Visits: 4,781
|
|
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
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 458,
Visits: 2,554
|
|
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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: 2 days ago @ 7:52 AM
Points: 13,008,
Visits: 3,169
|
|
| Why are you going down that route? What is being asked of you?
|
|
|
|
|
SSCoach
Group: General Forum Members
Last Login: Today @ 4:44 PM
Points: 19,604,
Visits: 14,498
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 458,
Visits: 2,554
|
|
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?
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Friday, July 16, 2010 3:54 PM
Points: 906,
Visits: 164
|
|
| 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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: 2 days ago @ 7:52 AM
Points: 13,008,
Visits: 3,169
|
|
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
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Wednesday, September 01, 2010 4:23 PM
Points: 9,341,
Visits: 8,799
|
|
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.
-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
|
|
|
|
