从C＃代码后面有{}的Sqlquery？任何线索为什么会发生？ [英] Sqlquery having { } from C# code behind ? any clues why it happening?

问题描述

大家好，



我用C＃为SqlConnection命令编写了一个内联查询。文本



这就是我在做什么，command.Text ={select * from table}为什么这个{和}在实际查询之前和之后出现。任何线索请回复



我尝试了什么：

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:

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();

}

}

推荐答案

我们无法分辨 - 那里没有任何东西应该为你的字符串生成器添加花括号。

所以要么是在编辑的JOIN中，要么根本不在那个代码中。

从调试器开始，检查StringBuilder运行时的内容，看看是否你可以准确地确定括号的去向 - 这可能会给你一个关于它们来自何处的线索，并且它至少会确保它是生成它们的确切代码。

如果是没有帮助，截断SQL字符串直到它们消失，并查看您删除的最后一位。 SQL不会起作用并不重要，因为你无论如何都不会执行它！



抱歉，我们不能做任何为你！

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!

从不使用字符串连接（或 string.Format 或 AppendFormat ，或插入字符串*，...）来构建SQL查询。 总是使用参数化查询。



在这种特殊情况下，因为你的参数都是 int 值，你已经避免了 SQL Injection [ ^ ]漏洞。但是开始这是一个坏习惯，而且很容易滑倒并使用字符串连接来处理不安全的事情。



正确传递多个参数值是稍微比它应该更复杂。但这并不是特别困难：

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:

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())
            {
                ...

你可能想看看 Dapper [ ^ ]，这使得这种查询变得更加容易。





*有一篇文章解释了如何安全地执行此操作，但我目前找不到链接。

---

你想知道关于SQL注入的一切（但不敢问）特洛伊亨特 [ ^ ]

如何在没有技术术语的情况下解释SQL注入？ |信息安全堆栈交换 [ ^ ]

查询参数化备忘单| OWASP [ ^ ]

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[^]

这篇关于从C＃代码后面有{}的Sqlquery？任何线索为什么会发生？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！