Solved

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

Posted on 2011-09-30
4
384 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
[X]
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
4 Comments
 
LVL 60

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 83

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

717 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