Search crashes when random characters '%^$^&%'?

Question

Good afternoon fellow coders, I am running into an error here. You see my search crashes when I type in random characters such as the following: %^&*&%. Here's the error as well as the search code, see below:

Server Error in '/' Application.

Error in Like operator: the string pattern '%$%^$&^%' is invalid.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.EvaluateException: Error in Like operator: the string pattern '%$%^$&^%' is invalid.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[EvaluateException: Error in Like operator: the string pattern '%$%^$&^%' is invalid.] System.Data.LikeNode.AnalyzePattern(String pat) +1277726 System.Data.LikeNode.Eval(DataRow row, DataRowVersion version) +341 System.Data.BinaryNode.Eval(ExpressionNode expr, DataRow row, DataRowVersion version, Int32[] recordNos) +20
System.Data.BinaryNode.EvalBinaryOp(Int32 op, ExpressionNode left, ExpressionNode right, DataRow row, DataRowVersion version, Int32[] recordNos) +12960 System.Data.BinaryNode.Eval(DataRow row, DataRowVersion version) +25
System.Data.BinaryNode.Eval(ExpressionNode expr, DataRow row, DataRowVersion version, Int32[] recordNos) +20
System.Data.BinaryNode.EvalBinaryOp(Int32 op, ExpressionNode left, ExpressionNode right, DataRow row, DataRowVersion version, Int32[] recordNos) +12960 System.Data.BinaryNode.Eval(DataRow row, DataRowVersion version) +25
System.Data.BinaryNode.Eval(ExpressionNode expr, DataRow row, DataRowVersion version, Int32[] recordNos) +20
System.Data.BinaryNode.EvalBinaryOp(Int32 op, ExpressionNode left, ExpressionNode right, DataRow row, DataRowVersion version, Int32[] recordNos) +12960 System.Data.BinaryNode.Eval(DataRow row, DataRowVersion version) +25
System.Data.BinaryNode.Eval(ExpressionNode expr, DataRow row, DataRowVersion version, Int32[] recordNos) +20
System.Data.BinaryNode.EvalBinaryOp(Int32 op, ExpressionNode left, ExpressionNode right, DataRow row, DataRowVersion version, Int32[] recordNos) +12960 System.Data.BinaryNode.Eval(DataRow row, DataRowVersion version) +25
System.Data.BinaryNode.Eval(ExpressionNode expr, DataRow row, DataRowVersion version, Int32[] recordNos) +20
System.Data.BinaryNode.EvalBinaryOp(Int32 op, ExpressionNode left, ExpressionNode right, DataRow row, DataRowVersion version, Int32[] recordNos) +12960 System.Data.BinaryNode.Eval(DataRow row, DataRowVersion version) +25
System.Data.DataExpression.Invoke(DataRow row, DataRowVersion version) +145 System.Data.Index.AcceptRecord(Int32 record, IFilter filter) +101 System.Data.Index.InitRecords(IFilter filter) +297 System.Data.Index..ctor(DataTable table, IndexField[] indexFields, Comparison`1 comparison, DataViewRowState recordStates, IFilter rowFilter) +464 System.Data.DataTable.GetIndex(IndexField[] indexDesc, DataViewRowState recordStates, IFilter rowFilter) +212
System.Data.DataView.UpdateIndex(Boolean force, Boolean fireEvent) +159 System.Data.DataView.UpdateIndex(Boolean force) +12 System.Data.DataView.SetIndex2(String newSort, DataViewRowState newRowStates, IFilter newRowFilter, Boolean fireEvent) +108
System.Data.DataView.SetIndex(String newSort, DataViewRowState newRowStates, IFilter newRowFilter) +14
System.Data.DataView.set_RowFilter(String value) +158
System.Web.UI.WebControls.FilteredDataSetHelper.CreateFilteredDataView(DataTable table, String sortExpression, String filterExpression, IDictionary filterParameters) +387
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1830
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +21
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +138
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +30
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +105 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +75 System.Web.UI.Control.EnsureChildControls() +83 System.Web.UI.Control.PreRenderRecursiveInternal() +42
System.Web.UI.Control.PreRenderRecursiveInternal() +168
System.Web.UI.Control.PreRenderRecursiveInternal() +168
System.Web.UI.Control.PreRenderRecursiveInternal() +168
System.Web.UI.Control.PreRenderRecursiveInternal() +168
System.Web.UI.Control.PreRenderRecursiveInternal() +168
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +974

And the code is:

 public static bool IsDate(Object obj)
        {
            string strDate = obj.ToString();
            try
            {
                DateTime dt = DateTime.Parse(strDate);
                if (dt != DateTime.MinValue && dt != DateTime.MaxValue)
                    return true;
                return false;
            }
            catch
            {
                return false;
            }
        }


 protected void BtnWinnersSearch_Click(object sender, EventArgs e)
        {

            string searchText = txtWinnersSearch.Text.Replace("'", "''").Trim();
            bool isDate = IsDate(searchText);

            GridViewWinners.Visible = true;

            if (isDate == true)
            {
                SqlDataSource4.FilterExpression = "dob" + " ='" + Convert.ToDateTime(searchText).ToString("yyyy-MM-dd") + "'";
            }
            else
            {
                SqlDataSource4.FilterExpression = "nickname like '%" + searchText + "%' or username like '%" + searchText +
                    "%' or clubnumber like '%" + searchText + "%' or firstname like '%" +
                    searchText + "%' or lastname like '%" + searchText +
                    "%' or email like '%" + searchText + "%'";
            }
        }

enter image description here

A form of this question/issue comes up on SO almost daily. Answer: Use parameters instead of dynamically generating your SQL. Besides avoiding errors you will also avoid SQL injection vulnerability. See here for one example: http://stackoverflow.com/questions/4387300 — Paul Sasik, Jul 19 '13 at 20:54

score 3 · Accepted Answer · answered Jul 19 '13 at 20:55

3

Right now your code is vulnerable to a SQL Injection attack.

Switch to using parameterized queries and I suspect it will fix your problem (as well as fix a huge security vulnerability you have right now...). I'm guessing there is some value that is not being properly escaped when you submit "random characters".

Here is an excellent link to get you started with parameterized queries.

answered Jul 19 '13 at 20:55

Abe Miessler

82,532
99
305
486

2

Please don't feed this much-too-frequently duplicated question. – Paul Sasik Jul 19 '13 at 20:56
SelectCommand="SELECT [PlayerID], [Nickname], [UserName], [ClubNumber], [FirstName], [Email], [LastName], [DOB] FROM [Players] where Nickname like '%'+@Nickname+'%' order by lastname ASC"> – Christopher Jul 22 '13 at 23:11
The above comment solved my problem using your solution @Abe. thanks again! – Christopher Jul 22 '13 at 23:12

Search crashes when random characters '%^$^&%'?

1 Answers1