2

I'm trying to parameterize all queries to satisfy SQL Injection findings from the PUMA vulnerability scanning extension in Visual Studio. While doing that, I'm running into the error in the title:

The SqlParameter is already contained by another SqlParameterCollection

This is the relevant code (second line generates the error):

    IEnumerable<UploadedDocsClass> docLst = prfDao.GetUploadedDocLog(Convert.ToInt32(id));
    if (docLst.Count() > 0){ //<-- this is where the error is thrown
    ...
    }

public IEnumerable<UploadedDocsClass> GetUploadedDocLog(int tickId)
{
  string s = "SELECT * FROM MyTable WHERE [request_id] = @tickId ";
  MyDataBaseContext accCon = new MyDataBaseContext();
  return accCon.Database.SqlQuery<UploadedDocsClass>(s, new SqlParameter("@tickId", Convert.ToString(tickId)));
}

I have searched here and in Google and can't seem to find a solution to my specific situation. I've tried cloning the parameter, declaring it specifically and setting it to null after using it, putting it into a collection before using it, I even tried adding a random number at the end of the parameter name so it is different each time, but nothing, same error.

How can I solve this while still using a parameter in the query?

Thanks

cheluto2
  • 35
  • 6
  • 1
    You haven't shown any code that actually involves `SqlParameter`. – SLaks Sep 25 '17 at 19:38
  • Yes I have. It's in the last line of code in my sample. – cheluto2 Sep 25 '17 at 19:46
  • I assume it is complaining about whatever that Convert phrase reduces to, which implies that whatever tickID resolves to in this context can only be used by one query. Usually we see `.Clear()` being used on such things before reusing (or create a new set of params, but I don't fully grok this code. (Which is why this is not an answer.) –  Sep 25 '17 at 19:51
  • That completely depends on what `SqlQuery()` does. – SLaks Sep 25 '17 at 19:51
  • T.S. I fixed it. I simplified the query text for this post, the real query is much longer and uses a StringBuilder, a reference to which I accidentally left in the uploaded text. – cheluto2 Sep 25 '17 at 19:53
  • Is this because `IEnumerable` returned? then, it tries to execute again? Try returning `List` – T.S. Sep 25 '17 at 19:54
  • @T.S. - that seems to have done the trick! Thanks a lot! If you make that comment an answer I will be happy to mark it as the solution. – cheluto2 Sep 25 '17 at 19:57

1 Answers1

1

It is because IEnumerable<UploadedDocsClass> returned. It tries to execute again when docLst.Count() called - differed execution

Try returning List<T>

MSDN: The query is not executed when this object is created; it is executed each time it is enumerated, for example by using foreach.

Apparently, it is trying to reuse parameters that are added to another collection (previous execution)

T.S.
  • 18,195
  • 11
  • 58
  • 78
  • Thanks again, this is exactly what I needed to know. I've tested it and it works when returning it as List – cheluto2 Sep 25 '17 at 20:05
  • No problem. Always be careful with `IEnumerable` as it takes you into the land of differed execution. And `DbRawSqlQuery` too – T.S. Sep 25 '17 at 20:22