Testing for SQL Injection Vulnerabilities

SQL Injection attacks pose tremendous risks to web applications that depend upon a database backend to generate dynamic content. In this type of attack, hackers manipulate a web application in an attempt to inject their own SQL commands into those issued by the database. In this article, we take a look at several ways you can test your web applications to determine whether they're vulnerable to SQL Injection attacks.

Automated SQL Injection Scanning

One possibility is using an automated web application vulnerability scanner, such as HP's WebInspect, IBM's AppScan or Cenzic's Hailstorm. These tools all offer easy, automated ways to analyze your web applications for potential SQL Injection vulnerabilities. However, they're quite expensive, running at up to $25,000 per seat.

Manual SQL Injection Tests

What’s a poor application developer to do? You can actually run some basic tests to evaluate your web applications for SQL Injection vulnerabilities using nothing more than a web browser. First, a word of caution: the tests I describe only look for basic SQL Injection flaws. They won't detect advanced techniques and are somewhat tedious to use. If you can afford it, go with an automated scanner. However, if you can't handle that price tag, manual testing is a great first step. 

The easiest way to evaluate whether an application is vulnerable is to experiment with innocuous injection attacks that won't actually harm your database if they succeed but will provide you with evidence that you need to correct a problem. For example, suppose you had a simple web application that looks up an individual in a database and provides contact information as a result. That page might use the following URL format:

 http://myfakewebsite.com/directory.asp?lastname=trehern&firstname=jeff 

We can assume that this page performs a database lookup, using a query similar to the following:

 SELECT phone

 FROM directory

 WHERE lastname = 'trehern' and firstname= 'jeff' 

Let's experiment with this a bit. With our assumption above, we can make a simple change to the URL that tests for SQL injection attacks:

 http://myfakewebsite.com/directory.asp?lastname=trehern&firstname=jeff'+AND+(select+count(*)+from+fake)+%3e0+OR+'1'%3d'1 

If the web application hasn't been properly protected against SQL injection, it simply plugs this fake first name into the SQL statement it executes against the database, resulting in:

 SELECT phone

 FROM directory

 WHERE lastname = 'trehern' and firstname='jeff'

 AND (select count(*) from fake)> 0

 OR '1'='1'

 

You'll notice that the syntax above is a little different than that in the original URL. I took the liberty of converting the URL-encoded variable for their ASCII equivalents to make it easier to follow the example. For example, %3d is the URL-encoding for the '=' character. I also added some line breaks for similar purposes.

Evaluating the Results

The test comes when you try to load the webpage with the URL listed above. If the web application is well-behaved, it will strip out the single quotes from the input before passing the query to the database. This will simply result in a weird lookup for someone with a first name that includes a bunch of SQL! You'll see an error message from the application similar to the one below:

 Error: No user found with name jeff+AND+(select+count(*)+from+fake)+%3e0+OR+1%3d1 trehern! 

On the other hand, if the application is vulnerable to SQL injection, it will pass the statement directly to the database, resulting in one of two possibilities. First, if your server has detailed error messages enabled (which you shouldn't!), you'll see something like this:

 Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

 [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'fake'. 

 /directory.asp, line 13

 

On the other hand, if your web server doesn't display detailed error messages, you'll get a more generic error, such as:

 Internal Server Error

 The server encountered an internal error or misconfiguration and was unable to complete your request.

 Please contact the server administrator to inform of the time the error occurred and of anything you might have done that may have caused the error.

 More information about this error may be available in the server error log.

 

If you receive either one of the two errors above, your application is vulnerable to SQL injection attack! Some steps that you can take to protect your applications against SQL Injection attacks include:

Implement parameter checking on all applications. For example, if you're asking someone to enter a customer number, make sure the input is numeric before executing the query.

Limit the permissions of the account that executes SQL queries. The rule of least privilege applies. If the account used to execute the query doesn't have permission to execute it, it will not succeed!

Use stored procedures (or similar techniques) to prevent users from directly interacting with SQL code.

SQL Injection

The vast majority of databases in use today have some form of web interface, allowing internal and/or external users easy access through familiar browser software. If you're security-conscious, you've undoubtedly spent a significant amount of time setting appropriate security permissions on your databases and web servers. Have you also considered the security of the code that powers the database-web interface? 

One common type of database attack, the SQL Injection, allows a malicious individual to execute arbitrary SQL code on your server.  Let's take a look at how it works by analyzing a very simple web application that processes customer orders. Suppose Acme Widgets has a simple page for existing customers where they simply enter their customer number to retrieve all of their current order information. The page itself might be a basic HTML form that contains a textbox called CustomerNumber and a submit button. When the form is submitted, the following SQL query is executed: 

SELECT *

FROM Orders

WHERE CustomerNumber = CustomerNumber 

The results of this query are then displayed on the results page. During a normal customer inquiry, this form works quite well. Suppose John visits the page and enters his customer ID (14). The following query would retrieve his results: 

SELECT *

FROM Orders

WHERE CustomerNumber = 14 

However, the same code can be a dangerous weapon in the hands of a malicious user. Imagine that Mal comes along and enters the following data in the CustomerNumber field: “14; DROP TABLE Orders”. This would cause the following query to execute: 

SELECT *

FROM Orders

WHERE CustomerNumber = 14; DROP TABLE Orders 

Obviously, this is not a good thing! There are several steps that you can take to protect your server against SQL Injection attacks:

Implement parameter checking on all applications. For example, if you’re asking someone to enter a customer number, make sure the input is numeric before executing the query. You may wish to go a step further and perform additional checks to ensure the customer number is the proper length, valid, etc.

Limit the permissions of the account that executes SQL queries. The rule of least privilege applies. If the account used to execute the query doesn’t have permission to drop tables, the table dropping will not succeed!

Use stored procedures (or similar techniques) to prevent users from directly interacting with SQL code.

As with many security principles, an ounce of prevention is worth a pound of cure. Take the time to verify the code running on your servers before disaster strikes!

Some Thoughts

I am often plagued by brief moments of clarity.  Why does man obsess over what he cannot have?  Why does man perpetually chase what he can never catch?  Is it the never ending struggle to achieve the unattainable or is it rather the human condition that craves the constant pain and agony of disappointment?  This is the question that must be, but can never be answered.  This is the truest form of irony.  

Is this the way the animal brain works?  Always searching for what it cannot find?  Can it not be said then that it is not the object for which we seek that is paramount.  It is however, the journey itself.  For in endeavors such as these, man can truly know himself.  He can learn his limitations.  He can learn how he acts in times of extreme pressure and sadness.  In this self awakening, he will find his true self and therefore his path maybe shown to him.  

It has been said that we only appreciate happiness because we have been sad.  We can only appreciate goodness because we have known evil.  If this is the case, then let us not frown upon times of heartache.  Let us instead intricately examine every facet of these emotions, no matter how painful it may be, so that we may better appreciate times of brevity and happiness.  Using this same logic, the person that has known the most heartache is the one that can know the most joy. Let us pray for the joyous times.   

God is a God of order and balance.  Both sides of the equation must equal each other.  Good and bad, Yin and Yang.  I have had a large amount of darkness.  I am ready for the light.  I have been in darkness for so long that I don't know how to enjoy the light.  This is a sadness.  Our world is in such turmoil and chaos that it affects every living thing.  Some are more aware than others.  Some are aware of the world behind the world.  Sometimes I envy the ignorant. 

Should I use Office 2011 for Mac?

Office 2010 won me back as a power user after Office 2003 stunk, Office 2007 was good but not great, and both OpenOffice and Google Apps had become quite compelling. Office 2010 was just so powerful and feature-rich that it was hard to ignore. Today’s launch of Office 2011 for the Mac brings most of this richness to an incredible growth platform, but somehow feels anticlimactic.

Don’t get me wrong. I’m happy not to have to launch a virtual machine every time I want the latest and greatest Office features on my Mac. Office 2011 is a great product like its Windows big brother. I’ve been using a press copy for a couple of weeks now and have been cranking out the PowerPoints, spreadsheets, and documents. I know that Outlook and Messenger are important to some people and have been both well-reviewed and are welcome additions. I’m a Gmail guy and tend to avoid mail clients like the plague, so these are non-issues for me, but bringing Outlook to the Mac platform was obviously a great business choice for Microsoft and one more pathway through which Macs will work their way into the enterprise.

And yet…

Even with the return of macro support and a revised interface that makes Office 2011 consistent with Office 2010, while still feeling more Mac than Windows…

Even with welcome updates that highlight just how much of an afterthought Office 2008 was…

Even with great templates and enhanced number-crunching abilities…

I just can’t get excited about Office 2011.

I know that Office remains the tool of choice for documentation, presentations, desktop data management, and communication for many businesses. I get that and I know that it isn’t going anywhere anytime soon in the corporate world. All the more power to the Mac for now being able to be seamlessly compatible with Office for Windows, both in file types and generally in user interface. And I don’t even completely disagree with a Gartner analyst who raved about the software:

“This is the best Office ever, not just on the Mac,” said Gartenberg. “It brings the Mac version to parity with the Windows version, but it still feels like Mac software, not a Windows port. Mac Office doesn’t feel like you walked into your house in the dark and someone rearranged all the furniture.”

The exclusion of OneNote, one of Office 2010’s best bits of software, as well as Microsoft’s failure to put Access on the Mac makes the “best Office version ever” assertion a bit dubious, although the interface and usability are quite good.The real question, though, aside from Mac users desperate to use their computers in corporate settings where Office remains the norm, is how many average Mac users will care that Office 2011 is just so incredibly dandy?

Mac users, after all, have a solid alternative on the cheap from Apple in their iWork software for most productivity and I’m actually finding myself more likely to produce everything from publication-ready documents to presentations in Adobe’s CS5 (and now Acrobat X) given the really powerful tools with the Creative Suite that Office simply can’t match. If what I need to do in terms of documentation or presentation can’t be handled in Google Apps, then I’m going to go whole hog and produce something really rich. Add in Adobe’s new ROME beta and there are more than enough alternatives for us stereotypical Mac creative types to just not be too fussed over Office 2011.

Aside from Adobe or Google, so much of what we (whether “we” means artsy Mac people or the world at large doesn’t really matter here) do ends up in a content management system, blog, or online somewhere makes Office 2011, no matter how good it is, just a little bit less spectacular than one might expect.

Because Office 2008 was such a weak product, and because Macs have traditionally been the tools of choice for designers and (more recently) developers, Mac users have grown accustomed to using “Not Office,” whatever that might be. Office 2011 is a boon for enterprise and business users, but won’t be the runaway hit that Office 2010 was. There just isn’t the need for it. We use Macs for a reason after all, and that reason usually isn’t to type Word documents, no matter improved the interface.

Something Different

I know that most of my blogs are about techie stuff and programming techniques.  However, tonight I am inclined to write about something completely different.  This really isn’t going to follow any particular writing discipline and this post will pretty much just be me getting some things off my chest for my own benefit.  

First, yes I admit it.  I am a recovering angry youth.  I wear too much black I have an inner adolescent that still wants to flip someone the bird when they make me mad or tell me what to do.  I often feel like I can’t live without music.  Although most people will not call what I listen to music.  Its mostly dark gothic rock with some occasional jazz, classical, opera, and blues mixed in.  I suppose in a nutshell I am somewhat of a goth freak.  Oh well thats me and those of you who do not like it please stop reading this blog NOW!

Ok.  There have been a lot of things going on in my life lately, some good, some bad, and some just dangerous!  At any rate, life for me is usually just one big suck fest after the other with brief periods of happiness mixed in.  

One thing am I struggling with is that people just always seem to use me.  They either want me to do something for them, fix something for them, create something for them, or (and my biggest issue) think for them!  Needless to say I hate doing that for the most part.  There are a few people that I don’t mind helping because they deserve it and I just like them.  

I often wonder if I am depressed or just crazy.  I constantly listen to depressing music.   Yes I listen to HIM all the time.  I like the dark and hate the sunlight, I often feel overwhelmed with everything that goes on, and I have a HUGE problem trusting people. HELP!!!! So I have come up with a few thoughts about this.  

There are a few basic existential realities we all confront: mortality, aloneness and meaninglessness. Most people are aware of these things. A friend dies suddenly, a coworker commits suicide or some planes fly into tall buildings-these events shake most of us up and remind us of the basic realities. We deal, we grieve, we hold our kids tighter, remind ourselves that life is short and therefore to be enjoyed, and then we move on. Persistently not being able to put the existential realities aside to live and enjoy life, engage those around us or take care of ourselves just might be a sign of depression.

We all get sad sometimes, struggle to fall asleep, lose our appetite or have a hard time focusing. Does this mean we are depressed? Not necessarily. So how do you know the difference? The answer, as with most psychological diagnoses comes down to one word: functioning. How are you sleeping and eating? Are you isolating yourself from others? Have you stopped enjoying the things you used to enjoy? Difficulty focusing and concentrating? Irritable? Tired? Lack of motivation? Do you feel hopeless? Feel excessively guilty or worthless? Experiencing some of these things may be a sign of depression. 

Depression can range from mild to severe. People sometimes minimize how they are feeling by saying, “anyone would feel this way in this situation” or “it isn’t like I want to kill myself”. You don’t have to be suicidal to be depressed but is a symptom of depression. Thinking a lot about death or wanting or even planning how you might die is serious and needs immediate attention. Call a friend, a crisis center, your doctor, call 911 or even show up at an ER.

Doing those things are easier said than done for those of us that are depressed.  Seeking help (for me at least) is like an admission of failure.  Like I am admitting that I can’t deal with “it”.  Whatever “it” is.  At any rate, I hope this post lets someone know that you’re not alone and other people feel the same way you do so don’t give up. 

I have some friends that I could talk to although at times I feel like I am unnecessarily bothering them.  I have one friend that knows me better than anyone but our relationship has been strained recently and I don’t know how to fix it.  I hope this person knows that they are very important to me and that I feel guilty because I feel the way I do (depressed).  

It is hard to communicate feelings to another person, at least it is for me.  This person however, has been very understanding and that is something you won’t find in many people.  However, and I regret to say this, I feel that our friendship might be coming to an end and for this I am very sorry.  I hope I am wrong and I suppose that only time will tell.  I hope that I can find out soon.  I am not sure I can live without this person.

Goodnight and I wish everyone  a great day tomorrow.  

Create Service-Stopping Batch Files to Optimize Your PC for Specific Tasks

You may have lots of software installed on your PC, but you don't need it running all the time. If you want to save some system resources, or just create a distraction-free environment in one click, a simple batch file can help.

First I'll walk through how to create a batch file for anyone unfamiliar with the handy Windows scripts, and then explain how to use some simple commands to start or stop resource-draining services or kill distracting applications.

Creating a Batch File and Starting Applications
To create a batch file, all you need to do is create a new, plain-text file with the .bat extension, and enter any commands in the file the same way that you would use them on the command line. You then execute the batch file (by, say, double-clicking it), and it'll run through the commands in the order you've written them into the file. Simple enough?

If you wanted to start a new instance of an application you should precede it with the start command. For example, to create a batch file that would start Notepad and open a new Explorer window that shows a certain folder, you could put these two lines into the batch file:
start notepad
start explorer c:\path\to\start

The batch file displays each command as output on the console, so if you would like to prevent that you can add the @ symbol before the command to eliminate extra output, or you can simply put this line at the top of the file:

@echo off

Now that we've got a few simple batch-file basics out of the way, lets move on to more useful batch file tasks. (Remember, you can copy and paste any of the commands below into a plain text file, save it with the .bat extension, and you've got yourself a batch file.)


Stop or Start Services in a Batch File
Many applications these days install a bunch of supporting services that help the application, but they really don't need to be running all the time. On my system, I've found this is especially true for virtual machine software, which often installs a bunch of heavy services that use a lot of extra resources. If you aren't running a virtual machine all the time, they really don't need to be running.

What we can do is create a batch file that stops the services, and another batch file that starts them back up. To do this, you can simply use the net command with start or stop as the first parameter, and then the name of the service after it. You can either use the service short name, or the display name in quotes, like one of these two examples:

net stop wuaserv
net
stop "Windows Update"

You can find the service names easily enough by opening the Services tool (use your Start menu search to find Services and run it) and double-clicking on one of the services. You'll see the short service name highlighted in the example below:

(For another way to look at the services that are currently running on your system, pull up the Windows Task Manager (Ctrl+Shift+Escape) and click the Services tab.)

You can start the services again by using the opposite command, like so:

net start wuaserv

Note that if you're using Windows 7 or Vista and you've still got UAC enabled, you'll need to run the batch file as administrator to actually stop the service. You can create a shortcut to the batch file and specify to always start as administrator in the shortcut properties to eliminate having to right-click every time.


Kill Applications with a Batch File
While stopping services is helpful to free up some system resources, you'll be able to free up a lot more resources by killing applications that don't need to be running—which can also be very helpful in killing distracting notifications and the like when you really want to focus. For instance, if you really should be writing that paper, you could have a KillDistractions.bat file that turns off everything else other than your preferred text editor.

To kill an application from the command line or a batch file, all you have to do is use the taskkill command with the /IM parameter, which matches the image name column from Task Manager—which is really just the file name of the application executable. For instance, to kill Notepad you would use the command like this:

taskkill /IM notepad.exe

This command by default will simulate clicking the red X in the corner of the window, so you'll be prompted to save your work if necessary. If you wanted to kill applications instantly without saving anything, you could use the /F parameter to force kill instead, like this:

taskkill /F /IM notepad.exe

taskkill /F /IM chrome.exe
You've got plenty of other parameters to choose from with the taskkill command, which you can browse with the /? parameter (i.e., type taskkill /?).


Create a Shortcut to Start the Batch File
Now that we've walked through the basic commands you'll need to create a batch file that starts or stops all the services and applications we don't need running, we can make the last line of the batch file start up the application that we're planning on running, and then customize the shortcut to start the batch file minimized so the Command Prompt window isn't flashing on the screen. So right-click your desktop or in any Explorer window, go to New -> Shortcut, and point it toward your batch script.

If you click the Advanced button on the shortcut window, you'll be able to specify to run the application as administrator if necessary. If you're stopping services in the batch file, you'll need to use this option, though you should note that any applications you start in the batch file will also be started as administrator. Of course, if you've disabled UAC, this won't matter either way.

Putting It All Together
Now that you know how to stop services, kill applications, and create the proper shortcuts, it's time to put it all together into a useful combination. Here's an example script that I use to kill distractions when I'm ready to switch into writing mode, but you could customize this to fit anything you might need.

taskkill /IM tweetdeck.exe
taskkill /IM chrome.exe
taskkill /IM firefox.exe

taskkill /IM pidgin.exe
taskkill /IM notepad.exe

Since I often use virtual machines to do my testing, I've also created batch files that start and stop the services when necessary to make sure that I'm only wasting resources when I actually need the virtual machines running. To stop the services, I've created a file called stopvmware.bat, though I've also set all these services to manual startup, so I only need to use this after I close VMware.


net stop VMAuthdService

net stop VMnetDHCP
net stop "VMware NAT Service"

net stop "VMUSBArbService"

Then when I need to start VMware again, I can simply use my startvmware.bat file, which starts up the services and then launches the VMware application.

net start VMAuthdService
net start VMnetDHCP
net start "VMware NAT Service"

net start "VMUSBArbService"
start "C:\Program Files (x86)\VMware\VMware Workstation\vmware.exe"

You can customize these scripts to do anything you might want, and tailor them to your environment, but this should give you the tools you need to get started creating your own time-saving batch files.

Securely Delete Files in Mac OS X

    I want to share a little known fact about Mac OS X with you but first I want to explain why this is important.  In today’s world, there are identity thieves around every corner just waiting for the chance to steal your personal information.  In addition, the theft of computers, especially laptop computers, is at an all time high.  Therefore, it is important that each of us remain vigilant against this threat. 
   
    Hard drives store data magnetically.  Meaning that if you simply delete a file, that file remains on your hard drive until it is overwritten.  This may be a matter of days or months depending on how you use your computer.  Mac OS X provides a way for you to easily and securely delete your files when you empty your trash can.  Here is how to do it.

    After you place the “deleted” files into your trash can, open up the finder preferences tab and find a check box that says securely delete files.  Click on that and now every time you empty your trash can, Mac OS X deletes your files and then writes random data over the area of the hard drive where those files lived rendering it virtually impossible to recover.

    Yes, I know this is an overly simplified explanation but I believe it will suffice for our purposes here.  If you want to learn more about how computers store data, visit this link http://www.ontrackdatarecovery.co.uk/data-recovery-articles/how-data-recovery-works/

    Thanks for taking the time to read this post and I hope you remember to take computer security seriously!

Adding to text fields together in Microsoft Access

I have a very short post for you guys today but it can save you lots of time.  I needed to add a person's last, first, and middle names together in one field for a report.  Each one of these is a separate field in my database.  I accomplished this by creating a query and adding all the appropriate fields that I needed.  Next, I clicked in a blank field in the query designer and added the following line of code. 

Full_Name: [Name_Last] & "" & ", " & "" & [Name_First_MI] & "" & " " & "" & [Name_Middle]


This concatenated all these fields together with a comma and a space after the last name and a space after the first name.   After that it was easy to create the report based on this query.  It looks great and I have adapted this technique to other data types as well. 

I hope this will be useful to you in the future. 

Internet Explorer 9 Beta

Today in San Francisco, Microsoft will officially unveil Internet Explorer 9 and make it available to the general public. It is, without question, the most ambitious browser release Microsoft has ever undertaken, and despite the beta label it is an impressively polished product.

For the record, I haven’t used Internet Explorer in years.  I usually stick with Safari on my Mac and Firefox on my PC.  However, with the release of IE9 I might have to change my default browser back to IE. 

The new IE has a greatly improved javascript engine and it renders HTML much better than previous versions.  It is also embracing HTML5 which is something IE has needed to do for sometime now. 

The biggest difference to me is the UI.  It is very minimal and there is almost no branding beyond the logo on the task bar.  This browser focuses more on the content of the web page than on the browser. 

At any rate, you should check out the beta version for yourself and give it an honest try.  It may win you back to the Internet Explorer users group!

Keyboard Shortcut for Access 2007

I have a small tip for you Microsoft Access people out there.  If you want to make a keyboard shortcut for a button click event or a shortcut for some type of an event in a form, I have a simple way to accomplish this. 

Use the “&” sign followed by the key you want to use as the shortcut after the caption definition and there you have it.  For example, if you have a button with a caption called Close you can create the shortcut by typing the “&C” after the word close.  After you have completed that you should notice the letter C on the button is underlined.  This means typing the letter c on your keyboard will activate the button.  Now you have a keyboard shortcut.  Have fun and don’t forget to comment!

If you have any topic ideas, or you have an article that you want to submit for posting on this blog, email me at jeff.trehern@gmail.com

Thanks!

Jeff

Welcome!

    Hello and welcome to my new blog.  Since this is my first post I wanted to write a little something to introduce myself.  My name is Jeff Trehern and I am a 31 year old IT professional.  I have a bachelor’s in Information Systems Security and I currently work for a local government.  Some of my responsibilities are to develop database driven applications, maintain current and develop new network infrastructure, and create and enforce information security policies. 

    This blog will primarily focus on technology driven subjects that will range from the beginning home user to the advanced application developer.   If at anytime you have an idea for a topic or just need some advice on a topic, feel free to write me at jeff.trehern@gmail.com. 

Thank you for taking the time to read this.  More is on the way soon!