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:
|
1 |
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:
|
1 |
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:
|
1 |
var query = "SELECT * FROM users WHERE name = '" + userName + "' AND group = 5"; |
Could be injected with:
|
1 |
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:
|
1 |
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. 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:
|
1 2 |
SELECT * FROM users WHERE name = ''; UPDATE users SET password = ‘1234’; SELECT * FROM users WHERE name = '' |
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:
|
1 |
var query = "SELECT * FROM users WHERE name = '" + userName + "'"; |
Can become:
|
1 2 |
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:
|
1 |
var query = "SELECT * FROM users WHERE name LIKE '%" userName + "%' AND group = 5 |
Still returns all users when it becomes:
|
1 |
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:
|
1 |
var query = "SELECT * FROM users WHERE name = '" + userName + "' AND group = 5 /* only return group 5 */"; |
Can be injected with “OR 1=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.
|
1 |
var query = "SELECT " + field + " FROM users WHERE type = 'user'"; |
After injection becomes:
|
1 |
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.
- 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.
1var query = "SELECT * FROM users WHERE userName = '" + userName + "'";
Should be:
12var query = new QueryRequest("SELECT * FROM users WHERE userName = $userName");query.AddNamedParameter(“$userName”, userName); - 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.
- If you do insert user input strings into your query, always escape quotes. Replace any instance of a single quote (‘) with two single quotes (”).
1var query = "SELECT * FROM users WHERE userName = '" + userName + "'";
Should be:
1var query = "SELECT * FROM users WHERE userName = '" + userName.Replace("'", "''") + "'"; - 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.
1var query = "SELECT " + field + " FROM users WHERE group = 5";
Should be:
1var query = "SELECT `" + field.Replace("`", "``") + "` FROM users WHERE group = 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.
1var query = "SELECT * FROM users WHERE userName = '" + userName + "' AND group = 5 /* only return group 5 */";
Should be:
1var 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.
