Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

CenterEdge Software: Couchbase and N1QL Security

DZone's Guide to

CenterEdge Software: Couchbase and N1QL Security

Looking for a way to deliver NoSQL to more developers? N1QL brings this functionality to more devs. See mroe aout N1QL injection, clause modifications, and security.

· Database Zone
Free Resource

Learn NoSQL for free with hands-on sample code, example queries, tutorials, and more.  Brought to you in partnership with Couchbase.

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:

var query = "SELECT * FROM users WHERE name ='" + userName + "'";

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

SELECT * FROM users WHERE name = '' OR '1'='1'

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:

var query = "SELECT * FROM users WHERE name = '" + userName + "' AND group = 5";

Could be injected with:

SELECT * FROM users WHERE name = '' OR 1=1 --' AND group = 5

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:

SELECT * FROM users WHERE name = 'blah'; DROP TABLE auditlog /*’AND group = 5

How Does This Affect N1QL?

After some experimentation, N1QL is actually more resistant to injection attacks than traditional SQL. 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.

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. However, batching could certainly be added as a new feature 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:

var query = "SELECT * FROM users WHERE name = '" + userName + "'";

Can become:

SELECT * FROM users WHERE name = '' OR '1'='1'

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

var query = "SELECT * FROM users WHERE name LIKE '%" userName + "%' AND group = 5

Still returns all users when it becomes:

SELECT * FROM users WHERE name LIKE '%' OR ''='%' AND group = 5

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:

var query = "SELECT * FROM users WHERE name = '" + userName + "' AND group = 5 /* only return group 5 */";

Can be injected with "OR 1=1 /*":

SELECT * FROM users WHERE name = '' OR 1=1 /*' AND group = 5 /* only return group 5 */

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.

var query = "SELECT " + field + " FROM users WHERE type = 'user'";

After injection becomes:

SELECT name, (SELECT * FROM users as users2 USE KEYS users.userPasswordDocumentIds) as passwordDoc FROM users WHERE type = 'user'

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.
    var query = "SELECT * FROM users WHERE userName = '" + userName + "'";
    Should be:
    var query = new QueryRequest("SELECT * FROM users WHERE userName = $userName");
    query.AddNamedParameter(“$userName”, userName);
  2. If you do insert user input strings into your query, always escape quotes.  Replace any instance of a single quote (') with two single quotes ('').
    var query = "SELECT * FROM users WHERE userName = '" + userName + "'";
    Should be:
    var query = "SELECT * FROM users WHERE userName = '" + userName.Replace("'", "''") + "'";
  3. 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.
    var query = "SELECT " + field + " FROM users WHERE group = 5";
    Should be:
    var query = "SELECT `" + field.Replace("`", "``") + "` FROM users WHERE group = 5";
  4. 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.
    var query = "SELECT * FROM users WHERE userName = '" + userName + "' AND group = 5 /* only return group 5 */";
    Should be:
    var query = "SELECT * FROM users WHERE userName = '" + userName.Replace("'", "''”) + "' AND group = 5"; // only return group 5

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.

The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:
security ,n1ql ,sql ,couchbase

Published at DZone with permission of Jeff Morris, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}