Stored Procedures (and their vulnerabilities!) (Part 3)

Good afternoon everyone! Today's blog post is a continuation of Stored Procedures (Part 1) and Stored Procedures (Part 2), which I would strongly recommend on reading before reading this one.

As mentioned on Friday, we will be looking at the safe execution of Stored Procedures. I say the safe execution because it is possible to be vulnerable to what is known as SQL Injection Attacks, where a hacker will try to steal your data via the code used in the Stored Procedure. It is incredibly important to make sure that you code defensively to try and avoid such events occurring.

What are SQL Injection Attacks? 

SQL Injection attacks are a form of exploit when a hacker can steal vital data via SQL statements where a user input has not been protected properly. However not only can they steal data, they can use this data as an advantage to modify data, delete it and even compromise the whole database entirely. Every developers worse nightmare, right?

Bugs in programming code are one of the major flaws in software which allows hackers to misuse any information that they manage to retrieve, which can be catastrophic when dealing with private and confidential data.

Types of SQL Injection Attacks

There are many different types of Injection Attacks that a hacker can use, which I am going to detail more in this section.

Exploit Injection Attacks

An exploit attack involves the hacker focusing on the login page of the application to steal log in information such as usernames, email addresses and passwords in order to gain access to the database system. Quite often, they drop the user table to make it impossible for any user to log onto the system. They may also use access to the user table to modify one of the user's passwords and then gain access to all of their personal information, which could potentially contain private and financial details as well.

Error Based Injections 

An error based injection attack involves generating and manipulating error messages in order to discover the entire back end database structure, which would obviously help the hacker in compromising the system. The key problem here is when error messages are not tidied up properly, and therefore display tables and field names.

Union Based Injections 

A union based attack involves the hacker joining tables together to reveal even more information than what is stored within a simple table. This may be more difficult if the hacker has no idea what structure the database has, however if successful can have even more devastating effects.

SQL Command Injections 

SQL command injections allows a hacker to not only inject SQL commands into a system, but execute commands as well. This is why you must really protect your stored procedures as much as possible because if a hacker can manipulate them, you really are in big trouble as it can cause mass data loss, data corruption and even the loss of an entire database.

Blind Injection Attacks 

Blind Injection Attacks are usually random attacks used by hackers to determine the vulnerability of a web application. This may be the hacker's first port of call to see just how secure the system is and to determine what other attacks should be used in order to achieve their goal (whether it be just bring a system to a stop, drop tables or drop an entire database). Blind Injections are a trial and error approach as the hacker would need to use their imagination and guess.

Timed Injection Attacks 

A Timed Injection Attack is a method of attack that uses the BENCHMARK() operator to delay server responses if an expression will evaluate to be true. This is one way of a hacker discovering a password, one character at a time. Similarly to Blind Injection Attacks, Timed Injection Attacks rely heavily on trial and error.

What is Defensive Programming? 

Defensive programming is the art of protecting yourself against issues when creating a piece of software by formulating a list of all the possible scenarios on why a piece of software would not work in the way it has been intended before actually hard coding.

Defensive programming has three main goals:

• To improve the general quality of the software produced – therefore reducing the number of issues and bugs. 
• To make sure that the code works correctly and appropriately, not only with expected user inputs but also with unexpected user inputs (testing in unique conditions). 
• Make the source code comprehensible to a software audit. 

If you plan how to tackle vulnerability issues before they arise, your application will be much safer and reliable to use. Note that it is also vital to use some form of security testing software on your applications before they are sent to the customer, as the last thing you want is to compromise their data.

How Can Defensive Programming Prevent Attacks?

Unfortunately, any live database application is subject to attacks from hackers. But luckily, there are simple steps that a developer can take in order to prevent all of the above attacks from having a detrimental effect on your database.

Stored procedures can actually help prevent SQL Injection Attacks as they parameterise queries. This means that the developer has to define and pass in parameters which make it more difficult for an attacker to manipulate your queries for their own requirements. However, you will need to avoid using dynamically generated queries within the procedure itself, otherwise a vulnerability has been created and can be exploited. If you do need to use dynamic generation and execution however, you must ensure you adhere to the following:

Escape all User Input

Where escape characters are used around the query. The following blog post (not written by myself, so kudos to the owner!) describes escape characters (and other useful methods of preventing SQL Attacks): http://blogs.msdn.com/b/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx

Appropriate Validation

For usernames for example, make sure that the data type is only a certain length and that the text box entry on the web application form itself can only store a certain number of character or usernames with no spaces etc.

There are other ways for you to prevent attacks on other parts of your database system too:

• Turn off error reporting to minimise the risk of Error Based Injections 
• Modify the connection to the web server to only allow enough privileges for normal use.
• Ensure that configuration choices for the server are suitable.

To Be Continued...

Part 4 will focus on: 

• Cursors. Stay tuned!