Towson University’s Outreach Blog

Timothy

Hi – welcome back to our discussion of SQL Injection, what it is and how to minimize an application’s exposure to this threat.  To quickly review from the previous article in November – “SQL Injection is a scheme whereby a hacker uses knowledge of database technology combined with flaws in a program’s design or construction to allow them a level of access to data that is beyond what was intended”.  This is accomplished by identifying user input that is going to be made into parts of a SQL statement that is created “on-the-fly” in the application and entering actual statements in the SQL language which the application unknowingly submits to the database for execution.

The article closed with the observation that there are three rules of thumb to make your database more difficult to access through a SQL Injection type of attack and promised examples of them were to come.

Minimize your exposure.
The single best way to keep an attacker from exploiting the dynamic SQL in an application is to simply not have any.  Most all contemporary database platforms such as Oracle, SQL Server, DB2, and MySQL support a type of function called a “Stored Procedure” (SP).  A stored procedure is a small program that is compiled and stored at the database.  An SP may expect input (known as parameters) which it uses as selection criteria and other input for a SQL statement.

In our context, the biggest difference between a stored procedure and Dynamic SQL is that with an SP, user input is not included in the syntax of a SQL statement that is submitted for execution.  This in itself is a significant deterrent to most SQL injection attacks.  Use of stored procedures can also allow a more robust error handling and easier front end maintenance.

For more detailed information on creating and executing stored procedures (in the SQL Server environment) check out this article at MSDN.

Examine user input.
There are numerous reasons for your programs to apply stringent validation rules to user input.  It just makes sense to do everything we can to make sure that any transaction that is submitted to the database is valid.  This gives us an opportunity to reject suspicious input before it gets submitted for storage or execution, which is the best time to deal with it.  To be effective, input must be examined in the context of what is expected.  If we are expecting the user to enter a basic “zip code” in a field, the field can be developed so it will reject any input that is not a number.  If the field is expected to contain a user’s name then input validation rules for the field will have to be looser, but we still have no reason to expect things like colons, or words like “ SELECT “ to be part of the input.

Be aware then input filtering is a sort of tug-o-war scenario between developers and would be intruders, with both sides keeping abreast of what the other side is doing and reacting to it.  To this end it is a good idea when possible to keep a log of both rejected and accepted user input so you can see what the intruders are up to.  Accepted input can be tracked through normal database log functions, but rejected input will have to have logic in the front end application to log the rejected material at the server for later inspection.

Don’t be too predictable.
The practice of obfuscating data by use of cryptic data and code structures is sometimes referred to as “security through obscurity”.   In a bygone day when the level of computer literacy in the general public was nothing like it is today, this sort of approach was all the security many systems had, or needed for that matter.  Although those days are long gone there is still a case that can be made for not giving database tables and fields names or formats that make it obvious that they contain sensitive information.  If an intruder is looking for a social security number and they find a database field that is named SSN, SocSecNum or anything similar they have had their task made much easier.  Even if the name was less obvious things like a length of 11 characters masked as “nnn-nn-nnnn” wouldn’t take very long to identify.  Imagine however if the intruder was confronted with a social security number that had been distributed between 3 fields that were 3, 2, and 4 characters respectively and were named “IDA”, “IDB” and “IDC”.  This is just a quick example but you can see how this sort of thing would complicate the intruder’s task.

Image Credit: David Castillo

Which Do I Use?
The short answer is, “all of them.”  They all work together to make your application smoother for the user, easier to maintain for the developers, and stronger in the face of potential intruders.

Another reality of data management is the periodic need to stand up to audits, both internal and external.  Whether you work for a public or private sector entity or if you are contractor or a an independent consultant, some of your designs and code will likely have to be audited.  Financial systems, critical records management systems such as those developed for the medical and insurance industry, and security applications can all be anticipated to be regularly audited.

If an application accepts user payments through credit cards (eCommerce) then it will be audited by, and held to standards developed and enforced by the Payment Card Industries (PCI).  These set required security standards for any merchant accepting credit card payments. Any eCommerce applications that you write will be subject to these standards .  Other outside organizations that impose security standards include HIPPA, State motor vehicle and social services records and many others.  Be aware of any security standards that may apply to your application and ensure that the applications design and implementation meet the requirements and that this compliance is documented and as easy to verify as possible.

Many auditors use automated scanning tools that can detect the presence of filtering code in the front end application, but do not have a view into the functions of stored procedures on the server side.  In order to facilitate these scans develop input filters and incorporate them into the front end application.  Other scans may access the database directly and require other sorts of security measures such as data encryption to be visible.

Don’t Turn Your Back On The Bad Guys
Cyber-Security is an ever evolving field.  The arena is filled with new technologies, system architectures and development tools which arrive almost daily, closing existing security gaps, and introducing new ones.  Stay informed. Use the information that you gather.  Periodically review your standard designs and development practices to makes sure nothing has changed to make them inappropriate.