What is the proper syntax when trying to insert a variable into a stringbuilder statement

I have this C# statement:
BuildSql.AppendFormat("(UPPER(Field1) like '%{0}%' or UPPER(Field2) like '%{0}%' or UPPER(Field3) like '%{0}%')")

Open in new window

I want to replace the {0} with a string variable passed in. What is the proper syntax for that? Thanks.
dodgerfanAsked:
Who is Participating?
 
Snarf0001Connect With a Mentor Commented:
Well you should consider a couple different things.  As per the question, to add into a stringbuilder you just add a comma separated list of values at the end.  {0} is the first, {1} would reference the second, etc...

string searchTerm = "myValue";

//rest of the sql builder
BuildSql.AppendFormat("(UPPER(Field1) like '%{0}%' or UPPER(Field2) like '%{0}%' or UPPER(Field3) like '%{0}%')", searchTerm);

SqlCommand com = new SqlCommand();
com.CommandText = BuildSql.ToString();

Open in new window



But assuming this is for a database, that will leave you very open to injection attacks.  The preferred way is by using SqlParameters, and passing those into the command structure.
string searchTerm = "myValue";
searchTerm = string.Format("%{0}%", searchTerm);

//rest of the sql builder
BuildSql.Append("(UPPER(Field1) like @search or UPPER(Field2) like @search or UPPER(Field3) like @search)");

SqlCommand com = new SqlCommand();
com.CommandText = BuildSql.ToString();
com.Parameters.AddWithValue("@search", searchTerm);

Open in new window

0
 
dodgerfanAuthor Commented:
Perfect. Thank you and thanks for the heads up on the sql injection.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.