Sqlquery having { } from C# code behind ? any clues why it happening?

Question

3.00/5 (1 vote)

See more:

Hi guys,

I have wrote one inline query using C# for the SqlConnection command.Text

this is how am doing, command.Text= "{ select * from table}" why does this "{" and "}" coming before and ending of the actual query .Any clues please do reply

What I have tried:

C#

private List<objecttype> GetFullData(int id, int[] array1)
{

            DbConnection conn = null;
            DbCommand command = null;
            DbDataReader reader = null;
<pre>var templist = new List<objecttype>();
            try
            {  conn = DB.GetConnection();
                command = conn.CreateCommand();              
                command.CommandType = CommandType.Text;              
                var sbSql = new StringBuilder(
                    @"
          SELECT * from table(long inner join of 2 tables)");
                if (workgroups.Length > 0)
                {
                    sbSql.AppendFormat("WHERE column_3 IN({0})", string.Join(",", array1));
                }
                command.CommandText = sbSql.ToString();
                reader = command.ExecuteReader();

}

Posted 8-Aug-17 7:17am

kida atlantis

Updated 9-Aug-17 6:12am

Add a Solution

2 solutions

Solution 1

We can't tell - there is nothing there that should add curly brackets to your string builder.
So either it's in the redacted JOIN, or it's not in that code at all.
Start with the debugger, and check the StringBuilder content while it's running, and see if you can identify exactly where the brackets are going - that may give you a clue as to where they are coming from, and it will at least ensure that it is that exact code that generates them.
If that doesn't help, truncate the SQL string until they disappear, and look at the last bit you removed. It doesn't matter that the SQL won't work, as you won't be executing it anyway!

Sorry, but we can't do any of that for you!

Posted 8-Aug-17 7:32am

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2017-08-09T06:12:00

NEVER use string concatenation (or string.Format, or AppendFormat, or interpolated strings *, ...) to build a SQL query. ALWAYS use a parameterized query.

In this particular case, since your parameters are all int values, you've avoided a SQL Injection[^] vulnerability. But it's a bad habit to start, and it's far too easy to slip up and use string concatenation for things that aren't safe.

Passing multiple parameter values correctly is slightly more complicated than it should be. But it's not particularly difficult:

C#

private List<objecttype> GetFullData(int id, int[] workgroups)
{
    try
    {
        using (var conn = DB.GetConnection())
        using (var command = conn.CreateCommand())
        {
            command.CommandType = CommandType.Text;
            
            var sb = new StringBuilder(@"SELECT * FROM ...");
            if (workgrouns.Length != 0)
            {
                sb.Append(" WHERE column_3 IN (");
                for (int index = 0; index < workgroups.Length; index++)
                {
                    if (index != 0) sb.Append(", ");
                    
                    string name = "@p" + index;
                    command.Parameters.AddWithName(name, workgroups[index]);
                    sb.Append(name);
                }
                sb.Append(")");
            }
            
            command.CommandText = sb.ToString();
            
            using (var reader = command.ExecuteReader())
            {
                ...

You might want to take a look at Dapper[^], which makes this sort of query a lot easier.

* There was an article which explained how to do this safely, but I can't find the link at the moment.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]