can't get cmd.Parameters.AddWithValue to work[Answered] RSS

• • Reply
  TiredOldCat

  Member

  5 Points

  43 Posts

  can't get cmd.Parameters.AddWithValue to work

  Jul 31, 2019 02:59 PM|TiredOldCat|LINK

  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.

• • Reply
  Yang Shen

  Contributor

  3140 Points

  983 Posts

  Re: can't get cmd.Parameters.AddWithValue to work

  Aug 01, 2019 03:09 AM|Yang Shen|LINK

  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

• • Reply
  TiredOldCat

  Member

  5 Points

  43 Posts

  Re: can't get cmd.Parameters.AddWithValue to work

  Aug 01, 2019 02:54 PM|TiredOldCat|LINK

  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;
  }

• • Reply
  Yang Shen

  Contributor

  3140 Points

  983 Posts

  Re: can't get cmd.Parameters.AddWithValue to work

  Aug 02, 2019 01:09 AM|Yang Shen|LINK

  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

• • Reply
  TiredOldCat

  Member

  5 Points

  43 Posts

  Re: can't get cmd.Parameters.AddWithValue to work

  Aug 02, 2019 06:30 PM|TiredOldCat|LINK

  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;
          }