ASP Classic SQL Injection
This is only my introduction to the subject.
Expect this topic to have many pages.
Also expect me to post the actual scripts so you can test.
SQL Injection is not just for SQL Database servers anymore.
Poisoned Links was a good example of what URL Injection can do.
Damage your Search Engine Links completely.
I'm not perfect with my programming but, I try to manage my sloppiness which makes me a good programmer.
On This Page:
- I will cover my coding processes.
- I will use functions and show you working examples.
- I will give you reasons to code better.
Rule number one.
- Avoid using QueryString if at all possible.
- Avoid using the Internet all together.
The second rule isn't going to help you very much.
In my experience the first rule is a joke. But it was said years ago, "Avoid" is a word you can toss out the window.
- Encode your Strings
- Never let a string go unchallenged.
- Never think you will not be hacked, you will one day.
- Never think you are better than the scripters or the next programmer.
Let's get down to a few basics so you can start shaking you head.
- Numbers are numbers and should always be numbers.
- Letters are letters and will always be letters.
- Numbers and Letters mixed will be Alphanumeric and always that way.
- Symbols are Symbols and need Encoding unless it's an image Symbol.
If you have a query string that only uses numbers you're going to limit the type of injection.
If you use a query string that is limited in size and only numbers you reduce even more.
I wrote a one page website years ago. It was a learning project on how to use post and get to do all the functions a thousand or more static page website would do.
In fact, my project ended up developing a search method only used by one giant and native to just about all browsers.
If you were to type "www.xtremecompouter.com/where is my memory located in my computer" into any browser my website would then take the words "where is my memory located in my computer" and use the words as a key word search then form a query string unique to your search and offer you up a page just what you needed or as close as possible.
This practice was used online for eCommerce where you could just type a SKU, Make, Model, Description, Price, Color... you get the idea, just type in the address line and the site would do the rest.
This was a 404 Page Not Found Query String Script. You'll find one online today and I'll publish the one I wrote later.
Back to the SQL Injection: For the script above to work I had to clean up allot of queries to the site. I would monitor how the rogue scripts would hit my site and start looking for patterns. That worked for about 3 months, patterns then changed and my script didn't.
Limit length and buid your checksum into your query string.
I saw a site that had a v=123 in the URL on every page. It was the checksum needed to pass a page that had no querystring or form data.
Now, when you did have a form or query the number changed. It ended up changing to the length of the generated string. v=20&q=twenty%20%20characters
The design was sound, you cant inject something into something that is filled. But when you try you find you can. You needed to have something screen the querystring before the actual function that used the querystring.
Enter XCtM Project, which most of you have never heard of except for those that got caught up in it's web.
The XCtM Project was a pre-screening process of all querystrings to the site from both internal and external .
So if the v=20 was to tell the XCtM Defender Script to only allow 20 characters it would have to know which are valid characters and which are not.
Enter Encode / Decode for all strings... Now that you see we have a v=20 you can figure out that you need to hack with only 20 characters and by placing them in front with a space you're go to go.
If I then encoded the string and made the xctm=my_real_String then I can do more. Right?
It's complicated, but so are the scripts that hit your pages every minute of the day.
So now we have a layer of protection that is only reading length of the string and encoded by the layer for only it's internal use. Now we know if the string has been changed we'll just redirect to a hacked.asp page or a access.asp or banned.asp page.
Let's take the data we are going to use for a query string and make it into a query string.
Now the site has every string starting with x= which means x= needs to be decoded.
Request.QueryString("x") seems to be the logical path right?
What if, just for argument we made the query actually land into a database table and assigned the public query a numeric value.
I ran a site that would generate a new page for every page and offer a number to extract the page.
Imaging your whole website, forms, page names, post, all represented by a single number that could handle up to 4,800 characters in length.
Today we have Short URL's that do just that.
Why not do that for every page on your site?
/1 the default.asp page.
/2 the contact.asp page.
Ok, so we're back to the 404 page not found script in that you really don't have a page called "1" in ASP and you don't have a folder named /1/ either. You just have the number one, even if you typed 2 spaces after the / you still have 1.
This would be helpful and would work very well but you need to add a good old fashioned function to allow the 404 page not found page they need.
So let's say you use Alphanumeric as your pages which also are your query and search strings and all of them are stored internally so the only thing going out is /1TxTa and the only thing coming back is /1TxTa so how do you use this?
First, only Letters and numbers so we have a quick check, anything else? Exit and redirect.
What we are looking to do is generate the page from a clean, clear, never seen by the public string using a fixed alphanumeric pattern externally that can be masked as a folder name.
Actually one function I programmed created a folder for every query and saved the query to that folder. The folder name was recorded with a date/time stamp. The next visitor to the site would trigger a folder delete option every 60 minutes. Now I have a single page website that cleans up the pages it creates during the day.
Again, using the 404 Page Not found approach we can create a static page from database data and delete the page as if it was a cached page.
Your first function to reduce the injection is to always encode your server posts or query strings. Adding a checksum for encoded length will help reduce decode encode injection.
Now that we have the encapsulated string we need to make sure the data entered is correct.
I'll take one of my forms.
We have fields email, name, message, captcha. Now the captcha helps prevent successful submissions and people are very much used to seeing them and if you use an easy to read captcha you could do yourself a great big favor and use captcha on all your submit forms (GET) and query string (POST) posts.
- If you think for a minute that a 3rd party app or plug-in is going to protect your SQL from Injection and don't clean up your code and protect the native code you are a coding hack and will without question be hacked.
Got the picture? We need to make our ASP and SQL code as safe as possible then we can add layers to make the management feel good about the backend code. Something about not seeing that captcha scares them even if scripts can read them.
Let's create your first sample easy function.
Your main page will always start with:
Tip: If you use a variable within the function only and will never pass the variable outside the function you can declare it within the function to keep it easy to manage.
Objects vs. Variables in naming should have something to do with what you are doing.
<%@ LANGUAGE="VBSCRIPT" %>
<%Option Explicit %>
Dim strDeclareEverything, strObject, strObj, intPostLen
EncodePost = ""
If Len(str) = 0 Then
str = Trim(str)
intPostLen = Len(str)
str = str & "l="&intPostLen 'l = length checksum
str = EncodeWithSecretSauce(str)
EncodePost = str
What we are looking for here is a simple method of encoding a string with unlimited querystring options because we are taking it as one large querystring
Encoded the string now looks like:
This can be anything your brain creates, I'm only showing the basics. The offset is the ASC(II) Shift in this case and I'll call that MX1 but this can be a Salt if you would like to add flavor. If you're storing this to a DB and if it's starting as an URL you will need to encode it but I'll get into more of the actual coding in a minute.
This new string, created from your POST form is not sent as v=qdsafadqatskisielssaslidlsiwidlwwidhkcdq$1Dbnl$1Eu0$1Eina$2ECHC$2CI2E2CB5QWLK5MX1
Now your version of the API XCtM takes the frontend roll.
- Your site now only accepts querystrings with a specific value. This can be a random value of the day if it's a post form or it can be a static value. I have used both with success.
Static value: ABC123, Random: DateTime(), Random(Number) etc.
Now your first Request.QueryString("v") (or if you use a random you need to store that in a simple session Request.QueryString(Session("v"))... Be creative.
Now if your site sees a QueryString and it's not the QueryString variable you selected you can safely say it's not what you want and redirect to the base page by taking the Request.ServerVariables("SCRIPT_NAME") and even the Request.ServerVariables("HOST") and redirecting the entry.
Once again, the key is to control what is in your string and you now are doing it by encoding the string.
This practice now only helps QueryString Injection where you find the really stupid scripts that go like this.
/a.asp?a=-1+1 or a=-1,b=-1
When it's a random hit on your forms page that has nothing to do with your forms name you should only display your forms page like nothing happened removing the bad string and redirecting to the good one.
Here's a function I called LinkPoisonClean() (Poisoned Links)
It was a very popular ANTI-SEO trick designed to get your website "DE-LISTED" due to the Poisoned links offered up to the search engines. I'm sure it had other purposes like pissing the programmers off but it was really easy to handle.
We can call this function from the top of our page:
Dim strCount, strRedirectPage
If Len(str) = 0 Then
strRedirectPage = "/link_poison_control.asp"
strCount = strCount + 1
str = LCase(str) 'this also changes in the following can be done with vbcompare
If InStr(LCase(str),"/**/") >= 1 OR InStr(LCase(str),"version))") >= 1 OR InStr(UCase(str),"%2F**%2F") >= 1 OR InStr(UCase(str),"%40%40") >= 1 OR InStr(UCase(str),"%0A%0A") >= 1 OR InStr(UCase(str),"'") >= 1 Then
strCount = strCount + 1
If strCount >= 2 Then
Response.Status = "301 Moved Permanently"
This is the first script I wrote the day I noticed the poison scripts. It's now a Select with Remove and Redirect to a sitemap. It's harder to clean up a poison link than it is to completely get if out of your site. I have a category list that creates page names which is a SQL script, I can use a match method to find if the page is in the string and then redirect to a clean page.
Like I said, this is your introduction and you need to be creative, if you only copy and do not test, evaluate, test, reivew, test and test again your code you are not going to be good as a programmer. Anyone can type If Then Else End but only creative people can fill in the blanks and make website magic.
The VBCompare method does work very well and could be used to clean up things that are known to be an issue.
I have a simple function called CSIWords(str) here's the working version.
strCSIW = Trim(strCSIW)
'# Search Index Posion Control Code
strCSIW = Replace(strCSIW,"/**/or/**/1=@@version--))"," ",1,-1,vbTextCompare)
strCSIW = Replace(strCSIW,"/**/or/**/"," ",1,-1,vbTextCompare)
strCSIW = Replace(strCSIW,"@@version"," ",1,-1,vbTextCompare)
strCSIW = Replace(strCSIW,"--))"," ",1,-1,vbTextCompare)
strCSIW = Replace(strCSIW,"--)"," ",1,-1,vbTextCompare)
strCSIW = Replace(strCSIW,"/**/"," ",1,-1,vbTextCompare)
strCSIW = Replace(strCSIW,"1=))--"," ",1,-1,vbTextCompare)
strCSIW = Replace(strCSIW,"))1=-2d"," ",1,-1,vbTextCompare)
strCSIW = Replace(strCSIW,"1=)--2"," ",1,-1,vbTextCompare)
strCSIW = Replace(strCSIW,")1=--"," ",1,-1,vbTextCompare)
strCSIW = Replace(strCSIW,"1="," ",1,-1,vbTextCompare)
strCSIW = Replace(strCSIW,"version))--"," ",1,-1,vbTextCompare)
strCSIW = Replace(strCSIW,"%40%40"," ",1,-1,vbTextCompare)
strCSIW = Replace(strCSIW,"%2F**%2F"," ",1,-1,vbTextCompare)
CSIWords = strCSIW
You can do other types of function calls that replace or remove or trigger when something is spotted. Remember, your goal is to make every effort in code to protect your SQL database and everything you can do is more than what I have offered so far.
Now, as you can see the patterns were limited but while they poisoned links back in 2011 allot of search engines were indexing these bad links faster then the developers could write small cleaning scripts like the one above.
It's important that you know what scripts are being run on your site so you know what to check. Putting your head in a hole is only going to get your head in a hole and nothing more.
Here's one thing I put in all my ASP programming, Error.X controls.
For the Poison Links I had the Links encoded and sent to me. (Encoded will save your rear one day)
I often just have my email server send me the error but I also use other scripts to write to a text file that I can review during coffee breaks. The key is to know.
One very simple error handling script for ASP.
You can place this at the bottom just or just after the last close script of your db connection.
Ready? This is a big, big, BIG secret to knowing forced and program errors.
'# DEBUG #
If Err.Number <> 0 Then
strScript = Request.ServerVariables("SCRIPT_NAME")
strHost = Request.ServerVariables("HTTP_HOST")
If Len(Request.QueryString)> 0 Then
strQueryString = Request.QueryString
strQueryString = "?="&EncodePost(strQueryString)
strET1 = Err.number
strET2 = Err.Description
strET3 = strHost&strScript&strQueryString
strET4 = "Notes that might help, updated page 12-20-2014 added EncodePost to Error Code "
On Error Goto 0
'# DEBUG #
What you will see from the Error Trap above is this.
Error Number, Description and the Page with the Query if any.
That's all you should need to debug your code or in our case, create a new function or sub-routine to defend against bad query strings.
You might also just setup your site to have every query sent to you or to a report so you can see just how many bad strings are being sent to your site.
If you are a Media Partner you really need to focus on this because your poison links could actually be sending bad, very bad words in your URL.
Recap to what we have covered so far.
- We now know that we need to protect every GET and POST that leaves our pages before it reaches the database.
- We need to Sanitize anything we find that has been added to our GET or POST request.
- We need to Monitor what is happening.
- We need active live reporting of anything out of our design.
Let's look at some simple encoding pratices and I'll show you how it works in a function on a very basic form. Then it's up to you to write the 1,000 line code page that works on every GET and POST page under your control.
Note: This is without secondary 3rd party scripts, first master the code then add other code to help.
<form id="contact" method="post" action="/">
<input type="text" name="name" id="name" />
<button type="submit" >Send Message</button>
strName = Request.Form("name")
If strName = "" Or Len(strName) = 0 Then
'do nothing in this example
Call Function(Send Email Message)
If we want to store the message we need to think about how, I know an Italian programmer that came up with a simple FSO create text file on server function for his blog.
He only had internal connections to the DB and everything external was writen to a text document in a write but not read folder.
I think it was a great idea for comments.
To help you control what happens in that form let's look at what can go in the form....
EVERYTHING PLUS THE KITCHEN SINK!!!!
So let's limit things. Let's say we think names will not have numbers and if the Hyphen is used let's only allow it once.
arrChar ="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890- "
NameCheck = True
For i = 1 to len( input )
cZ = mid( input, i, 1 )
If ( InStr( arrChar, cZ ) = 0 ) Then
NameCheck = False
Can we spend up things? I bet but let's get the idea down then we can be creative and dremove the UPPER CASE and add our VBTextCompare to the function.
The idea here is to allow only characters you have in the array. This helps allot.
The VBCompare might look like.
If (InStr(arrchar,cZ,1,-1,vbTextCompare) = 0) Then
Later I'll help you setup your function timers so you know what speed your server is processing these scripts.
So you know that your server will only allow Letters with a hyphen and a space.
Anything else would be a FALSE and all bets are off. No Else needed here, you made the rules your form needs to work within the scope of your rules and functions.
Prior to your INSERT, UPDATE, SELECT you should make it a rule to have Functions that check to make sure your input data is matched to your table.
- Exit Function
- Exit For
- Exit Sub
- If (Value <> Value) _
The idea is to challenge the data as if you know every form is a type of injection.
The NameCheck(str) Function would look like,
If NameCheck(str) = True Then
If NameCheck(str) Then
What I do with Functions like the NameCheck(str) function is as follows.
- Check to make sure it's True before going to next step.
- If False then find out why.
- EMail a copy of the data to the developer with the page name
Once again I need to know why the post failed and by sending a copy via text email I am able to review the issue in nearly realtime.
What if it was a new type of SQL Injection string or a new Poisoned Link redesign? You wont know if you don't get the failed attempts.
Just like in any industry we have lazy people and lazy programmers will always think their code doesn't stink. But believe me, it does, and those that only blog parts will lead you down a path you might not enjoy.
Code is fun, believe me, I wouldn't do it for hours on end if it wasn't.
Before you go and test my pages here let's say this is a pure .Net site. I have other sites with different code running, I'll post many examples mostly about how to monitor your site and record things you need to record.
Notice that I'm using a form GET so you can create the querystring.
This method is for internally created server side querystrings which once put together can not be edited or the page will simply redirect. I'll be more detailed in the ASP SQL Injection Code Page.