Unclosed quotation mark after the character string ''. Incorrect syntax near ''. How to resolve this while search function using like operator?

Question

public CategoryModel getDate(string str)
{
CategoryModel obj = new Models.CategoryModel();
StaticCat objc = new StaticCat();
objc.CategoryID = 0;
obj.obj1 = objc;
DataTable CatTable = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
//string str =obj.obj1.CategoryName ;
SqlDataAdapter sqlDb = new SqlDataAdapter("SELECT * FROM CategoryDetail WHERE CatName LIKE '%" + str + "%'", conn);
sqlDb.Fill(CatTable);
}
List<CategoryLists> obj2 = new List<CategoryLists>();
foreach (DataRow dr in CatTable.Rows)
{
CategoryLists obj3 = new CategoryLists();

            obj3.CategoryID = dr["catId"].ToString();
            obj3.Category = dr["catName"].ToString();
            obj3.Status = dr["CatStatus"].ToString();
            obj2.Add(obj3);

        }
        obj.obj2 = obj2;

        return obj;
    }

Answer 1

your code allows sql injection. you should use a command argument. append the % to search value.