this ' in my search

[dorandoran]

I have a search box txtSearch and it works fine for both first or last name. but I try to break it by inputing o'brien and received following error. I using sql like syntax to do my search. How can I make search better and break free? (should I be using parameterized stored proc? ) Please suggest. What are the pros doing (i mean their approach to this)?

syntax error: Missing operand after 'brien' operator

[dorandoran]

I tried this and it seems to be working. But now in text box it shows O''Brien. Note: I put extra quotation mark.

here is code, works just puts extra ' in the search text

        txtNameSearch.Text = txtNameSearch.Text.Trim().ToString();
        txtNameSearch.Text = txtNameSearch.Text.Replace("'","''").ToString();
        if (txtNameSearch != null)
        {
            SetFilter("([first_name] LIKE '%" + txtNameSearch.Text + "%' or [last_name] LIKE '%" + txtNameSearch.Text + "%')");
            GridView2.SelectedIndex = -1;
        }

[Frinavale]

This doesn't work because an apostrophe (') is special SQL syntax.
It is used to indicate string literals. So when you are searching for John Doe your SQL will look like

Select * from MyTable Where name='John Doe' 

Now look what happens when you enter O'Brien:

Select * from MyTable where name = 'O'Brien'

See? It breaks.

In fact, this is taken advantage of by SQL Injection Attacks.

You need to change how you are creating your SQL query.

Instead of using input directly from the user while creating your SQL query, use parameters instead.

So instead of creating your SQL query like this:

sqlCom.CommandText = "Select * From myTable where name = '"+myTextBox.Text +"'";

You would create your SQL query like this:

sqlCom.CommandText = "Select * From myTable Where name = @searchName"
sqlCom.Parmaeters.Add("@searchName", SqlDbType.VarChar).Value =  myTextBox.Text;

For examples please see the article on how to use a database in your program.

-Frinny

[dorandoran]

Thanks Frinny for your tips. I was sure someone would know what I am talking about. and the find me the best solution. I will try your suggesion. Again thanks a lot.