C# Using LIKE to see if X contains Y

Question

Hi All,

I've inherited the code below but I'm puzzled as the code works just fine but I'm not convinced it's correct. I've not used LIKE before so I can't decide if I should bother changing this. How does this look to you?

The code is for a filter in a db it checks if X value contains Y value, find X* in X (e.g. Match would be F* in Fun and Fan), and X does not contain Y.

Thanks for your opinion.

foreach (ListViewItem item in listView1.Items)
                {
                    strStuff += item.Text;
                    object o = item.SubItems[1].Tag;
                    switch ((int)item.SubItems[1].Tag)
                    {
                        case 0:
                            strStuff += " LIKE '%" + item.SubItems[2].Text + "%'"; //contains
                            break;
                        case 1:
                            strStuff += " LIKE '" + item.SubItems[2].Text + "'"; //allows * wildcard
                            break;
                        case 2:
                            _strCriteria += " NOT LIKE '%" + item.SubItems[2].Text + "%'"; //doesn't contain
                            break;

                        default:
                            strStuff += "\"" + item.SubItems[2].Text + "\"";
                            break;
                    }
                    strStuff += " And ";
                }

Answer 1

While this looks like it should work for the intended purpose, you should really be escaping the text thats appended into what's presumably a SQL string.

Answer 2

It looks right for me, you have to give more context to say if it actually correct. As already noted you have to look for possible SQL injection attacks (is it web app)

To understand the functionality of the Sql LIKE keyword just try some queries against the database directly.

Answer 3

Escape the text strings to avoid SQL injection, but otherwise you are good. LIKE in SQL does string pattern matching and accepts various wild-cards like "%" and "_".

Detailed information can be found here and here.