Hi Team,

We are facing issue in SQL injection flaw , we referred to this document of veracode but that didn't lower the count of the flaw.

https://www.veracode.com/security/dotnet/cwe-89

Please also refer to the attachments.

This is the code for which we are trying get rid of sql injection flow

  public virtual ObjectResult<SystemUser> GetSystemUser(string p_name)

        {

            var tran = base.Database.BeginTransaction();

            var command = base.Database.Connection.CreateCommand();

            var courseList = base.Database.SqlQuery<string>(@"SELECT concept.get_system_user('" + p_name + "')").FirstOrDefault();

            return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<SystemUser>("fetch all in \"" + courseList + "\""); \\Sql Injection issue at this line

        }

Approaches we have tried:

After :

Approach 1

public virtual ObjectResult<SystemUser> GetSystemUser(string p_name)

       {

           var parameters = new NpgsqlParameter("@p_name", p_name);

           var tran = base.Database.BeginTransaction();

           var command = base.Database.Connection.CreateCommand();           

            var courseList = base.Database.SqlQuery<string>(@"SELECT concept.get_system_user(@p_name)", parameters).FirstOrDefault();           

            var param = new NpgsqlParameter("@courseList",courseList);

           return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<SystemUser>($"fetch all in\"" + @courseList + "\"", param);  

        }

Approach 2

public virtual ObjectResult<SystemUser> GetSystemUser(string p_name)

       {

           var parameters = new NpgsqlParameter("@p_name", p_name);

           var tran = base.Database.BeginTransaction();

           var command = base.Database.Connection.CreateCommand();           

            var courseList = base.Database.SqlQuery<string>(@"SELECT concept.get_system_user(@p_name)

    var param = new List<NpgsqlParameter>

           {

               new NpgsqlParameter()

               {

                   ParameterName = @courseList, NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Refcursor,Value = courseList

               }

           };

           return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<SystemUser>($"fetch all in\"{@courseList } \";", param);

       }