How limited are Parameterized Queries?

SQL++
, ,
1

I’ve been trying to write a query builder for N1QL and I’ve run into a small issue. I’m trying to parameterize multiple parts of the query and it failing to understand or it’s not written correctly.

Given this query,

SELECT `bucket`.* FROM `bucket` WHERE param = 1;

Would it be possible to parameterize similarly to this?

SELECT $1 FROM $2 WHERE $3 = $4;

I’ve been able to do single values such as:

SELECT `bucket`.* FROM `bucket` WHERE param = $1;

This works, but I’d like to take away the worry of being able to inject into variables and values by passing them as arguments.

Is this just not possible or would I need to manually validate each directive and the value that follows and embed them in the string?

2

Parameterization is only values not field names, identifiers.
SELECT bucket.* FROM bucket WHERE field1 = $1 AND field2 = $2 …;

3

How would you recommend sanitizing the field names then? Just verify they’re strings?

4

Filed names are identifiers and needs to be specified as part of query

5

Right, but how would you sanitize those inputs into the query. I don’t know if Couchbase has any sort of protection against N1QL injection. Similar to how with mysql I can run escape_strings or trim to invalidate comments at the end of the string.

6

Inputs are validated with JSON types.

7

If you put back ticks around the field name, it is treated as an identifier regardless of content. (Though to sanitize the input, you would need to quote any backticks in the field name. IIRC, to quote a backtick you replace it with two backticks.)

8

Can you give an example of what you’re trying to explain?

Are you saying it would be possible to have identifiers in place of fields if they’re escaped with backticks?

9

https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/identifiers.html

10

Thanks for the clarification.