Passing strings in Sql query from C#

Question

I Have a sql query as below:

Select CatId 
From tbl_T2H_Category 
Where Category IN ('Category3', 'Category4', 'Category6')

Now what i want is the values inside IN clause should be added dynamically from the checkboxlist. I am getting comma separated values from my control and passing that to the sql query like this:

string mystring = "Category3,Category4,Category6";
cmd.commanText = "Select CatId From tbl_T2H_Category Where Category IN (" + mystring + ")";

This is not executing on sql side, because sql only recognises strings if they are inside "'" "'" (single quotes). Kindly help me write the appropriate query.

What version of sql server? Later versions have a better way of handling this. — Joel Coehoorn
– Joel Coehoorn, Commented Jun 11, 2013 at 14:22
This looks like an old request for free work, and once people supplied that free work, you did not reply anyway. Please always reply to the people who assist you. — halfer
– halfer, Commented Apr 27, 2017 at 11:18

Joel Coehoorn · Accepted Answer · 2013-06-11 14:23:43Z

6

You want to use a Table-Value Parameter. The MSDN article shown here demonstrates it better than I can:

http://msdn.microsoft.com/en-us/library/bb675163.aspx

answered Jun 11, 2013 at 14:23

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

phadaphunk Over a year ago

+1 for not proposing the obvious, dangerous SELECT 'INJECT' + 'ME' FROM Table

GarethD · Accepted Answer · 2013-06-11 14:30:53Z

This is best dealt with using Table-Valued Parameters.

Your first step is to create the type:

CREATE TYPE dbo.StringList AS TABLE (Value NVARCHAR(MAX) NOT NULL);

Your next step is to create a datatable in c# from your comma separated list to pass to your SqlCommand:

string mystring = "Category3,Category4,Category6";
string[] myarray = mystring.Split(",".ToCharArray());

DataTable table = new DataTable();
table.Columns.Add("Value", typeof(string));

for (int i = 0; i < myarray.Length; i++)
{
    var row = table.NewRow();
    row[0] = myarray[i];
    table.Rows.Add(row);
}

Finally you can pass this to your SqlCommand:

cmd.commanText = "Select CatId From tbl_T2H_Category Where Category IN (SELECT Value FROM @Strings)";
cmd.Parameters.Add(new SqlParameter("@Strings", SqlDbType.Structured)).Value = table;

Arif YILMAZ · Accepted Answer · 2013-06-11 14:30:00Z

correct syntax

string mystring = "'Category3','Category4','Category6'";
cmd.commanText = "Select CatId From tbl_T2H_Category Where Category IN (" + mystring + ")";

but please dont use it this was. you can use it like below(but this is not recommended as well)

cmd.commanText = "Select CatId From tbl_T2H_Category Where Category IN ('" + cat_1 + "','" + cat_2 + "','" + cat_3 + "')";

I would use this one below, it is alot secure

string commandText = "Select CatId From tbl_T2H_Category Where Category IN (@cat_1,@cat_2 @cat_3)";
 SqlCommand command = new SqlCommand(commandText, connection);
 command.Parameters.Add("@cat1", SqlDbType.Varchar);
 command.Parameters["@cat1"].Value = "category1";
 command.Parameters.Add("@cat2", SqlDbType.Varchar);
 command.Parameters["@cat2"].Value = "category2";
 command.Parameters.Add("@cat3", SqlDbType.Varchar);
 command.Parameters["@cat3"].Value = "category3";

this last one is much secure, it prevents sql-injection

Soner Gönül · Accepted Answer · 2013-06-11 14:24:02Z

0

Just wrap every category in string with single quote?

Something like

var mystring = "'Category3','Category4','Category6'"

edited Jun 11, 2013 at 14:24

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

answered Jun 11, 2013 at 14:22

Stay Foolish

3,7763 gold badges28 silver badges31 bronze badges

2 Comments

phadaphunk Over a year ago

This will leave him open to sql injection.

Ondrej Svejdar Over a year ago

And be sure there is no way, user can forge category values - i.e. if user can submit category with name ';drop table Category;-- you're in trouble

user1477388 · Accepted Answer · 2013-06-11 14:24:13Z

0

Split your comma separated values into an array with

 string[] myArr = commaSepString.Split(',');

and then join them with commas, surrounded by commas like this

 string newString = "'"+myArr.Join("','")+"'";

answered Jun 11, 2013 at 14:24

user1477388

21.6k35 gold badges152 silver badges277 bronze badges

Comments

Arthur Menezes · Accepted Answer · 2013-06-11 14:39:10Z

0

Try this...

public String returnCategories(string categories) 
    {
        string categoriesConc = "";

        string[] split = categories.Split(',');

        for (int i = 0; i < split.Length; i++) 
        {
            if (string.IsNullOrEmpty(categoriesConc))
            {
                categoriesConc = split[i].ToString();
            }
            else 
            {
                categoriesConc = categoriesConc + "," + split[i].ToString();
            }
        }

        return categoriesConc;
    }

answered Jun 11, 2013 at 14:39

Arthur Menezes

2615 silver badges17 bronze badges

Collectives™ on Stack Overflow

Passing strings in Sql query from C#

6 Answers 6

1 Comment

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related