[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

649 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