Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 24th, 2005, 07:41 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to ~Bean~
Default DB Protection

I want to be sure I am using good techniques for protecting myself from SQL attacks. I have read several documents on the topic (SQL injection attacks), but am confused mostly as to what protection stored procedures are giving me. I am using SQL Server. Almost all my database access is done through stored procedures, but I am confused whether I still need to be concerned with filtering things like apostrophes and double dashes and other characters that can be used to hack into my db.

What is a good (enough) protection scheme?

-------------------------
Beware of programmers with screwdrivers...
__________________
-------------------------
Beware of programmers with screwdrivers...
Reply With Quote
  #2 (permalink)  
Old October 24th, 2005, 02:08 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Stored procedures generally provide an extra layer of defense.

If you use SQL text generated by your code, then you make it easy for an injection attack. For example, if you have code like:
Code:
mySQL = "SELECT * FROM Products WHERE ProductID = " & textbox.text"
and then execute the string, an attacker who fills in the textbox with the string "ProductID; DELETE Products;" will wreak havoc. Using a stored procedure presumably would assign the textbox value to a parameter. The stored procedure code would then be:
Code:
SELECT * FROM Products WHERE ProductID = @parameter
and the same text will simply result in an error rather than sending you scrambling for a backup.

While this is a measure of defense, the best is scrupulous editing of the input data. If special characters don't belong in your database, then they don't belong in the input to it. Remove them by filtering such junk out.

"good (enough)" is a relative term...



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #3 (permalink)  
Old October 24th, 2005, 03:05 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to ~Bean~
Default

If I use a stored procedure like you say, and the @parameter I pass to it has a value of "ProductID; DELETE Products;"...would that not have the same result as the text generated attack? Would the sp not execute SELECT * FROM Products WHERE ProductID = ProductID; DELETE Products; ???

Unfortunately I need to allow most special characters. I thought of using a scheme whereby I replace any special characters with an HTML code equivalent. For example, an "=" sign would be replaced with "#61;"...then when I need to read that text back I reverse the process. This works OK, but I'm just not sure if this is overkill...and this has also caused problems with simple data binding...



-------------------------
Beware of programmers with screwdrivers...
Reply With Quote
  #4 (permalink)  
Old October 24th, 2005, 04:05 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by ~Bean~
 If I use a stored procedure like you say, and the @parameter I pass to it has a value of "ProductID; DELETE Products;"...would that not have the same result as the text generated attack? Would the sp not execute SELECT * FROM Products WHERE ProductID = ProductID; DELETE Products; ???
No, because you have defined a datatype for the parameter (presumably varchar() or some such). Parameterized queries are essentially a value substitution, so the code executed would be:
Code:
 ... WHERE ProductID = <the value of the parameter>
and the value of the parameter would be the string 'ProductID; DELETE Products;' which most likely won't match anything. If the parameter was defined as say an INTEGER, then the WHERE clause would fail with a type conversion error.

But it would be better for that sort of error to occur on the client, hence the idea of editing the data before you ship it off to the database.

Quote:
quote:
Unfortunately I need to allow most special characters. I thought of using a scheme whereby I replace any special characters with an HTML code equivalent. For example, an "=" sign would be replaced with "#61;"...then when I need to read that text back I reverse the process. This works OK, but I'm just not sure if this is overkill...and this has also caused problems with simple data binding...
My humble opinion is that is overkill. Depends on your environment and your level of paranoia, I guess. Presumably your data follows some sort of business rules - that is it's not totally free form. Given that, apply those rules to data that a user inputs (or that you obtain from anywhere "outside"), use parameterized queries, and you should be OK.



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #5 (permalink)  
Old October 25th, 2005, 07:37 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to ~Bean~
Default

Thanks Jeff! That helps immensely!

-------------------------
Beware of programmers with screwdrivers...
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Decompile Protection Eudora Servlets 0 July 18th, 2005 09:59 PM
Protection evad Excel VBA 0 April 26th, 2005 09:06 AM
Protection and commandButtons bahachin Excel VBA 3 March 4th, 2005 09:27 AM
Password Protection and Other Items snoopy92211 Excel VBA 1 January 14th, 2005 02:27 AM
Protection level noman77 .NET Framework 2.0 0 October 23rd, 2004 07:33 AM



All times are GMT -4. The time now is 04:04 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.