Azure Cosmos DB SQL API QueryDefinition multiple parameters for WHERE IN

Asked Oct 01 '19 at 20:19

Active Oct 01 '19 at 20:19

Viewed 1,455 times

I'm trying to use SQL query parameters with a WHERE ... IN statement in an Azure Cosmos DB SQL API query:

var componentDesignGuids = new List<Guid>();
// ...
var queryDefinition = new QueryDefinition(
        "SELECT componentDesign.guid, componentDesign.component.name, componentDesign.component.componentType " +
        "FROM components componentDesign " +
        "WHERE componentDesign.guid IN (@componentDesignGuids)")
    .WithParameter("@componentDesignGuids", string.Join(",", componentDesignGuids.Select(guid => $"\"{guid}\""))));

but this results in a query where the replaced parameter is a single string, like IN ("guid0, guid1, guid2"). Since this is an IN clause, I want to put an indeterminate number of strings there, like IN ("\"guid0\", \"guid1\", \"guid2\""). I realize I can make this work with an interpolated string, but I want to be as safe with inputs as possible to prevent injection. How can I specify this with QueryDefinition and/or WithParameter?

asked Oct 01 '19 at 20:19

Scotty H

6,432
6
41
94

5

this is a valid question and not a dup. The answer is any IN query can be written as ARRAY_CONTAINS, which can be parameterized. – Aravind Krishna R. Oct 04 '19 at 03:53
1

Any updates here? Im facing the same problem :/ – Fran Oct 10 '19 at 08:59
1

@Frank I ended up generating a parameter for each item in my list and then filling in each of those generated parameters with `.WithParameter`. – Scotty H Oct 10 '19 at 20:16
Oh! Thank you @ScottyH i'll try that. I hope they fix this soon – Fran Oct 11 '19 at 07:14

Azure Cosmos DB SQL API QueryDefinition multiple parameters for WHERE IN

0 Answers0

Linked