SQL injection protection in Expression

Couchbase Server
,
1

Hello:

I am trying to make my queries SQL injection proof. I am using couchbase expression to build the query with user input

    private GroupByPath getSearchStatement(SearchParams searchParams) {
        String bucketName = asyncBucket.name();
        Expression expression = x("sample_id").eq(s(searchParams.getSampleId()));
        String selectStatement = bucketName + ".*";
        return select(selectStatement)
                .from(bucketName)
                .where(expression);
    }

Is this code SQL injection safe? Or I have to do anything extra.

Thanks,
Ashwani

2

I think you need adhoc=false. Not sure without the underlying N1QL statement. cc @daschl @ingenthr

3

To prevent from SQL injection, generally you’ll want to use a query with placeholders. The querying from the SDK section in the docs has a brief example on this.

In your code example, I believe the string supplied by searchParams.getSampleId() could introduce a side effect. It’s hard to say exactly the possibilties, but I believe one problem you could have with the above is inserting a subquery since that is valid in a WHERE clause. That may expose data you didn’t mean to be exposed or have another side effect.

By using placeholders, the statement will be parsed for execution by cbq-engine, and then the parameters are applied later using cbq-engine’s API for this.

The .adhoc() parameter on the query will have the Java SDK automatically set up prepared statements for the query and execute them once prepared, which are more of a performance optimization. You can have a combination of parameterized/placeholders and prepared statements, or both independently.

By the way, there is a .toString() on the query if you want to log it to have @geraldss take a look.

4

Thanks for the reply.

I updated code with this

  private N1qlQuery getSearchStatement(SearchParams searchParams) {
        String bucketName = asyncBucket.name();
        Expression expression = x("sample_id").eq(x("$sample_id));
        String selectStatement = "count(*) as size";
        Statement statement= select(selectStatement)
                .from(i(bucketName))
                .where(expression)
                .limit(20);
        JsonObject placeHolderValues = JsonObject.create().put("sample_id", searchParams.getSampleId());
        N1qlParams params = N1qlParams.build().adhoc(false);
        return N1qlQuery
                .parameterized(statement, placeHolderValues, params);     
    }

However I am getting following error if I use adhoc=false.

Error occurred in Couch base Count{"msg":"Unable to decode prepared statement - cause: Unrecognizable prepared statement - cause: JSON unmarshalling error: Keyspace not found keyspace  - cause: No bucket named ","code":4070}

Following is the generated query:
Query:
ParameterizedN1qlQuery{statement=SELECT count(*) as size FROMdefaultWHERE sample_id = $sample_id LIMIT 20, params={"sample_id":"abc"}}

With adhoc=true it works fine.

What can be the issue here?
Thanks

5

If it works without being prepared, it should also work as prepared. The error returned doesn’t trigger any particular thoughts to me, but it might to @marcog.

We can look into it further though. What version of server/sdk are you using? Note that there have been a number of prepared statement fixes over the various versions, so if you’re not on 4.6, I’d recommend trying that first.

6

The encoded plan is not unmarshalling correctly on the target n1ql service.
Could I have a look at the relevant entry from system:prepareds (specifically, I need generated text as stored in the prepared cache, and the encoded plan) on the node where the request has been successfully prepared?

7

Thanks @ingenthr, upgrading to 4.6 worked without any changes in the code.

8

Meaning it works well with adhoc(false) and prepared statements @ashwanikumar04? That’s good info.