October 12, 2008 at 3:04 am
I am using SQl Server 2000 but facing lot of problems due to SQl injections in which script is inserted into tables fields.plz help me to slove this issue.
Regards
October 12, 2008 at 3:18 am
SQL injection is a front end vulnerability that comes from concatenating together SQL statements and executing them.
The way to completely prevent SQL injection is simple, but may take a lot of work with an existing application.
1) Ensure that all calls from the web front end to the database server use stored procedures and are parameterised
2) Ensure that there is no dynamic SQL in the stored procedures
3) Ensure that the web application user has only execute rights on the stored procedures and no rights at all to the base tables
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
October 12, 2008 at 9:04 am
You have written bad code in your scripts and are not properly checking for validation of parameters.
http://www.sqlservercentral.com/articles/Security/updatedsqlinjection/2065/
October 14, 2008 at 12:16 pm
I have a question.. lets say you have to use a dynamic query. how about you modify the dynamic query string like this
eg.
incoming query: " select * from test1 where vchusername = '@var' "
where they sent @var = ;drop table test--
but now you run a replace statement on the string before sending it to your execute
@var = replace(@var,'-',''''+'convert(char,45)'+'''')
@var = replace(@var'-',''''+'convert(char,59)'+'''')
essentially replace all the known bad characters with their ascii number or escape char equivalent
October 14, 2008 at 1:07 pm
bcronce (10/14/2008)
I have a question.. lets say you have to use a dynamic query. how about you modify the dynamic query string like thiseg.
incoming query: " select * from test1 where vchusername = '@var' "
where they sent @var = ;drop table test--
but now you run a replace statement on the string before sending it to your execute
@var = replace(@var,'-',''''+'convert(char,45)'+'''')
@var = replace(@var'-',''''+'convert(char,59)'+'''')
essentially replace all the known bad characters with their ascii number or escape char equivalent
There are two problems with this approach:
1) there is no definitive list of "all known bad characters", especially given the strange things in Unicode and even just in the upper half of the ASCII table. So if Microsoft never prints a definitive list then you can never be sure that you have them all.
2) This approach will absolutely hose many perfectly legitimate values.
Additionally, note for instance that the example that you gave would result in the following SQL command:select * from test1 where vchusername = ';drop table test--' which does not cause any problems.
In the case on the example that you gave (assuming that they actually sent ';drop table test-- which would cause problems, the best solution would be to transform your Dyanmic SQL into an sp_executesql call like this:
@cmd = 'select * from test1 where vchusername = @var '
exec sp_executesql @cmd, '@var varchar(255)', @var=@var
[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]
October 14, 2008 at 1:17 pm
rbarryyoung (10/14/2008)
There are two problems with this approach:
1) there is no definitive list of "all known bad characters", especially given the strange things in Unicode and even just in the upper half of the ASCII table. So if Microsoft never prints a definitive list then you can never be sure that you have them all.
2) This approach will absolutely hose many perfectly legitimate values.
In addition, you just have to miss one place and all of your checking is useless. If the web user has no rights other than exec procedure then you don't have to worry about missing a spot.
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
October 14, 2008 at 1:39 pm
GilaMonster (10/14/2008)
In addition, you just have to miss one place and all of your checking is useless. If the web user has no rights other than exec procedure then you don't have to worry about missing a spot.
Agreed.
[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]
October 17, 2008 at 1:52 pm
Hi Also make sure that the guest user is assigned restrictive permissions. Explicitly denying permissions on the system tables can help reduce some impact. I have faced the SQL Injection Attack in my current environment and noticed that part of the reason for the table being injected with the script was because the guest user had dbo permissions.I changed the permissions to dbdatareader and dbdatawriter and explicitly denying permissions on system tables as well as explicitly granting excute permissions to the stored procedure.
October 17, 2008 at 2:13 pm
In my opinion, guest should have no rights at all in the DB. Logins that need access to the DB should be granted access and have the exact permissions they need assigning. Datareader and datawriter are far too high for the guest account.
SQL 2005 has done a lot of tightening to the metadata security. In 2005 and higher, if a user has no rights on an object (table or the like) then they cannot see it in the system tables. Hence if the application user only has execute rights on the stored procedures, the only objects they'll be able to see when they query the metadata are those stored procedures. It will be as if the tables don't exist. And, of course, since the user has no rights on the tables, they can't insert anything into them anyway
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply