C# SQL parameter query with in [duplicate]

Question

Possible Duplicate:
Parameterizing a SQL IN clause?

i have the follwing code:

var myCommand = new SqlCommand();
myCommand.Parameters.Add("@username", SqlDbType.NVarChar);
myCommand.Parameters["@username"].Value = username;
return _dbConnection.ExecuteQuery("Select * from customer where username = @username");

now i need to adapt the query for more values. I want to do something like this:

var myCommand = new SqlCommand();
foreach (string username in usernames){
  myCommand.Parameters.Add("@username", SqlDbType.NVarChar);
  myCommand.Parameters["@username"].Value = username;
}
return _dbConnection.ExecuteQuery("Select * from customer where username in @username");

Is that possible? How?

Thank you!

BR Stefan

Ted Elliott · Accepted Answer · 2011-10-20 14:33:54Z

2

Each parameter needs to be unique:

var paramNames = new List<string>();
var myCommand = new SqlCommand();
foreach (string username in usernames){
  var paramName = "@user" + paramNames.Count;
  myCommand.Parameters.Add(paramName, SqlDbType.NVarChar);
  myCommand.Parameters[paramName].Value = username;
  paramNames.Add(paramName);
}
return _dbConnection.ExecuteQuery("Select * from customer where username in (" + string.Join(",", paramNames) + ")");

You will get sql like this:

SELECT * from customer where username in (@user0, @user1, @user2)

It doesn't help much from a query plan caching standpoint, but you will get the benefits of not being vulnerable to sql injection attacks.

answered Oct 20, 2011 at 14:33

Ted Elliott

3,5481 gold badge31 silver badges34 bronze badges

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

Comments

DeveloperX · Accepted Answer · 2011-10-20 14:46:49Z

2

Change the code to this

    var myCommand = new SqlCommand();
    int i = 0;
    string param = string.Empty;

    foreach (string username in usernames)
    {
        string paramName = string.Format("@username{0}", i);
        myCommand.Parameters.Add(paramName, SqlDbType.NVarChar);
        myCommand.Parameters[paramName].Value = username;
        param += string.Format("  (username={0}) or", paramName);

        i++;
    }
    param = param.Substring(0, param.Length - 2);
    return _dbConnection.ExecuteQuery("Select * from customer where " + param);

answered Oct 20, 2011 at 14:46

DeveloperX

4,69319 silver badges22 bronze badges

1 Comment

DeveloperX Over a year ago

also using or instead of IN is more faster

Trio · Accepted Answer · 2011-10-20 15:21:24Z

2

I like to do stuff like this in two methods.

public void ReadDatabase(string[] values)
{
    foreach (var value in values)
    {
        using (var rd = GetData(value))
        {
            if (!rd.Read())
                throw new OMGException("FAILED!");

            Console.WriteLine(rd["UserId"].ToString());
        }
    }
}

public IDataReader GetData(string value)
{
    using(var cm = _connectionWhatever.CreateCommand())
    {
        cm.CommandText = @"
            Select UserId
            From MyTable
            Where UserName = @User
        ";
        cm.CommandType = CommandType.Text;
        cm.Parameters.AddWithValue("User", value);
        return cm.ExecuteReader();
    }
}

This is just to give you some ideas. Hopefully this will help.

edited Oct 20, 2011 at 15:21

answered Oct 20, 2011 at 14:44

Trio

1764 bronze badges

1 Comment

MAW74656 Over a year ago

Virtual +1 for OMGException!

Curtis · Accepted Answer · 2011-10-20 14:20:55Z

1

This could be done by passing an XML data type to the Query and searching within that.

declare @x xml
set @x = '<IDs><ID>1</ID><ID>2</ID></IDs>'

SELECT ID.value('.', 'int') AS ID
FROM @x.nodes('/IDs/ID') as IDS(ID)

So in this instance you could do:

var myCommand = new SqlCommand();
myCommand.Parameters.Add("@usernames", SqlDbType.Xml);
string usernames = "<Usernames>"
foreach (string username in usernames){
   usernames+= String.Format("<username>{0}</username>", username);
}
usernames += "</Usernames>"
myCommand.Parameters["@usernames"].Value = usernames;
return _dbConnection.ExecuteQuery("Select * from customer where username in (SELECT
    username.value('.', 'nvarchar') AS Username
    FROM @x.nodes('/Usernames/Username') as Usernames(Username))");

However, syntax may need checking

answered Oct 20, 2011 at 14:20

Curtis

104k69 gold badges280 silver badges359 bronze badges

1 Comment

Joel Coehoorn Over a year ago

Not exactly the most performant option, but the concept should work.

Purplegoldfish · Accepted Answer · 2011-10-20 14:24:25Z

0

You cant do it exactly the way you are thinking with the loop because you would be adding the same parameter multiple times with different values, however this may be helpful:

http://support.microsoft.com/kb/555266 (its in VB but you can probably find a c# example or translate)

The way I would solve this myself is to make this into a stored procedure as its just good practice (benefits such as added security, efficiency, code reuse etc), then have your procedure accept a parameter of XML. In your C# application convert the Usernames collection to XML then pass that into the SP.

answered Oct 20, 2011 at 14:24

Purplegoldfish

5,29410 gold badges41 silver badges59 bronze badges

Comments

Joel Coehoorn · Accepted Answer · 2011-10-20 14:42:41Z

-1

Well, if you really want to you could do this entirely through string manipulation.

string sql = "Select * from customer where username in (";

string comma = "";
foreach(string s in usernames)
{
   sql += comma + "'" + CleanSqlParameter(s) + "'";
   comma = ", ";
}

return _dbConnection.ExecuteQuery(sql);

This would be messy! But if you feel like you need to construct the query entirely in C#, here's a way to do it. I've had success with this method.

edited Oct 20, 2011 at 14:42

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

answered Oct 20, 2011 at 14:30

MAW74656

3,57921 gold badges80 silver badges120 bronze badges

7 Comments

Curtis Over a year ago

It's important to ensure that the usernames object is not built from textboxes on a page for example, or else this will be prone to SQL Injection

MAW74656 Over a year ago

@Curt -Absolutely correct. I actually have a procedure in the cleanup section that makes sure no unsafe words are in the query such as 'N, DELETE, UPDATE, INSERT'. This was just a sample.

Curtis Over a year ago

Yeah I think this is an acceptable solution as long as users don't have a way of manipulating the values in the usernames object

Joel Coehoorn Over a year ago

@Curt - It's worse than that. Everything in the database was entered into a computer by a human at some point. They may have been safe when first put in, but on cycling them through in this way you now have what is called a 2nd Order Sql Injection vulnerability. This is just a really BAD way to build your query, no matter where the data comes from.

Joel Coehoorn Over a year ago

@MAW74656 - A cleanup procedure is a weak and failure-prone substitute for good parameterization. There are better ways.

|

Collectives™ on Stack Overflow

C# SQL parameter query with in [duplicate]

6 Answers 6

Comments

1 Comment

1 Comment

1 Comment

Comments

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

1 Comment

1 Comment

Comments

7 Comments

Linked

Related