cheluto2 cheluto2 - 24 days ago 7
C# Question

How to secure my query: I get The SqlParameter is already contained by another SqlParameterCollection

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

Answer Source

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)