How To Communicate With UsersUsing Binary Serialization

Avoid Using Dynamic SQL Statements

May 24th, 2007

Mind if I look through your database?Most programmers who’ve developed Web-centric database applications know about SQL injections attacks where a malicious hacker can insert commands into your dynamically built queries to retrieve passwords, infiltrate systems, drop tables or do other harm. However, desktop application developers should also be aware of the security risks and complications involved when you use dynamically built SQL statements in your applications.

The bottom line is that building SQL statements in your application code is asking for trouble. Not only does it leave you open for malicious attacks but it also makes your code harder to maintain and can cause it to behave in unexpected ways.

First, lets look at SQL Injection. If you haven’t read it already, read Stop SQL Injection Attacks Before They Stop You by Paul Litwin. He gets into some detail on how this kind of attack can happen. You may think that this is an Internet only threat but it’s not. It would be easy for a disgruntled employee or other internal hacker to gain access to data they weren’t supposed to have or even delete tables if they weren’t secured well. Remember, the “How To” for it is only a Google search away.

Secondly, if you need to make changes to the query, even a small one, this means a recompile and redeployment of your executable. Making a change to a stored procedure is much simpler and reliable. Another bonus of using a stored procedure with parameters is that you don’t have to worry about matching up or escaping embedded quotation marks. All this error prone and costly string manipulation code is handled for you. Plus, using stored procedures means that your query is optimized to run on the server, thus improving the performance of the query and your application.

Our last reason are unintended results from queries causing problems. These can also affect stored procedures. A good example would be a search criteria field. The value the user enters is used in the WHERE clause to narrow the data returned. But what if the user doesn’t enter any criteria. You may end up trying to populate a control with 1000’s of rows of data or you might have a query that runs several minutes. Another might be if a user enters a wildcard ‘%’ and uses it to gain access to records they shouldn’t have access to. To avoid these kinds of problems you should always validate user input, even if you’re loading a parameter object for a stored procedure. As Paul Litwin mentions in his article, all user input should be considered suspect and validated.

So, when you’re developing database queries for you application, think stored procedures with validated input and you’ll greatly improve the security and stability of your application on both the front and back ends.

Share This Article: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • del.icio.us
  • Reddit
  • StumbleUpon
  • Technorati
  • DotNetKicks
  • DZone

Entry Filed under: Tip Sheets


Rate This Article:

Not That GoodCould Be BetterOKGoodGreat (No Ratings Yet)
Loading ... Loading ...

Leave a Comment

Required

Required, hidden

Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Trackback this post  |  Subscribe to the comments via RSS Feed


Visit Me At My New Site, Programming In C#

Most Popular Articles

Highest Rated Articles

Categories

Most Recent Articles

Feeds

 Subscribe in a reader

To subscribe by e-mail
Enter your address here

Delivered by FeedBurner

VB Opportunities

Archives