locked
Need to modify DBHelper class to remove Check Id: CA2100 (Review SQL queries for security vulnerabilities) Warnings RRS feed

  • Question

  • User1052662409 posted

    HI All,

    Below I am having my DBHelper class to implement CRUD operaions.

    public class DBHelper
    {
        string ConnectionString = string.Empty;
        static SqlConnection con;
        public DBHelper()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
            con = new SqlConnection(ConnectionString);
        }
        public void SetConnection()
        {
            if (ConnectionString == string.Empty)
            {
                ConnectionString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
            }
            con = new SqlConnection(ConnectionString);
        }
        public DataTable GetDatatable(string procName, Hashtable parms)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.CommandText = procName;
            cmd.CommandType = CommandType.StoredProcedure;
            if (con == null)
            {
                SetConnection();
            }
            cmd.Connection = con;
            if (parms.Count > 0)
            {
                foreach (DictionaryEntry de in parms)
                {
                    cmd.Parameters.AddWithValue(de.Key.ToString(), de.Value);
                }
            }
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        public DataTable GetDatatable(string procName)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.CommandText = procName;
            cmd.CommandType = CommandType.StoredProcedure;
            if (con == null)
            {
                SetConnection();
            }
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        public int Insert_Update_Delete(string procName, Hashtable parms)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            if (parms.Count > 0)
            {
                foreach (DictionaryEntry de in parms)
                {
                    cmd.Parameters.AddWithValue(de.Key.ToString(), de.Value);
                }
            }
            if (con == null)
            {
                SetConnection();
            }
            cmd.Connection = con;
            if (con.State == ConnectionState.Closed)
                con.Open();
            int result = cmd.ExecuteNonQuery();
            return result;
        }
    
    
    }
    

    All works fine. But when I run code analyzer on my project it shows Check Id: CA210 warnings on below lines of every method of class.

    cmd.CommandText = procName;

    With the below message.

    The query string passed to 'SqlCommand.CommandText.set(string)' in 'DBHelper.GetDatatabel(string)' could contain the following variables 'procName'. If any of these variables could come from user input, consider using a stored procedure or a parameterized SQL query instead of building the query with string concatenations.

    I know that doesn't make any difference on my project, but I don't want those warnings any more.

    Please suggest

    Tuesday, April 9, 2019 3:54 AM

Answers

  • User-893317190 posted

    Hi demoninside9 ,

    According to your code,  you have used a stored procedure or a parameterized SQL query, and your variable procName will not from user's input.

    So you could omit this warning.

    If you don't want to see this warning, you could  right click the warning , select suppress choose either of two options(global or in source).

    Then you  will not see this warning.

    Below is what you could see when choosing in source

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
            public int Insert_Update_Delete(string procName, Hashtable parms)

    Below is what you could see when choosing global in GlobalSuppressions.cs.

    [assembly: System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Scope = "member", Target = "WebFormCases2.sqldbexe.DBHelper.#GetDatatable(System.String)")]

    You could also change the ruleset for analysis.

    Right click your project , choose properties, choose code analysis , then click open, expand  managed binary analysis, then uncheck CA2100.

    Then   use ctrl + S to save the ruleset , a ruleset file should appear at root path of your project. If you want to change  this ruleset ,just delete it or  edit it.

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 9, 2019 6:24 AM