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
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

829 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