2

I need some help filtering a SQL WIQL query. How can I modify the code below to ignore the fields that have empty model values? For example if the user only wants to filter one field like the work item type and ignore the state field? Any help would be appreciated.

 public async Task<ActionResult> Method(filterModel model)
   {
        VssConnection connection = new VssConnection(new 

        Uri(vstsCollectionUrl), new VssClientCredentials());

        WorkItemTrackingHttpClient witClient = connection.GetClient<WorkItemTrackingHttpClient>();
        Wiql query = new Wiql() { Query = "SELECT [Id], [Title] FROM workitems WHERE [Work Item Type] = '" + model.workitem + "' && 
        [State] = '"+ model.state + "'"};
        WorkItemQueryResult queryResults = witClient.QueryByWiqlAsync(query).Result;

        if (queryResults == null || queryResults.WorkItems.Count() == 0)
        {
            Console.WriteLine("Query did not find any results");          
        }
    }
Filburt
  • 17,626
  • 12
  • 64
  • 115
Jeremiah Mora
  • 75
  • 2
  • 9
  • 1
    You've got a lot bigger issue than that.... you're query isn't parametized and it's a SQL injection attack waiting to happen, learn more here >>>> https://blogs.msdn.microsoft.com/sqlphp/2008/09/30/how-and-why-to-use-parameterized-queries/ – Aydin Oct 11 '18 at 13:08
  • yeah I took this code from a sample from Microsoft website on how to use the tfs web API. Just wondering how to modify. – Jeremiah Mora Oct 11 '18 at 13:13
  • 3
    @AydinAdn This is a query against Azure Dev Ops - formally VSTS. WIQL has a SQL like query syntax but it's not truly SQL and doesn't support parameters. https://learn.microsoft.com/en-us/rest/api/vsts/wit/wiql/query%20by%20wiql?view=vsts-rest-4.1 – fourwhey Oct 11 '18 at 13:16
  • 1
    OP You're going to need to create multiple queries that represent the different filter scenarios, then use your filter to determine which one to execute. Your query will be a predefined string literal that includes the filter scenario. Or dynamically create the SQL query based on the values of the model. – fourwhey Oct 11 '18 at 13:25
  • @fourwhey Ooops, when I read SQL query, I didn't bother to read the whole code, just the line containing the query. Thanks for including the link, going to read up on this – Aydin Oct 11 '18 at 13:34

1 Answers1

1

Just remove this code && [State] = '"+ model.state + "':

public async Task<ActionResult> Method(filterModel model)
{
    VssConnection connection = new VssConnection(new 

    Uri(vstsCollectionUrl), new VssClientCredentials());

    WorkItemTrackingHttpClient witClient = connection.GetClient<WorkItemTrackingHttpClient>();
    Wiql query = new Wiql() { Query = "SELECT [Id], [Title] FROM workitems WHERE [Work Item Type] = '" + model.state + "'" };
    WorkItemQueryResult queryResults = witClient.QueryByWiqlAsync(query).Result;

    if (queryResults == null || queryResults.WorkItems.Count() == 0)
    {
        Console.WriteLine("Query did not find any results");          
    }
}

Better and clear way:

Wiql query = new Wiql() { Query = $"SELECT [Id], [Title] FROM workitems WHERE [Work Item Type] = {model.state}"};
Shayki Abramczyk
  • 36,824
  • 16
  • 89
  • 114