MSSQL query in C#, Geting part of WHERE clause from a string

Question

I am using following code:

string str = "ID = 15";

using (SqlCommand cmd = new SqlCommand("SELECT * " + 
                                       "FROM Cutomer" + 
                                       " Where (Status='Umbruch') and '"+str+"' " , con))

I am getting this error:

near 'ID = 15' is a non boolean expression specified in a context where a condition is expected.

I know I can use the above code like this:

int id = 15; 

using (SqlCommand cmd = new SqlCommand("SELECT * " + 
                                       "FROM Cutomer" + 
                                       " Where (Status='Umbruch') and (ID='"+str+"')" , con))

It works fine like this but I want to use it as mentioned in first code above. Because the number of IDs is not fixed they can be 2,3 or more. Just for simplicity I put my code here like this.

Because the right usage of this `ID = '15'` not `'ID = 15'` ? — Soner Gönül, Dec 17 '13 at 15:20
Then you don't need single quotes anywhere in your string used for the where clause. But I repeat again, keep yourself save and use a parameterized query — Steve, Dec 17 '13 at 15:36

score 1 · Answer 1 · edited May 23 '17 at 10:32

The first version should be (from your second "working" query, you need simple quotes around 15, not around the entire expression).

str = "ID = '15'";

using (SqlCommand cmd = new SqlCommand("SELECT * " + 
                                       "FROM Cutomer" + 
                                       " Where (Status='Umbruch') and "+str , con))

By the way, you should user Parameters, not direct strings.

If you need an IN clause, you may look at that question

score 1 · Answer 2 · edited May 23 '17 at 12:28

A simple error, do not put single quotes before and after the string

using (SqlCommand cmd = new SqlCommand("SELECT * FROM Cutomer " + 
                                       "WHERE (Status='Umbruch') AND " + str , con))

of course this assumes that your ID field is numeric and not a text field.
In the latter case you should put the single quotes around the 15 constant

string str = "ID = '15'";

As a side note, keep in mind that string concatenation is really dangerous because your code could be exploited with a simple Sql Injection attack. In this very specific case there is no possibility for injection.

If you receive the ID to search for from a user input, do not use string concatenation to build sql commands. Instead use a parameterized query

 using (SqlCommand cmd = new SqlCommand("SELECT * FROM Cutomer " + 
                               " Where (Status='Umbruch') and ID=@custID" , con))
 {
      cmd.Parameters.AddWithValue("@custID", 15);
      ......
 }

score 0 · Accepted Answer · answered Dec 17 '13 at 15:20

0

You're making it a literal here:

using (SqlCommand cmd = new SqlCommand("SELECT * " + 
                                       "FROM Cutomer" + 
                                       " Where (Status='Umbruch') and '"+str+"' " , con))

It should be:

using (SqlCommand cmd = new SqlCommand("SELECT * " + 
                                       "FROM Cutomer" + 
                                       " Where (Status='Umbruch') and "+str, con))

Note the slight difference in which the ' were removed.

Further, please read through my blog post on why you're open to SQL injection and why you need to leverage parameterized SQL instead of raw SQL.

answered Dec 17 '13 at 15:20

Mike Perrenoud

66,820
29
157
232

@SLaks: Are you referring to the fact that the `str` should be `ID = '15'`? Which would only be true if the key were in fact a `VARCHAR` yes? In other words `ID = 15` wouldn't work if the key were a `VARCHAR`, but either would work if it were an `INT`. – Mike Perrenoud Dec 17 '13 at 15:22
@MichaelPerrenoud It not working like this I am having a syntax error, at + strr +, con)). The error is: invalid expression term ',' – Kamran Dec 17 '13 at 15:24
@Kami, is it the extraneous `+` after the `str` variable that I missed on my first edit? – Mike Perrenoud Dec 17 '13 at 15:25

MSSQL query in C#, Geting part of WHERE clause from a string

3 Answers3