Making a dynamic page displayed from using URL Parameters secure from SQL injection?

Posted on 2011-09-30
Medium Priority
Last Modified: 2012-05-12
Hello I have a page that displays content based off URL parameters relative to MS SQL content.
I am using the classic Default.asp?ID=number&name=name approach.

What are simple steps that I can do to help from SQL injection attacks?
Is there a simple piece of code that can "filter" or limit or clean?

I am coding with ASP Classic.

Thank you in advance.
Question by:colonelblue
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 668 total points
ID: 36894571

If you are looking for one piece of code, I will say using ADODB parameters versus passing the values of the query request string directly to SQL. It is a bit more complex obviously, but the key is to only trust input you control; therefore, I tend to make local variables for these values before even passing to the parameters. In doing this, I usually ensure that numbers are numbers and not 1 or '; DELETE * ...' you get the drift. That allows you to pass defaults if a specific parameter fails, which then protects your code from blowing up and displaying pertinent database information (which you should ensure is not being sent from IIS, i.e., turn off sending ASP error details once an application is in production -- use custom error handling to handle any notifications or specific storage/handling of specific errors then user does not need to see them).

When I was a web developer, I would read these guys all the time:

Beyond that I would look at ADODB command parameters.

Hope that helps!
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 664 total points
ID: 36894597
Using a query string is 'ok' if you are just looking up data.  Date in the URL and query string is called the GET method for a page request.  Note that the query string can be edited by the user.

You should use forms and the POST method to put data into a database or other storage.  POST data is not displayed in the address bar and can't be modified by the normal user.  You should also use it when you don't want the users to easily edit it.

Here's an article using ASP: http://www.sitepoint.com/sql-injection-attacks-safe/   Some suggest using 'parameters' to build queries as a way to prevent SQL injection.  I believe there are ways to do that with ADO.

Accepted Solution

worthyking1 earned 668 total points
ID: 36905640
There are two things I normally do in this scenario:

1. Sanitize the incoming text for sql injection characters, such as single quotes and semi-colons. This will stop 99% of all text based injection attempts. I use a simple replace function like this:

Function Sanitize(TheText)
      Sanitize = Trim(Replace(Replace(Replace(Replace(Replace(TheText, ";", ""), "%3B", ""), """", ""), "'", ""), "%27", ""))
End Function

Use if simply like this:

TheName = Sanitize(request("name"))

Add as many replacements as you want. I just use the most common as above and replace them with nothing, essentially breaking any attempted sql query that would affect my sites.

2. Format your incoming data very specifically for your needs. Meaning convert the querystring values to the exact data types and lengths you need, for example:

TheName = Left(Sanitize(request("name")),64)   ' assuming you're allowing no more than 64 chars for a name
TheID = CInt(request("ID"))  ' ALWAYS do this to ensure the incoming val is an integer

Or check them first before using them, such as:

If Len(request("name"))>64 then ErrorMsg = "Sorry, name is too long."

There are a lot of ways to skin this cat. Choose what's best for your situation, but the bottom line is that if you ensure the incoming data is correctly formatted before passing it to the DB or to your output, then you won't ever run into an injection string having any valid effect on your site.

Author Comment

ID: 36925866
Fantastic! Thank you! I gleaned powerful information and technique from all three of you.

Sincere thanks and kind regards.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question