0

I have a problem with calling by SQL Server database. I have this table and an object with property Groups, which is something like "all nj sk2".

My query in ssms is 

SELECT * 
FROM Hours 
WHERE class_id = 1 
  AND groups IN ('all', 'nj', 'sk2')

In C# I'm doing something like this

var query = "SELECT * FROM Hours WHERE class_id = @class_id AND groups LIKE (@groups)";

using (var cmd = new SqlCommand(query, conn))
{
    cmd.Parameters.AddWithValue("@class_id", User.Current.ClassId);
    cmd.Parameters.AddWithValue("@groups", User.Current.Groups.Replace(" ", ", "));
}

The only way I made this work was

var groups = "('" + User.Current.Groups.Replace(" ", "', '").Remove(User.Current.Groups.Length - 2);
//above is ('all', 'nj', 'sk2')
var query = "SELECT * FROM Hours WHERE class_id = @class_id AND groups LIKE " + groups;

using (var cmd = new SqlCommand(query, conn))
{
    cmd.Parameters.AddWithValue("@class_id", User.Current.ClassId);
}

But this is not a good solution imo, so if anyone knows what am I doing wrong please help me out. Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Daniel Suchan
  • 51
  • 8
  • 2
    **[Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause)** – Lukasz Szozda Apr 22 '18 at 17:02
  • The issue is that the parameter `@groups` represents a single value. That is the benefit of parameters, otherwise SQL injection could occur. (What if `@groups` was `"''); drop table Hours; --"`?) – HABO Apr 22 '18 at 17:06
  • This is good thanks @lad2025, but i think i'll still need to use IN statement for further purposes so if anyone has a cooler solution, ill appreciate it – Daniel Suchan Apr 22 '18 at 17:07
  • @HABO I know thats why im asking – Daniel Suchan Apr 22 '18 at 17:08
  • 1
    I've closed your question with three duplicates. If that's not enough, you can also use a table valued parameter (that might be just an overkill for such a purpose). – Zohar Peled Apr 22 '18 at 17:08

0 Answers0