Sql注入参数化查询 [英] Sql Injection parameterised query

问题描述

我需要实现下面的功能

     public PartyDetails GetAllPartyDetails(string name)
    {
        try
        {
            String query = "select * from [Party Details] where name=@name ";
            pd = new PartyDetails();
            com = new SqlCeCommand(query, con);
            com.Parameters.AddWithValue("@name", name);
            con.Open();
            sdr = com.ExecuteReader();
            while (sdr.Read())
            {

                pd.name = sdr.GetString(0);

            }
            con.Close();
            return pd;
        }

        catch (Exception e)
        {
            con.Close();
            throw e;
        }
    }

但是这个函数对我来说效率不高，

But this function is not efficient for me because i don't need to write different function code only just because of change in query.

现在这就是我需要的

  public PartyDetails GetAllPartyDetails(string query)
        {
            try
            {
            pd = new PartyDetails();
            com = new SqlCeCommand(query, con);
            con.Open();
            sdr = com.ExecuteReader();
            while (sdr.Read())
            {
                pd.name = sdr.GetString(0);
            }
            con.Close();
            return pd;
        }
        catch (Exception e)
        {
            con.Close();
            throw e;
        }
    }

但它增加了sql注入的风险，因为不使用 com.Parameters.AddWithValue（@ name，name）; 。有任何替换可能通过调用函数来停止sql注入。

But it increases the risk of sql injection as it is not using com.Parameters.AddWithValue("@name", name);.Is there any replacement possible for this to be achieved by calling function to stop sql injection .

对于不了解我的问题的人

For those who don't understand my question

例如，我有另一个查询 select * from [party details] where address = @ address and name = @ anme ，对于这个我需要再次写一个函数，其中我使用 com.Parameters.AddWithValue （@address，address）;
com.Parameters.AddWithValue（@ name，name）; 只是浪费时间。查询可以有不同的参数值，我需要不依赖查询中的参数的函数。

For example i have another query select * from [party details] where address=@address and name=@anme , for this i need to again write a function in which i use com.Parameters.AddWithValue("@address", address); com.Parameters.AddWithValue("@name", name);, Which is simply wastage of time. Query can have different no of parameter's and i need function which does not depend upon no of parameter in query.

推荐答案

GetAllPartyDetails应不接受一个sql字符串作为参数。这样的方法的目的是抽象出其余的应用程序知道或关心sql的需要，并简单地提供一个来源的数据库实现独立的方。 应该接受参与者的名字作为参数，但如果你需要接受一个sql查询，你正在错误的地方建立你的sql。

A function named "GetAllPartyDetails" should not accept an sql string as an argument. The purpose of a method like that would be to abstract away the need for the rest of the app to know or care about sql, and simply provide a source for party details separate from your database implementation. It should accept the name of the party as an argument, but if you need to accept an sql query, you're building your sql in the wrong place.

你需要的是一个方法，可以通用的方式调用不仅从GetPartyDetails（），而且从其他方法，需要从您的特定数据源的数据。如果你在GetPartyDetails之外创建查询字符串，你需要重新架构一点。

What you need is a method that can be called in a generic way not only from GetPartyDetails() but also from other methods that need data from your particular data source. If you're building your query string outside of GetPartyDetails, you need to re-architect that a little bit.

这种方法检索任何数据应该是什么样子？当然，它需要接受一个sql字符串。它还需要一些方法来接受参数信息。这可以像键/值对数组一样简单，但我喜欢避免需要建立你的参数集合两次的代码。这个参数也应该是 required ，而不是可选的或重载的，以鼓励良好的参数使用。

What should that method for retrieving any data look like? Of course it needs to accept an sql string. It also needs some way to accept parameter information. This could be as simple as an array of key/value pairs, but I prefer code that avoids the need to build up your parameter collections twice. This argument should also be required, rather than optional or overloaded, to encourage good parameter use.

我目前使用这种模式， ve变得非常喜欢它：

I currently use this pattern, and I've become very fond of it:

private IEnumerable<T> GetData(string sql, Action<SqlParameterCollection> addParams, Func<IDataRecord, T> translate)
{
    using (var cn = new SqlConnection("connection string here"))
    using (var cmd = new SqlCommand(sql, cn))
    {
       addParams(cmd.Parameters);
       cn.Open();
       using (var rdr = cmd.ExecuteReader())
       {
          while (rdr.Read())
          {
              yield return translate(rdr);
          }
       }
    }
}

满足我们的通用数据访问方法的所有目标。我唯一不感兴趣的部分是翻译委托的需要，这不是那么大的损失，因为这是代码，你将不得不写在你的应用程序的某个级别。如果你没有将每一行复制到该方法中的一个新对象，你可以得到意想不到的结果，所以我们需要一种方式从datarecord转换到一个业务对象。

This meets all the goals of our generic data access method. The only part I'm not thrilled with is the need for the translation delegate, and it's not that big a loss because this is code you were going to have to write at some level of your application anyway. If you don't copy each row to a new object inside that method, you can get unexpected results, and so we need a way to translate from a datarecord to a business object right there.

这样调用它：

public string GetPartyDetailsByName(string name)
{
    return GetData("select * from [Party Details] where name=@name", p =>
    {
       p.Add("@name", SqlDbType.NVarChar, 50).Value = name;
    }, row =>
    {
       row.GetString(0);
    }).First();
}

如果你有另一个带参数的查询，你可以这样调用： / p>

If you have another query with parameters, you would call it like this:

public string GetPartyDetailsByNameAddress(string name, string address)
{
    return GetData("select * from [Party Details] where name=@name and address=@address", p =>
    {
       p.Add("@name", SqlDbType.NVarChar, 50).Value = name;
       p.Add("@address", SqlDbType.NVarChar,200).Value = address;
    }, row =>
    {
       row.GetString(0);
    }).First();
}

不带任何参数的方法如下：

A method that does not take any parameter would look like this:

public IEnumerable<string> GetAllPartyDetails()
{
    return GetData("select * from [Party Details]", p => {}, row =>
    {
       row.GetString(0);
    });
}

这有点尴尬，但这就是要点。你希望人们

It's a little awkward, but that's the point. You want people to be deliberate about not using parameters, so they can't help but stumble into doing it the right way.

我知道你想要避免写两个方法，但这是右的方式来处理你的数据访问 。是的，有一个方法与数据库谈，帮助抽象掉一些样板代码。但是每个sql查询都有一个额外的方法仍然是正确的做法。

I know you wanted to avoid writing two methods, but this is the right way to handle your data access. Yes, have one method that talks to the database, to help abstract away some of the boilerplate code. But having an additional method for each sql query is still the right thing to do.

你不需要完全按照我的GetData（）方法：函数式有点多一些。但你需要一个单一的方法，这是唯一的地方，可以发送查询到您的数据库，这个方法必须有一些机制接受参数数据。其他方法应不通过sql。这导致注射问题。

You don't need to follow my GetData() method exactly: the functional style is a bit much for some. But you do need a single method that is the one and only place that can send a query to your database, and this method must have some mechanism for accepting parameter data. Other methods should not be passing sql around. That leads to injection issues.

每个问题你要求数据属于自己的方法。理想情况下，这些方法将聚集在一个类或一组类中，这些类在一个项目中收集用于较大的应用程序。

Each question you ask the data belongs in it's own method. Ideally, these methods are gathered together in a class, or group of classes gathered in a single project for larger applications.

这篇关于Sql注入参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！