can't get cmd.Parameters.AddWithValue to work

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.

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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