Parameterize DocumentDB query with IN clause

Question

I have a query somewhat like the following which I'm trying to parameterize:

List<string> poiIDs = /*List of poi ids*/;
List<string> parameterNames = /*List of parameter names*/;

string inClause = string.Join(",", parameterNames);

string query = string.Format("SELECT c.id AS poiID, c.poiName, c.latitude, c.longitude FROM c WHERE c.clusterName = @clusterName AND c.id IN ({0}) AND c.deleted = false", inClause);

IQueryable<POI> queryResult = Client.CreateDocumentQuery<POI>(Collection.SelfLink, new SqlQuerySpec
            {
                QueryText = query,
                Parameters = new SqlParameterCollection()
                {
                    new SqlParameter("@clusterName", "POI"),
                    // How do I declare the dynamically generated parameters here
                    // as new SqlParameter()?
                }
             });

How do I declare the dynamically generated parameters as new SqlParameter() for the Parameters property of SqlQuerySpec in order to create my document query?

http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause ? — Soner Gönül, Oct 30 '15 at 06:53
Appreciate your response, but I'm working with Azure DocumentDB not SQLServer. — Abhishek Das, Oct 30 '15 at 06:54

score 3 · Answer 1 · answered Apr 13 '16 at 03:07

You can create dynamic parameterized query like this:

// DocumentDB query 
 // POINT TO PONDER: create the formatted query, so that after creating the   dynamic query we'll replace it with dynamically created "SQL Parameter/s"
            var queryText = @"SELECT
                                us.id,
                                us.email,           
                                us.status,
                                us.role
                            FROM user us
                            WHERE us.status = @userStatus AND us.email IN ({0})";


            // contain's list of emails  
            IList<string> emailIds = new List<string>();
            emailIds.Add("a@gmail.com");
            emailIds.Add("b@gmail.com");

            #region Prepare the query

            // simple parameter: e.g. check the user status
            var userStatus = "active";
            var sqlParameterCollection = new SqlParameterCollection { new SqlParameter("@userStatus", userStatus) };                

            // IN clause: with list of parameters:
            // first: use a list (or array) of string, to keep  the names of parameter          
            // second: loop through the list of input parameters ()
            var namedParameters = new List<string>();
            var loopIndex = 0;

            foreach (var email in emailIds)
            {
                var paramName = "@namedParam_" + loopIndex;
                namedParameters.Add(paramName);

                var newSqlParamter = new SqlParameter(paramName, email);
                sqlParameterCollection.Add(newSqlParamter);

                loopIndex++;
            }

            // now format the query, pass the list of parameter into that
            if (namedParameters.Count > 0)
                queryText = string.Format(queryText, string.Join(" , ", namedParameters));

                // after this step your query is something like this  
                // SELECT
                //                 us.id,
                //                 us.email,            
                //                 us.status,
                //                 us.role
                //             FROM user us
                //             WHERE us.status = @userStatus AND us.email IN (@namedParam_0, @namedParam_1, @namedParam_2)

            #endregion //Prepare the query

            // now inject the parameter collection object & query
            var users = Client.CreateDocumentQuery<Users>(CollectionUri, new SqlQuerySpec
            {
                QueryText = queryText,
                Parameters = sqlParameterCollection
            }).ToList();

Skorunka František · Answer 2 · 2017-03-30T10:49:30.517

1

The following gives you a SQL query, you can then run in your DocumentDB Collection, to get the Documents by their IDs.

var query = $"SELECT * FROM p WHERE p.id IN ('{string.Join("', '", arrayOfIds)}')";

edited Mar 30 '17 at 10:49

answered Mar 29 '17 at 11:01

Skorunka František

5,102
7
44
69

Just add some explanation to your snippet and explain what you are doing ;) – Alex Cio Mar 29 '17 at 12:08
1

Was in hurry and just had the "answer" in a clipboard ;). Done. – Skorunka František Mar 30 '17 at 10:50
No hating, I just reviewed some answers :) – Alex Cio Mar 31 '17 at 11:19

score 0 · Answer 3 · edited May 23 '17 at 12:02

0

The DocumentDB SDK doesn't support parameterized IN queries.

Judging from the SO thread in the comment above, SQL does not either. As mentioned in the other thread, you can use LINQ as a workaround.

edited May 23 '17 at 12:02

Community

1
1

answered Nov 04 '15 at 21:25

Andrew Liu

8,045
38
47

You can parameterize a SQL query with IN clause. Refer: http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause – Abhishek Das Nov 05 '15 at 06:55
My understanding is that using LINQ instead does not help the cause against SQL Injection problem. Does it? – Abhishek Das Nov 05 '15 at 06:57
According to the thread you linked, SQL does not support parameterized IN as an out-of-box feature. The answers in the thread are generic workarounds and can be adapted to DocumentDB as well. – Andrew Liu Nov 05 '15 at 07:23
Try adapting http://stackoverflow.com/a/337792/319795 or http://stackoverflow.com/a/380185/319795 – Andrew Liu Nov 05 '15 at 07:29
The Count of parameterNames is unknown to me for they are populated at run-time depending upon the user-input. Therefore I cannot declare the SQLParameters in the SQLParameterCollection while creating my query. I want to know whether the Query can be tweaked in a similar manner done as in the thread I posted in my comment. If yes, please post a code snippet. – Abhishek Das Nov 05 '15 at 09:17

score 0 · Answer 4 · answered Jan 23 '20 at 21:06

Why not use the ArrayContains method? Here is an example in node

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

Parameterize DocumentDB query with IN clause

4 Answers4