Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JBelthoff
Posting Yak Master
173 Posts |
 Posted - 2004-12-25 : 21:36:09
|
Hi All,I am working on a new ASP.NET web application. Can I be assured that I am safe from an SQL Injection Attack if I only use Stored Procedures with parameters for accessing data, assuming that I don't have any dynamic sql in the procs?Or is there more that I am not considering?Any help would be greatly appreciated.Thanks,JB  -- We know a man called Mr. Gump. Mr. Gump has a seven hump Wump. So... if you like to go Bump! Bump! just jump on the hump of the Wump of Gump. -- |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2004-12-26 : 02:46:37
|
| No, is the short answer. If what you are passing into the stored procedure already has the injected modification, you are still suseptible to attack. Consider the case of a sproc that takes a @CustID parameter and selects the customer data, a laSELECT CustName, CustAddress, CustPhone FROM CustomersWHERE CustID = @CustIDIf the value of @CustID is set to "'12345' delete Customers", you are still going to suffer.Having said that, stored procedures are the proper security step to take but they do not, per se, prevent injection attacks.HTH=================================================================Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -Mark Twain, author and humorist (1835-1910) |
 |
|
|
JBelthoff
Posting Yak Master
173 Posts |
Posted - 2004-12-26 : 09:39:53
|
| Thanks Steve,So what does one do for protection.I have seen web side scripts that Replace() words such as "delete" "drop" with "". I will also be using ASP.NET's command object and sending the parameters through that object. Seeing that the command object accepts single quotes without producing error in the DB, replacing single quotes would actually store 2 single quotes.Would I need to do the same in the SPROC and if one of the params has "delete", or the other "bad words" in the string simply Return(8) an error.I guess what I am asking is are there any standards for protection? Any good articles out there that provide advice?Thanks,JB -- We know a man called Mr. Gump. Mr. Gump has a seven hump Wump. So... if you like to go Bump! Bump! just jump on the hump of the Wump of Gump. -- |
|
|
|
JBelthoff
Posting Yak Master
173 Posts |
Posted - 2004-12-26 : 10:08:13
|
| Hi again,I did some testing with the command asp.net object using parameters. Seems that that object formats the input as an acceptable string for db input.For instance...If I enter "'12345' delete from customers" the command object will simply store that exact string in the db, quotes and all.To change my original question....If I use the asp.net command object with parameters and stored procedures would I then be safe?Thanks,JB -- We know a man called Mr. Gump. Mr. Gump has a seven hump Wump. So... if you like to go Bump! Bump! just jump on the hump of the Wump of Gump. -- |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-26 : 10:38:24
|
quote:
SELECT CustName, CustAddress, CustPhone FROM CustomersWHERE CustID = @CustIDIf the value of @CustID is set to "'12345' delete Customers", you are still going to suffer.
? Suffer what? Not sql injection. You might suffer a datatype mismatch, or no data being returned, or something like that, especially if @CustID should be an integer and you are declaring it as a varchar. Eitherway, that is not a situation in which sql injection is possible. Now, if instead, you say:exec ('... where customerid =''' + @custid + '''')in a stored proc, then sql injection is possible.- Jeff |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-26 : 12:21:01
|
| >> If I use the asp.net command object with parameters and stored procedures would I then be safe?The answer should be yes.It depends on the stored proc.Someone argued that stored procedures are bad because a lot people don't really know how to code them so include vulnerabilities.If you stick to passing parameters which are used in queries to access fields then you are ok.Once you start using dynamic sql in the SP then you can have problems.As jsmith8858 points out the example given will not suffer from injection.Best is to give the user permissions on stored procedures but not the tables or views (or anything else) then dynamic sql cannot be included in the stored procs and you will be secure.Have a look at a .net dal which prevents anyone doing naughty thingshttp://www.nigelrivett.net/DotNet/DotNetDBAccess.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
JBelthoff
Posting Yak Master
173 Posts |
Posted - 2004-12-26 : 12:31:48
|
Hi Nigel,Thanks, that clears things up in a big way.I realize the datatype mismatch in the above example, I was just pondering using the thoery behind it.Thank you all.JB  -- We know a man called Mr. Gump. Mr. Gump has a seven hump Wump. So... if you like to go Bump! Bump! just jump on the hump of the Wump of Gump. -- |
|
|
|
|
|
|
|
|
SQL Injection Question