Hi,
Some of our queries use user input. I’m a little worried about sql injection.
From this official document (https://developer.couchbase.com/documentation/server/4.1/developer-guide/querying.html), it recommends query placeholders and says “The approach above is both unwieldy and insecure (subject to SQL injection attacks).”
The problem with our cases is that our queries do not work with placeholders because our index definitions have where clauses in them. I’ve already asked this question before on Query takes 75 seconds when using rest api/sdk, but 20ms in cbq
I’m very interested in alternative. It sounds like I have to somehow filter user input on my end, but what is the best approach?
In mysql world, filtering user input to prevent sql injection in code is considered a bad practice. It’s always recommended to use prepared statement as far as I know.
how are you guys doing it?
1 Like
Hi @moon0326,
You can write code to map the user input to some predetermined query fragments. For example, if the user can supply a “type” value, you can map the user’s input to a hash map of known type values.
This way, you are using partial indexes while not subject to SQL injection.
Hi @geraldss
That’s a good idea, but I think it can only be used for some cases.
If we have a search UI where a user can input anything, then I simply can’t pre-map the in my code. That would be really big. If I pre-map the keywords in couchbase, then I need to query them anyway, which brings me back to sql injection issue.
I understand n1ql is different than other query languages, but I really think it should have a solution to sql injection either by an extension or improving how it processes parameters. Not able to use parameterized query just because I have an index definition that has where clause, which is a feature of n1ql index does not sound good to me.
Hi @moon0326,
You cannot have an application where a user can enter any SQL at all. There must be some domain or some specific boundaries within which your users will be operating. Or is that not the case?
If you provide some examples of indexes, queries, and user input, we can offer more specific suggestions.
You are right, this will be easier when N1QL handles it automatically. Let’s see what can be done in the meantime.
It’s very simple and common case.
Let’s say you have a search input where users can input a keyword. How can you prevent users with a limited # of keywords? Even if you can supply keywords to users, that mans I have to search by user’s keyword then create a suggested keywords map, which means I have to search my documents anyway.
query might be simple as
select * from bucket where something='something' and something1='something else' and searchField = userProvidedInput
I haven’t seen a database that does not support parameterized query just because I have a different index definition yet. Oracle, Mysql, Mssql, Postgres…all support parameterized queries without a problem. I might be miss-understanding something here though.
Ok. We need to revisit your examples. Can you give a specific query and index where the user’s parameter would prevent the index from being used? I will also look at the earlier thread. This is solvable. N1QL does have parameters.
I am not sure that MySQL has parameterized index selection. It is a fairly advanced feature.
I will put a detailed use case tonight (or tomorrow morning). with index and query. If you look at the thread, it’s just that n1ql does not use index if I send placeholders along with parameters.
this is from your reply in that thread
Hi @moon0326, so everything works correctly as intended. If you use a parametrized query, we cannot choose a conditional / partial index, because the query plan must work with any value of the parameter.
So you have two options:
(1) Use a conditional index and fully specify the query, without parameters for the index keys.
(2) Use a full index, and you can use a parametrized query for the index keys.
Sounds good. Here was my point about mapping user input. In your example, you had form=example1, form=example2, form=example3.
If you have only 3 possible values for form, you can use partial indexes. Just map the user input to one of the three valid form values.
Okay, I’ve tried a few more tests…and yes, I can use partial index. It requires more code (to fetch form values and validate) and selectively binding values, but I think it’s the best approach for now.
To summarize…
- just put values into query string if those values are validated by a map or some other means.
- bind user input values and use partial index.
Thank you @geraldss for answering over the weekend. I really appreciate it.
1 Like