How to use parameterized queries for paths in document?

SQL++
,
1

Let’s say I have a document:
{
“xp” : 1,
“hp”: 2,
“mp”: 0
}

How can I parameterize a query like:
select myBucket.* from myBucket where xp = 1

Let’s say I need to swap out xp, hp, or mp with a parameter? Doing something like this just seems to compare the text “xp” against the number 1:
select myBucket.* from myBucket where $1 = 1
(then of course, use the SDK to set a positional parameter)

I am using the .NET SDK, but I think this is just a general N1QL question.

2

Hi,

You can parametrize a value, but not an attribute. So you can say WHERE xp = $1.

In your case, you can just generate the query string on the fly.

3

This sounds like SQL injection to me.

How do I guard against that?

4

Can you describe your use case or your app?

You may be able to put all the attributes in your query. If the user does not specify one of them, you can use IS NOT NULL or IS NOT MISSING for that attribute.

You are right to be conscious of SQL injection.

5

We have some leaderboards that are dynamic, and the client (iOS/Android app) passes different paths depending on the type of leaderboard. We use a N1QL query to pull out the needed data, but we need parameters as described above.

The issue is this is a public facing API, so we need to be conscious of SQL injection, for sure.

Is there not a function or something like EVAL that would take a parameter/string and use it as a path inside the JSON document?

6

Can you expose a drop down for the different paths, so that your application controls the possible strings?

Presumably, EVAL is subject to injection as well, or some other risk.

7

I was comparing to what a RDMS (SQL Server) would do, and people are saying to use a CASE statement in the query: http://stackoverflow.com/a/10092975/132442

Is this an option in Couchbase? Will it still take advantage of indexes?

8

Yes, N1QL has a CASE expression.

There are probably better ways to do this. In your application, you can have a hash map of queries, and use the column name to choose the query from the hash map.

9

Thanks, I think that is probably the best option here: use a list/hashmap to validate the input, then append the SQL string together as text.

1 Like