1
foreach (string word in allTheseWords)
{
allTheseStringsWhereClause = allTheseStringsWhereClause + 
                             " report=" + 
                             word + 
                             " AND ";
}

The problem is after the loop, the SQL clause has an extra AND at the end of it.

How do I fix this?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Cocoa Dev
  • 9,361
  • 31
  • 109
  • 177

7 Answers7

13

you can alternatively use LINQ

string _final = string.Join(" AND ", (allTheseWords.Select(x => "report=" + x)));
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 1
    You are using the wrong variable. `allTheseStringsWhereClause` is a string. The words are in the `allTheseWords` variable. – Guffa Jan 27 '13 at 06:24
4

By inserting user input into a sql where clause you are introducing a vector for a SQL Injection Attack. Do no use this approach. Basically you want to use SQL Parameters. 

where report in @allTheseWords

See http://en.wikipedia.org/wiki/SQL_injection

EDIT

See the answer with the most votes in Parameterize an SQL IN clause for how to really do parameterised in query.

Community
  • 1
  • 1
Richard Schneider
  • 34,944
  • 9
  • 57
  • 73
  • 2
    The question is about string manipulation, not SQL best practices. This is not an answer to his question, and would better serve as a comment to the question than an answer. – Timothy Strimple Jan 27 '13 at 06:21
  • 1
    @TimothyStrimple, I so strongly disagree. The OP specifically mentions `allTheseStringsWhereClause`, which implies a SQL where clause. This is **WRONG** and must be corrected. – Richard Schneider Jan 27 '13 at 06:24
  • 1
    Er. What database lets you pass an array as a parameter? – Random832 Jan 27 '13 at 06:26
  • @RichardSchneider what if the `WHERE` clause the OP is referring is used to filter the dataset? anyway, that is still **NOT** the correct way of parameterizing `IN` clause. – John Woo Jan 27 '13 at 06:27
  • @Random832. Sorry I was just taking the easy way with out doing the research (I always use an ORM) thats why I said basically). However now see the answer with the most votes in http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause – Richard Schneider Jan 27 '13 at 06:38
  • @RichardSchneider - thank you for your insight. Although, I didn't ask about SQL Injection; you were able to deduce that I am making an SQL Query. So my question is how do I pass an array as a Parameter? – Cocoa Dev Jan 28 '13 at 15:13
2

This kind of question is very common and here's the proof.

  1. Delete last char of string
  2. How to delete last character in a string in C#?
  3. Finding the last index of an array

But here my simple solution

        string[] allTheseWords = { "try", "test", "let" };
        string whereLine = string.Empty;
        foreach (var item in allTheseWords)
                whereLine += "report = " + item.ToString() + " and ";
        string final = whereLine.Remove(whereLine.Length - 5);
        Console.WriteLine(final);
        Console.ReadLine();
Community
  • 1
  • 1
spajce
  • 7,044
  • 5
  • 29
  • 44
1

A simple way would be to add a bogus always-true clause (and move the AND):

string allTheseStringsWhereClause = "WHERE 1=1";
foreach (string word in allTheseWords)
{
allTheseStringsWhereClause = allTheseStringsWhereClause + 
                             " AND report=" + 
                             word;
}
Hans Kesting
  • 38,117
  • 9
  • 79
  • 111
0

You don't need LINQ, just use Join:

string whereClause = " report=" + String.Join(" AND report=", allTheseWords);
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
-1

The best way to do this without hard coding a substring method is to try an Aggregate LINQ query

var result = allTheseWords.Aggregate(allTheseStringsWhereClause, (current, word) => current + " report=" + word + " AND ");
Dharun
  • 613
  • 8
  • 26
-3

Easy. You just need to do allTheseStringsWhereClause.substr(0,-4) and that's it!

Sitepor500.com.br
  • 388
  • 1
  • 5
  • 11