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
– Soner Gönül, Commented Dec 17, 2013 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
– Steve, Commented Dec 17, 2013 at 15:36

Community · Accepted Answer · 2017-05-23 10:32:19Z

1

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

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Dec 17, 2013 at 15:20

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:28:40Z

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);
      ......
 }

Mike Perrenoud · Accepted Answer · 2013-12-17 15:20:40Z

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, 2013 at 15:20

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

3 Comments

Mike Perrenoud Over a year ago

@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.

Kamran Over a year ago

@MichaelPerrenoud It not working like this I am having a syntax error, at + strr +, con)). The error is: invalid expression term ','

Mike Perrenoud Over a year ago

@Kami, is it the extraneous + after the str variable that I missed on my first edit?

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related