SQL Injections? How are those still a thing?
A SQL injection is the art of modifying the structure of a SQL query in a way that was not intended by the developers. SQL injection is still one of the most dangerous vulnerabilities (e.g. in the top 10 vulnerabilities listed by the OWASP). The most important leaks can be caused by SQL injections since they allow for a full database access.
For more than 15 years SQL injections have been one of the biggest threats to web applications. But are SQL injections still happening today? The short answer is yes. The SQL injection hall of fame lists the most famous ones.
User inputs – for the best and the worst
Let’s consider the example of a website allowing users to rank cows based on their ID. To display the cow with the id 123, the URL would look like:
www.ratemycow.com/123
The corresponding SQL query, generated by the application, is:
Assuming the id field is injectable, anything a client would put in the id field will be found in the SQL query:
www.ratemycow.com/I’d prefer dogs
Would translate to:
This particular query is not valid from a SQL point of view and would trigger an error from the SQL server.
Remember that it concerns only the structure of the SQL query: modifying a string or integer (called literals in the SQL specification) is not an issue (think search or user data insertion / modification).
The ORM is everything
15 years ago, people were crafting SQL requests by hand. This was a hacker’s paradise. Nowadays, ORMs have become the norm, and SQL injections are a bit less common.
The ORM (object-relational mapping) is the layer that offers developers a high level and generic way to query databases. Common examples are:
- ActiveRecord for Rails
- Hibernate for Java
- SQLAlchemy for Python
- Sequelize for Node.js
- [insert your own here]
ORMs have made huge progress since their emergence, and they rely on the best security practices to interact with the database. Having access to the database tables definitions, they can often map the database fields to their types.
But ORMs are not perfect
Yet ORMs are not perfect: they keep growing in complexity. This has two consequences on security:
- like any piece of software, they have bugs, and some of them have security consequences:
- on Rails / ActiveRecord: https://groups.google.com/forum/m/#!topic/ruby-security-ann/WccgKSKiPZA, https://groups.google.com/forum/m/#!topic/rubyonrails-security/jILZ34tAHF4…
- on Java / Spring : https://pivotal.io/security/cve-2016-6652…
- on Python / SqlAlchemy: http://docs.sqlalchemy.org/en/latest/changelog/changelog_07.html#change-0.7.0
- they have a richer API which makes it more error prone for users needing to perform advanced queries;
One ORM that does a great job at escaping the resulting SQL by the underlying database driver is Django’s querysets. However, Django also gives developers the power to write raw queries or execute custom SQL. But as the documentation states, “These capabilities should be used sparingly and you should always be careful to properly escape any parameters that the user can control. In addition, you should exercise caution when using extra() and RawSQL.”
- some queries are typically written in wrong ways, such as the search (common injection in Rails): SELECT * FROM posts WHERE text like ‘%#{params[:search]}%’
Thus ORMs need to be updated, and their documentation is worth reading carefully! In practice, even if you and your colleagues are world-class engineers mistakes will be made, shortcuts will be taken, etc. Acknowledging this is already a first good step towards safer application development.
False positives are the enemy of security
A Web Application Firewall (WAF) is an HTTP proxy performing security checks. As such, they have full access to the HTTP request, but know very few things about the server they actually protect.
They use a pattern approach to protect against SQL injections. Web application firewalls are inspecting incoming request payloads and look for known strings. These are basically checking regular expressions from a base of known signatures. For SQL injections, they have to look for SQL statements of any kind, or known attacks. That’s very close to how antivirus software work.
A novel approach intended to reduce false positives is the libinjection approach (see some slides or the GitHub repository). This C library parses user parameters trying to find valid SQL substrings.
This approach is lacking one major thing: context. They cannot know, while inspecting packets raw bytes that it is going to be used in a SQL query, or in another context. This might be people talking about SQL. It might be code. It may have nothing to do with it.
A few years ago, while I was performing a penetration test (security audit) for a well-known bank, they reported issues with some of their customers, in the French city of Lyon, who were unable to register. Their common denominator was they were all leaving near the “Parc de la tête d’Or.” And “ ‘or” is a typical pattern used by Web Application Firewall for SQL injections. Their WAF was catching it, and returned a 500 error to the subscribing customer. That’s a basic example of why companies are turning off a lot of rules protecting against SQL injections.
Being WAF or Libinjection, the biggest problem with those traditional approaches is the lack of context when deciding to block a malicious request. This leads to false positives, time wasted in checking useless attacks and worse, turned off protections leaving applications insecure.
Retrieve more context
The main issue here was obviously the lack of context. Web Application Firewalls perform SQL injection checks, based on simple patterns, but does not know if a SQL query will be performed through the execution cycle. The type of database (MySQL, PostgreSQL, Oracle…) cannot be predicted, neither their configuration.
This context can be automatically detected by standing inside the application:
This brings much more data and context:
- database model;
- full SQL query;
- stack trace;
- decoded parameters (!);
- unciphered parameters (!!).
And obviously, the information that WAFs have are also available:
- IP address;
- full HTTP query;
- uncoded parameters (might be of use);
- ciphered parameters (hard to think about it here…).
Do more than pattern matching (it’s 2017 for god’s sake)
We have a SQL query, a database type, and user parameters. What more can we do?
For the purpose of this, let’s distinguish 2 important elements of the SQL grammar:
- a literal: the “scalar” elements of SQL, that’s a string or an integer.
- a statement: the “executable” parts of SQL, usually noted in upper case, such as SELECT, UNION, WHERE, ORDER…
The literals can be anything: user input, constant strings, pre-defined numbers… This is not related to the structure of the SQL query but allows to insert or search specific data. These parameters can be any string or integer.
On the other hand, statements define the structure of the SQL query. They allow specifying where to read the data, how to fetch it, order it…
To find a SQL injection we would then be looking at a SQL query for SQL statements coming from a non-trusted source.
Let’s say we have these user parameters – amongst others:
IF((@@version),SLEEP(1),193)
The question is, is-it a SQL injection?
We also have the full SQL query, so we can compare them:
Parsing this raw SQL query according to the server specifications (here, MySQL) will provide the following structure:
Two SQL statements have been injected: IF and Sleep.
Thus, the SQL query structure has been modified by untrusted parameters: this is a SQL injection.
What would have happened with the same user parameters, but with a correctly escaped SQL query -i.e. with a non-vulnerable code?
The user input would still have been detected:
But it is encoded as a string, and the SQL query looks for a string-like id.
Hence, parsing the SQL query only find a longer string, no new SQL statement is added:
The structure is not modified: this is not a SQL injection.
This protection method is one key component of Sqreen’s SQL injection protection. When an injection is detected, the request is not sent to the database, and an exception is raised. Sqreen also protects applications from NoSQL in that same manner.
Are you tired of unreliable application security solutions? Give Sqreen a trial or schedule your demo today.
