locked
can't get cmd.Parameters.AddWithValue to work RRS feed

  • Question

  • User348142989 posted

    HI

    I have a web service that fetches image names matching user input text in an autocomplete text box.  The code below works but has a flaw - the SearchText is user input and I want to avoid SQL injection attacks.

    List<string> my_list = new List<string>();
    string conString = ConfigurationManager.ConnectionStrings["Images_Connection"].ConnectionString;
    string query = "Select TOP (@Count) image_Name FROM dbo.Table_Images WHERE (image_Name LIKE '%@SearchText%')";
    query = query.Replace("@Count", count.ToString());
    query = query.Replace("@SearchText", prefixText);
    SqlConnection sqlConn = new SqlConnection(conString);
    sqlConn.Open();
    SqlCommand cmd = new SqlCommand(query, sqlConn);

    DataTable dt = new DataTable();
    dt.Load(cmd.ExecuteReader());
    sqlConn.Close();
    if (dt.Rows.Count > 0)
    {
    foreach (DataRow row in dt.Rows)
    {
    my_list.Add(row[0].ToString());
    }
    }
    return my_list;

    If I update the code to update the parameter @SearchText (as follows), even though I enter the same text, nothing is returned. 

    string query = "Select TOP (@Count) image_Name FROM dbo.Table_Images WHERE (image_Name LIKE '%@SearchText%')";
    query = query.Replace("@Count", count.ToString());
    SqlConnection sqlConn = new SqlConnection(conString);
    sqlConn.Open();
    SqlCommand cmd = new SqlCommand(query, sqlConn);
    //https://forums.asp.net/t/1132244.aspx
    cmd.Parameters.AddWithValue("@SearchText", prefixText);
    DataTable dt = new DataTable();
    dt.Load(cmd.ExecuteReader());
    sqlConn.Close();

    What am I doing incorrectly?

    Thanks for any help.

    Wednesday, July 31, 2019 2:59 PM

Answers

  • User348142989 posted

    Hi Yang Shen

    Took awhile, but you pointed me in the correct direction.  The query caused the problem.  This is a query I used successfully in VB but for some reason, using C# it does not work. 

    I also updated the web method to use a datareader (which I think is more efficient than using a table) for this type of query.  Updated code below.

    And since I have total control over the count (which is an integer), I can update the query directly.

    Also, I can now change prefixText = "%" + prefixText + "%" by adding/removing % depending if I want the search to begin with, include or end with.  

    Thanks for your help.

           [WebMethod]
            public List<string> Get_image_Names(string prefixText, int count)
            {
                List<string> my_list = new List<string>();


                prefixText = "%" + prefixText + "%";


                string conString = ConfigurationManager.ConnectionStrings["Images_Connection"].ConnectionString;
                SqlConnection conn = new SqlConnection(conString);


                string query = "Select TOP (@Count) image_Name FROM dbo.Table_Images WHERE (image_Name LIKE @SearchText) ";
                query = query.Replace("@Count", count.ToString());


                SqlCommand cmd = new SqlCommand
                {
                    Connection = conn,
                    CommandType = CommandType.Text,
                    CommandText = query
                };
                cmd.Parameters.AddWithValue("@SearchText", prefixText);


                conn.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    my_list.Add(rdr[0].ToString());
                }
                conn.Close();


                return my_list;
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 2, 2019 6:30 PM

All replies

  • User-719153870 posted

    Hi TiredOldCat,

    If you add % directly in your query string will cause your program can't find the @SearchText in it.

    Please add the % in your prefixText declaration.

    You can refer to below code:

                string prefixText = "%"+TextBox1.Text+"%";
                int count = 1;
                string query = "Select TOP (@Count) image_Name FROM dbo.Table_Images WHERE (image_Name LIKE @SearchText)";
                query = query.Replace("@Count", count.ToString());
                SqlConnection sqlConn = new SqlConnection(conString);
                sqlConn.Open();
                SqlCommand cmd = new SqlCommand(query, sqlConn);
                cmd.Parameters.AddWithValue("@SearchText", prefixText);
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                sqlConn.Close();

    Best Regard,

    Yang Shen

    Thursday, August 1, 2019 3:09 AM
  • User348142989 posted

    Hi Yang Shen

    Here is my web method in its entirety.  prefixText and count are passed in from the autocomplete extender.

    I wrapped prefixText with % but did not solve my problem.  The addwithvalue did not seem to update the query.

    [WebMethod]
    public List<string> Get_image_Names(string prefixText, int count)
    {
    List<string> my_list = new List<string>();
    string conString = ConfigurationManager.ConnectionStrings["Images_Connection"].ConnectionString;
    string query = "Select TOP (@Count) image_Name FROM dbo.Table_Images WHERE (image_Name LIKE '%@SearchText%')";
    query = query.Replace("@Count", count.ToString());
    //query = query.Replace("@SearchText", Server.HtmlEncode(prefixText));
    SqlConnection sqlConn = new SqlConnection(conString);
    sqlConn.Open();
    SqlCommand cmd = new SqlCommand(query, sqlConn);
    prefixText = "%" + prefixText + "%";
    cmd.Parameters.AddWithValue("@SearchText", prefixText);
    DataTable dt = new DataTable();
    dt.Load(cmd.ExecuteReader());
    sqlConn.Close();
    if (dt.Rows.Count > 0)
    {
    foreach (DataRow row in dt.Rows)
    {
    my_list.Add(row[0].ToString());
    }
    }
    return my_list;
    }

    Thursday, August 1, 2019 2:54 PM
  • User-719153870 posted

    Hi TiredOldCat,

    I'm sorry I didn't make it clear before. You have also need to remove the '% and %' in your query string.

    Please try change below:

    string query = "Select TOP (@Count) image_Name FROM dbo.Table_Images WHERE (image_Name LIKE '%@SearchText%')";

    to:

    string query = "Select TOP (@Count) image_Name FROM dbo.Table_Images WHERE (image_Name LIKE @SearchText)";

    Best Regard,

    Yang Shen

    Friday, August 2, 2019 1:09 AM
  • User348142989 posted

    Hi Yang Shen

    Took awhile, but you pointed me in the correct direction.  The query caused the problem.  This is a query I used successfully in VB but for some reason, using C# it does not work. 

    I also updated the web method to use a datareader (which I think is more efficient than using a table) for this type of query.  Updated code below.

    And since I have total control over the count (which is an integer), I can update the query directly.

    Also, I can now change prefixText = "%" + prefixText + "%" by adding/removing % depending if I want the search to begin with, include or end with.  

    Thanks for your help.

           [WebMethod]
            public List<string> Get_image_Names(string prefixText, int count)
            {
                List<string> my_list = new List<string>();


                prefixText = "%" + prefixText + "%";


                string conString = ConfigurationManager.ConnectionStrings["Images_Connection"].ConnectionString;
                SqlConnection conn = new SqlConnection(conString);


                string query = "Select TOP (@Count) image_Name FROM dbo.Table_Images WHERE (image_Name LIKE @SearchText) ";
                query = query.Replace("@Count", count.ToString());


                SqlCommand cmd = new SqlCommand
                {
                    Connection = conn,
                    CommandType = CommandType.Text,
                    CommandText = query
                };
                cmd.Parameters.AddWithValue("@SearchText", prefixText);


                conn.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    my_list.Add(rdr[0].ToString());
                }
                conn.Close();


                return my_list;
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 2, 2019 6:30 PM