0

I have a string array that gets split at the comma,

string example = "happy,sad,depressed,shock";
string[] split = example.Split(',');

What i want to do is check if there is only one string lets say happy,and just have that one string populated in my where statement. But if there is more than one string in the array i want to pass one of them to one WHERE Statement then the others to a built up OR 

if (split.Count() == 1)
    {
       query = query + "WHERE emotion LIKE '%" + split[0] + "%'"; 
    }
else
    {
      query = query + "WHERE emotion LIKE '%" + split[0] + "%'";


 for (int i = 0; x < split.Length; i++)
     {
       query = query + "OR emotion LIKE '%" + split[i] + "%'";
     }
    }

so my query will end up like:

   WHERE emotion LIKE %happy% OR LIKE %sad% OR LIKE %depressed%

Not quite sure how to structure the syntax for that. Any help will be greatly appreciated.

Anthony Horter
  • 45
  • 1
  • 12
  • 3
    Your approach is vulnerable to SQL Injection. Instead of this you can create a procedure and pass the values as table valued parameter to it. – PSK Jan 27 '19 at 08:40
  • Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) – mjwills Jan 27 '19 at 08:46
  • If you can't use a [table valued parameter](https://stackoverflow.com/questions/31965233/adding-multiple-parameterized-variables-to-a-database-in-c-sharp/31965525#31965525) at least use a technique similar to [this](https://stackoverflow.com/questions/46520435/multiple-ids-in-in-clause-of-sql-query-c-sharp/46521159#46521159) to parameterize the `In` operator (which also simplifies your code since it means you have the same code for 1, 2, or 200 values in the string) – Zohar Peled Jan 27 '19 at 08:47
  • Have you considered using `CONTAINS` instead of `LIKE`? In that case, your syntax will be easier, something like `WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')` and without multiple `OR LIKE`, but you will need to enable full-text search. More details here https://www.developer.com/db/article.php/3446891/Understanding-SQL-Server-Full-Text-Indexing.htm. – Samvel Petrosov Jan 27 '19 at 08:48
  • 1
    The key here is to do `query += string.Join(split.Select(z => "emotion LIKE etc", " OR "));` I have purposely not provided a working syntax, to give you space to read up on SQL injection. – mjwills Jan 27 '19 at 08:48
  • So what is not working in your code? – Aldert Jan 27 '19 at 09:09
  • If you're using sql server 2016 you can use [string_split (example E)](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017) to move the juggling with that where clause to the sql server and that addresses the sql injection as well. – rene Jan 27 '19 at 09:38

0 Answers0