Looping WHERE variable in SELECT statement for c#

Question

So, I'm trying to create an area called "shoutout" where the current user may view "shoutout", basically messages from the shoutout table from people that are friends with him/her.

So, what I did was first put all the current user's friends into an array called "currentuserfriends", then I wish to select message from the shoutout table where username = all the users in "currentuserfriends".

However I have no idea how to loop in a select statement,

I don't want to have to do something like,

string getuserfriendlist = "SELECT friend FROM friend WHERE username = '" + currentuserfriends[0] + "'";

and increase currentuserfriends[0] manually. Is there a way to loop currentuserfriends until the end in a single select statement? Thanks.

Friend table

username | friend

shoutout table

username | message | datetime

Code:

        string[] currentuserfriends = new string[9999];
string[] posternames = new string[9999];
    string getuserfriendlist = "SELECT friend FROM friend WHERE username = '" + currentuser + "'";

    SqlCommand cmdb = new SqlCommand(getuserfriendlist, con);

    SqlDataReader drb;

    drb = cmdb.ExecuteReader();

    while (drb.Read())
    {

        string currentuserfriend = drb["friend"].ToString();
        currentuserfriends[l++] = currentuserfriend;
    }
    drb.Close();

    string getshoutout = "SELECT * FROM shoutout WHERE username = '" + currentuserfriends + "' AND username ='" + currentuser + "' order by datetime DESC";
    SqlCommand cmdc = new SqlCommand(getshoutout, con);
    SqlDataReader drc;
    drc = cmdc.ExecuteReader();

    while (drc.Read())
    {
        string postername = drb["username"].ToString();
        posternames[m++] = postername;
    }
    drc.Close();

    Label2.Text = posternames[0];
    Label3.Text = posternames[1];
    Label4.Text = posternames[2];
    Label5.Text = posternames[3];
    Label6.Text = posternames[4];

I would like to report a bug, I created a user account and now your website stopped working. My user name was `'; drop table shoutout; --` (please look in to using SqlParameters, do a search there are a lot of questions on this site about it.) — Scott Chamberlain, Jun 27 '13 at 07:31
On a on topic note, [here is a different, but related, question](http://stackoverflow.com/questions/17321281/is-there-a-better-way-to-dynamically-build-sql-where-clause-than-by-using-1-1-at) that may solve your problem. **EDIT:** Another side question, what is with the arrays of size 9999? why not just use a `List`? — Scott Chamberlain, Jun 27 '13 at 07:37
@ScottChamberlain: I think a `drop table` is the least of your problems here. The real danger is someone taking over your server or [stealing all your data](http://www.cuttingedge.it/blogs/steven/pivot/entry.php?id=44) without you knowing. — Steven, Jun 27 '13 at 07:44
@Steven yea, but a drop table is the easiest to get the point across in the space of a comment, thanks for the link though, I will use it for a good example in the future. — Scott Chamberlain, Jun 27 '13 at 07:45

score 1 · Answer 1 · answered Jun 27 '13 at 07:41

You can use a List<string> instead of an array. In that case you can do something like this:

var currentuserfriends = new List<string>();

while (drb.Read())
{
    currentuserfriends.Add(drb["friend"].ToString())
}

Like others said, you can optimize your query. I like to take it one step further and advice Entity Framework (or LINQ to SQL). It allows you to do this:

var db = new ObjectContext();

string[] currentuserfriends = (
    from friend in db.Friends
    where friend.Username == currentuser
    select friend.Name)
    .ToArray();

Saves you from having to hand craft your SQL queries and directly prevents your queries from SQL injection, since your current queries are currently vulnerable to SQL injection.

score 0 · Answer 2 · answered Jun 27 '13 at 07:28

0

Use the foreach loop on the array and then generate the SQL query

answered Jun 27 '13 at 07:28

Liam Sorsby

2,912
3
28
51

score 0 · Answer 3 · answered Jun 27 '13 at 07:30

0

Is there a way to loop currentuserfriends until the end in a single select statement?

Yes, there is. the IN Operator does that.

answered Jun 27 '13 at 07:30

tafa

7,146
3
36
40

score 0 · Answer 4 · answered Jun 27 '13 at 07:36

0

First make your array a comma seperated string using the following code

string idString = String.Join(",",currentuserfriends);

Then, Use the below sql statement

SELECT friend FROM friend WHERE username in ("+idstring+");

answered Jun 27 '13 at 07:36

Murugavel

269
1
2
9

Looping WHERE variable in SELECT statement for c#

4 Answers4