How to parameterize IN clause

[zhouchong90]

Hi,

If I have a list of IDs, and I'd like to query like select * from c where c.id IN (...) How can I parameterize this with the SqlQuerySpec?

Thanks.

[moderakh]

@zhouchong90 for IN (...) clause please try to avoid Parametrizing the query. Please just pass the query string without any parametrization.

[zhouchong90]

Will it be suspect to SQL injections and attacks?

[moderakh]

@zhouchong90 You can try something like this:

String query = "SELECT * from c where c.prop IN (@param1, @param2)";
SqlParameterCollection params = new SqlParameterCollection(new SqlParameter("@param1", 98), new SqlParameter("@param2", 99));
SqlQuerySpec sqs = new SqlQuerySpec(query, params);

[zhouchong90]

@moderakh Thanks. Will there be any built-in support to take a list directly? Since I'll have a list of thousands of values and it'll be ugly to write a dynamically generate sql and put the values.

[mohitgoyal91]

@zhouchong90 We have a simple library, where you can generate queries more effectively. https://github.com/mohitgoyal91/azure-cosmosdb-querybuilder-java

For example in your case for query -> "SELECT * from c where c.prop IN (@param1, @param2)" Assuming you have a

List<Integer> list = new ArrayList<>();
list.add(98); list.add(99);

Simply call the below function:

new SelectQuery()
.in("prop", list.toArray())
.createQuery();

[christopheranderson]

Cleaning up older issues.

No plans on support parameterized lists directly any time soon. Following Mohit's pattern makes sense and something we could potentially support ourselves in the future, but not a priority at the moment.

[kmansel]

For node SDK based applications, I found that this way works as well.

sqlQuery: SqlQuerySpec = {
        query: 'SELECT * FROM t WHERE ARRAY_CONTAINS(@idList, t.id)',
        parameters: [
            {
                name: '@idList',
                value: ['id1','id2','id3'],
            },
        ],
    };

[MrBuddyCasino]

Just found this issue, absolutely perplexed that this is just silently not supported. Its not like the client libs are still 1.x early days. Combined with the fact that it is not possible to log the outgoing SQL queries, this just cost me several hours.

[viswa-optimusprime]

@All is this fixed ? What is the solution ? We really need this feature to pass list for in clause.. Can someone tell us the solution ?

[shethmanan]

Its 2024, and cosmos SDK is still missing this feature. Any plans to add it or solutions for this?

[kushagraThapar]

Hey @shethmanan, this repository is not active anymore and has reached end of life, please create this issue here and we can follow up - https://github.com/Azure/azure-sdk-for-java