Recently, an advisory was announced for the Drupal content management system (CMS) in relation to a security risk to all sites using the platform. This security flaw was in the form of a SQL injection attack against the Database Abstraction Layer (DAL) and consisted of allowing anonymous users to escalate their privileges and run database access scripts. The purpose of this blog post is to analyse what caused the security breach and how it can be prevented in the future.
What is a SQL injection Attack
SQL is the programming language that is used to communicate with a database. Content management systems use a database to store website content, structure as well as information about registered users. SQL injection attacks occur when a malicious user enters SQL into an entry field to be executed or run against a data-driven web application. This approach can be used to retrieve, insert, change or delete data in a web application’s database tables. SQL injection can only be used to exploit a pre-existing security flaw.
A Simple Example
Assume there is web page with a text input for a user to enter their user id for authentication purposes that links back to the following piece of server code:
SELECT * FROM Users WHERE UserId = " + txtUserId
This allows the user to maliciously enter “321 or 1=1” where 321 is an incorrect user id. This transforms the server code into:
SELECT * FROM Users WHERE UserId = 123 or 1=1
Because 1=1 is always true every record in the Users table of the database is output by this statement.
Defending against SQL Injection attacks
There are three basic methods of defending against SQL injection attacks. Firstly the simplest but least effective method of protection is to use input validation to check the input received is the correct type and length to be the input expected. It is also possible to detect certain characters and prevent them from being entered as valid input. The problem with this method is that everything that can possibly be used maliciously must be detected on every possible input; this is time consuming to implement and is potentially liable to leave exploitable holes. Another problem with this approach is that some input that can be used maliciously can also be completely valid in certain situations.
The best method of defence is to use parameterised queries and stored procedures rather than directly concatenating user inputs to form the SQL statements being executed. Simply put, using parameterised queries works in a similar manner to placing parentheses around an expression in an equation. For example, in the situation laid out above, “123 or 1=1” is compared against the UserId of each record of the table as a complete phrase rather than being compared separately.
Some Modern tools (ORM, LINQ) abstract away from writing SQL directly and protect from injection at the same time. DNN uses a mixture of parameterised stored procedures, LINQ and an object relational mapping tool (PetaPoco) to interact with the database, each of which offers an intrinsic level of protection against injection attacks.
What Caused the Drupal Breach
By all accounts the Drupal breach was caused by the introduction of a data access layer API specifically to protect against SQL injection attacks which enforced parameterised queries. However, this was placed on top of legacy code that allowed non-parameterised queries and resulted in an exploitable method that is explained in detail here. The exploit was then used in a variety of ways to take control of a number of Drupal sites.
Advantages of .NET over PHP
Defence against SQL injection is possible in both .NET and PHP through the use of parameterised queries. It is however arguable that the PHP method of providing this functionality is more unwieldy and prone to developer error than the .NET solution. This is exacerbated by the presence of many outdated/misinformed PHP tutorials across the web that do not support best practice.
A parameterised PHP query is built up over a sequence of steps. Firstly the base statement is prepared with placeholders left where the variables will eventually be injected. The placeholders are then bound to variables to complete the SQL statement. Finally the statement is executed. A simple example of this using PHP with PDO is shown below.
$stmt = $dbh->prepare("INSERT INTO Customers (CustomerName, Address, City)
VALUES (:nam, :add, :cit)");
This sequence can become very complex on more complicated queries (as happened with the Drupal exploit) which could lead to security holes being opened.
The ASP.NET solution can be encompassed in a single function call that, using object relational mapping, passes a single predefined object from which parameterised SQL is generated and executed behind the scenes.
var rep = ctx.GetRepository<Customer>();
In certain situations, inline SQL is still used; however, it is still encompassed in a single function call using the PetaPoco micro-ORM API.
var rep = ctx.GetRepository<Customer>();
Customer = rep.Find("WHERE CustomerName = @0 AND Address = @1", CustName, Address);
Obviously a lot of these factors come down to personal preference; however, the simplicity and cleanliness of using parameterised queries in .NET makes it much easier to prevent SQL Injection attacks.