locked
Entity Framework calling stored procs async and Checkmarx RRS feed

  • Question

  • Our company recently discovered Checkmarx and started testing existing applications.  In the generated report, Checkmarx is reporting a SQL Injection issue. We are using Entity Framework 6 and we have code performing existing Stored Proc calls using

    context.Database.SqlQuery<[RESPONSEOBJECT]>(query).ToListAsync();

    where query is a string and looks like

    "EXEC [STOREDPROCNAME]"

    Admittedly there are a couple like $"EXEC [STOREDPROCNAME] '{param}'"

    Question If I needed to execute the .SqlQuery method to leverage the procs and the ToListAsync(), how would I get this past Checkmarx review?  We could go backwards and add every proc to the .edmx manually (a scaffold-dbcontext would be even better).  This is EF 6.  

    Thank you

    Thank you.

    • Moved by CoolDadTx Wednesday, June 17, 2020 1:39 PM EF related
    Wednesday, June 17, 2020 12:43 AM

All replies

  • Check if such modification passes the checks:

    string query = "EXEC [STOREDPROCNAME] @p0";

    context.Database.SqlQuery<[RESPONSEOBJECT]>(query, param).ToListAsync();

    Wednesday, June 17, 2020 1:43 AM
  • Hi

    It still fails.  The fact I have a string at all seems to flag it and when I have a generic load that has no params it is the same as before.  Adding the proc directly to the edmx forces me to abandon the .ToListAsync() which is a nice benefit of using SqlQuery.  Dumping the procs would be a pain because we have a bunch but also alias columns inside to match a common return object.  Thank you for the idea though.

    Thursday, June 18, 2020 9:36 PM
  • Hi Cheesebread,
    ToListAsync is an extension method on IQueryable<T> declared in System.Data.Entity.QueryableExtensions.
    In order to better understand SqlQuery, I found some related threads you can refer to.
    [How to use DbContext.Database.SqlQuery<TElement>(sql, params) with stored procedure? EF Code First CTP5]
    [Is there a way I can run a Database.SqlQuery as async?]
    Hope these are hepful to you.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 19, 2020 6:14 AM
  • Thank you for the feedback.  I do understand and appreciate the ToListAsync() extension method and we have used it extensively over a short period of time with EF6.  I guess though from google diving and feedback the answer is that I cannot reconcile this choice with checkmarx which will not pass a sqlQuery (with or without params) and flags it as a sql injection risk.  Adding the proc to the edmx is not the preferred option because while it may pass checkmarx scrutiny EF6 won't let me call it as async which was what led  us to leverage the ToListAsync() and sqlQuery in the first place.
    Friday, June 19, 2020 8:32 PM