N1SQL Injection prevention via parametric statements

query
n1ql

#1

N1QL injection attacks appear to be possible: (e.g. https://github.com/brantburnett/N1QlInjection)

I’m interested in using the Go SDK as a couchbase client in an API I’m working on (https://godoc.org/github.com/couchbase/gocb).

The Go sql package (for instance) provides a means for driver implementers to escape user provided values by way of the separation of a parametric query string and the user provided values like so: Exec(query string, args ...interface{}). Is it possible to do something similar with N1QL using the gocb package?

Alternatively has anybody written a general purpose wrapper that escapes user provided values for N1QL correctly (in any programming language)? Also if somebody could point me to the formal spec for the N1QL language and specifically anything that would help pin down the exact characters that need to be escaped (and how that escaping must be done), that would be very helpful and appreciated.


#2

I found Bucket.ExecuteN1qlQuery in the SDK and that looks similar to the way sql works but it’s hard to tell if escaping is happening:

Can anybody point me to the documentation for how this parametrized query language works? Is does it use ? or $1 for the parameters?


#3

Hmm, it looks like all the values are kept separate from the query and the N1QL escaping (if any) and the substitution is done on the server side.

See the specific code line here

So now I will go digging through server code to try to find out how to format the parameters and whether they’re escaped (unless somebody has more easily accessible information).


#4

I also found what looks like the parameter format here:

execOpts["$"+key] = value

So a parametrized query might be

SELECT * FROM bucket WHERE age>=$min_age

Then params could be something like

map[string]interface{}{"min_age":21}

I haven’t looked at the server code yet so is this accurate?


#5

Here is the other part of Brant’s posting: https://blog.couchbase.com/couchbase-and-n1ql-security-centeredgesoftware/

SQL injection attacks are possible with N1QL, however the SDK’s provides a means of substitution which is done on the server, which protects the consuming application.

-Jeff


#6

Thanks jmorris! Definitely interesting to note that (despite being schemaless):

there is no named parameter equivalent for identifiers

In my specific use case when dealing with identifiers / schema related stuff I’ll likely maintain an explicit mapping in the application rather than using user supplied data for that.


#7

Hi @voutasaurus, just to confirm that yes your query looks good for substitution. So you’d do something like

query := gocb.NewN1qlQuery("SELECT * FROM bucket WHERE age>=$min_age")
params := map[string]interface{}{"min_age":21}
rows, err := bucket.ExecuteN1qlQuery(query, params)

You can also use positional placeholders where you’d do something like

query := gocb.NewN1qlQuery("SELECT * FROM bucket WHERE age>=$1")
rows, err := bucket.ExecuteN1qlQuery(query, []interface{}{21})