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

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