Note: this is a guest post by Brant Burnett of CenterEdge Software, a company which developes POS and specialty software for the amusement park, leisure and entertainment industries.

Overview

N1QL is an incredibly powerful new tool which will help to bring NoSQL databases to a wider pool of developers with a much shallower learning curve.  This will help developers create advanced, performant, and robust applications more quickly and easily than ever before.  But with any new technology, the surface area for hackers to attack is inherently increased.

SQL injection is a well known security flaw commonly found in SQL-based applications, and has been very well documented over the years.  So how does N1QL compare to SQL in terms of security?  Is N1QL vulnerable to injection attacks as well? If so, how can developers avoid these pitfalls?

A Review of SQL Injection

SQL injection is a form of code injection where the end user can add malicious code to SQL queries being run by your application. A simple example is this query:

If the developer doesn’t take steps to protect their application, the user may include malicious text in the userName field. For example:

This query results from the user inputting “‘ OR ‘1’=’1”. Now the query will return all users in the system to the malicious user.

To allow more powerful query alterations, the malicious user might also use comments to exclude part of the developer’s query. Extending the previous example:

Could be injected with:

Since SQL will ignore all text after “–“, the restriction that group must be 5 is now removed from the query. Once again, all users in the system are returned to the malicious user.

The user might also combine the comments with batch commands to alter data in your database:

How Does This Affect N1QL?

After some experimentation, N1QL is actually more resistant to injection attacks than traditional SQL. For example, N1QL doesn’t currently support batching multiple commands. Therefore there is no equivalent to the batch attacks that allow malicious modifications of data in SQL. For example, this injection attack, which could work in SQL, is rejected as invalid syntax:

However, there are still options for a malicious user to perform an attack. Without protection these attacks could result in allowing access to secured data, or denial of service because the altered queries use too much processing power on the Couchbase cluster.

Additionally, some features such as batching could certainly be added in a future version of N1QL. So if developers don’t protect user input in their queries data modification could become a problem in the future.

Where Clause Modifications

As with SQL injection, N1QL injection allows the alteration of the WHERE clause. For example:

Can become:

Due to operator precedence rules for the AND and OR operators, this attack can even work if there are additional clauses:

Still returns all users when it becomes:

N1QL Comments

N1QL’s comment system uses C style comment blocks (/* comment */) instead of using “–” to comment out the remainder of the line. This protects N1QL from some of the more advanced injection attacks. Since N1QL requires a closing comment */, attackers can’t comment out parts of your query without causing a syntax error.

Note, however, that this depends on the developer not leaving comments in their query. If there is a comment in the query text, the user now has a closing comment block to use to their advantage:

Can be injected with “OR 1=1 /*”:

As in the SQL example, the group restriction is now removed from the query.

N1QL Identifier Injection

Couchbase’s schemaless document model actually creates an interesting new area of attack. When working with SQL, it’s very rare to include user input anywhere except the WHERE or ORDER BY clause of your query. This is because the table and column names are well known and don’t change.

The lack of a schema for Couchbase documents, however, means that developers might be tempted to allow the user to control which fields they’re selecting from the document.

After injection becomes:

Now the attacker has access to data from a related password document that was not in the user document the developer specified.

How To Protect Your Application

Fortunately, it’s just as easy to protect your application from N1QL injection attacks as it is from SQL injection attacks. Here are some guidelines that make security easy. The examples are in C#, but the concepts apply just as well to any other language.

  1. Best practice: Instead of inserting user input directly into your query, used named or positional parameters as protection. This way user input is never directly added to your query, providing 100% protection against all injection attacks.

    Should be:
  2. Best Practice #2: Use a strongly typed language construct, such as .Net POCOs or Java POJOs, that generate the query text. For example, the Linq2Couchbase library (https://github.com/couchbaselabs/Linq2Couchbase) handles proper escaping when generating N1QL from LINQ queries.
  3. If you do insert user input strings into your query, always escape quotes.  Replace any instance of a single quote (‘) with two single quotes (”).

    Should be:
  4. When inserting user input identifiers into your query, always escape the identifier with ticks (). Then replace any instance of a tick in the input with two ticks (`). Note that there is no named parameter equivalent for identifiers, so escaping is the identifier is the best solution.

    Should be:
  5. If you implement the other rules, you’re protected against comment based attacks as well.  However, a secondary policy against comments in queries that contain user input can provide additional protection in case a developer forgets the other rules. Instead, just put any comments in application code instead of the query itself.

    Should be:

To see examples of these attacks and their protection methods in C#, please see this GitHub repo: https://github.com/brantburnett/N1QlInjection. Note that you will need Couchbase installed locally and with beer-sample installed to run the tests.

Conclusion

While N1QL is vulnerable to injection attacks, this vulnerability is no worse than well known vulnerabilities in SQL. Additionally, it is very easy for developers to protect against injection attacks. Therefore, N1QL provides an excellent platform for developing secure applications using Couchbase NoSQL databases.

Author

Posted by Jeff Morris, Senior Software Engineer, Couchbase

Jeff Morris is a Senior Software Engineer at Couchbase. Prior to joining Couchbase, Jeff spent six years at Source Interlink as an Enterprise Web Architect. Jeff is responsible for the development of Couchbase SDKs and how to integrate with N1QL (query language).

Leave a reply