Don’t trust outside data!
SQL injection attacks were once considered the greatest threat plaguing the Internet. They continue to be the source of many large data breaches today. This is due to the fact that these vulnerabilities are common, easy to exploit, and very rewarding to attackers. As a web developer, it is your responsibility to protect your clients and users from these threats.
Some of the largest data breaches in recent history were the result of SQL injection (SQLi) attacks. The impact on these victims can be quite severe. Users’ identities are compromised, credit card information is stolen, and companies spend billions of dollars repairing the damage caused by such attacks.
The Vulnerability
A SQLi attack is accomplished by providing an application with malformed user data that changes the meaning of the database command contained within the application. For example, imagine we have a website that a user can search for a colleague by name. These names are stored in the Employees table of a database within a single row.
Users can’t always remember the full name of the colleague they are attempting to find, so we make the lookup a fuzzy search. The search method in this Rails app could look like this:
def search
query = params[:search]
@colleagues = connection.execute("SELECT * FROM Employees WHERE name LIKE '%" + query + "%'")
end
Or, like this:
def search
query = params[:search]
@colleagues = find(:all, :conditions => "name LIKE '%#{query}%'")
end
Both of these examples are vulnerable to SQLi. This is because the query is taken directly from the user, and stuck in the middle of the SQL statement as it is. The application is trusting the user will never enter malformed input. This is a mistake.
The flaw can mean different things depending on the database being used. For instance, PostgreSQL will not execute multiple commands in ActiveRecord, however, it will execute the last command it is given. Therefore, if the second example above was used, and the search term entered by the user was:
'); DELETE FROM Employees --
The resulting SQL command would be:
SELECT "employees".* FROM "employees" WHERE (name LIKE '%'); DELETE FROM Employees -- %’)
The semicolon completes the first SQL command, the next command is run, then the double dash comments out the rest of the SQL in the code. This attack would successfully delete the entire contents of the employees table.
This sort of vulnerability can lead to more devastating attacks as well, limited only by one’s imagination. There are a number of techniques used by attackers today to penetrate databases. For example, the attack described above required the knowledge of a table name. To acquire this knowledge, the following search term can be entered in the form:
‘ OR EXISTS (SELECT 1 FROM Users) AND ‘’=’
When the search button is clicked, an error is returned from the application because the table “users” does not exist. When a valid table name is entered in the query, such as "employees", the application will not throw an error, and the attacker will have a valuable piece of information. There are several tricks such as this that leak information about the system, all made possible by SQLi vulnerabilities.
The Fix
This attack can be mitigated by replacing the vulnerable code with the following parameterized query:
def search
query = params[:search]
@colleagues = find(:all, :conditions => ['name LIKE ?', '%' + query + '%'])
end
Replacing the problematic conditions string with the array shown above, allows ActiveRecord to sanitize the user input and alleviate the SQLi problem. This is accomplished by escaping characters such as single quotes, backslashes, and others. As of the time of this writing, this sanitization occurs in active_record / sanitization.rb with help from active_record / connection_adapters / abstract / quoting.rb.
While sanitization is accomplished very well in Rails, it is rarely a perfect solution. As such, Rails 3.1 has adopted parameterized queries (also known as prepared statements) in the code shown above for SQLite and PostgreSQL databases. This means that the SQL statement is sent to the database with placeholders represented by question marks. The database parses the SQL statement, comes up with a query plan, then caches that query plan for future execution. It then passes a token back to the application as a reference for the given statement. When the application wants to execute that query with actual values, it passes the acquired token back to the database with the values that are to be plugged in. The database then executes the query and returns the results.
This provides security against SQLi attacks because the data supplied by the user is never treated as anything other than simple values. Because the query plan has already been established by the database, it cannot be altered by user supplied input. As a bonus, prepared statements provides significant speed improvements as well.
Each database handles prepared statements differently so this feature is implemented in the individual database adapters. For instance, MySQL does not support prepared statements in Rails. This is because MySQL does not do query planning, which results in slower performance when using parameterized queries. When looking for prepared statements with other databases, such as Oracle or DB2, the relative adapter/gem must support the feature.
C# and Java are just a couple of other languages that support prepared statements. These statements should be used as frequently as possible when communicating with a database with parameters, especially when user input is involved. It increases speed and greatly improves your application’s security.
Recent Rails Vulnerability
A new SQLi vulnerability has been discovered in all versions of Rails. While it appears to require a fair amount of star alignment for your app to be vulnerable, it should not be taken lightly. It is recommended that you update or patch your Rails as soon as possible. As Bruce Schneier reminds us, attacks only get better over time, not worse. The attention this new vulnerability received, highlights the seriousness of SQLi. It is good to see how quickly the Rails community reacted and that a patch is available.
Conclusion
Never trust any user supplied data. This includes query parameters, cookies, or form data. These values should never be placed within a raw SQL command. While most users will try to use your application appropriately, there are others that would love to abuse it every way possible for personal gain.
The current number of successful SQL injection attacks is disappointing. There is certainly no shortage of good information on the causes and cures for these vulnerabilities, yet, the problem persists. This is a fairly simple attack to protect yourself from, but your awareness is required. Pay attention to the code you write. Security matters.