Solved

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

Posted on 2011-09-30
4
344 Views
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.
0
Comment
Question by:colonelblue
4 Comments
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 167 total points
ID: 36894571
colonelblue,

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:
http://www.4guysfromrolla.com/webtech/061902-1.shtml

Beyond that I would look at ADODB command parameters.

Hope that helps!
0
 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 166 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.
0
 
LVL 6

Accepted Solution

by:
worthyking1 earned 167 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.
0
 

Author Comment

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

Sincere thanks and kind regards.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now