纠正连接查询。 C＃和MySQL [英] correcting concatenation query. C# and MySQL

问题描述

我的数据库中有一个表有4列Specie |价格|股票|国家。和两个下拉列表，第一个是国家一旦选择国家，第二个下拉列表显示分配给该国家的物种。这一切都很好。



问题



我选择了索引更改显示2个标签，选择了Price和Specie。问题在于标签没有显示所选国家/地区的正确打印，我知道它的查询，但我尝试了几种变化，似乎无法让它正常工作，建议或帮助将非常感激。 />


以下查询的代码

 < span class =code-keyword> protected   void  DropDownList1_SelectedIndexChanged（ object  sender，EventArgs e ）
 {
  string  selection_price = DdPetPist.SelectedValue; 
  string  selection_stock = DdPetPist.SelectedValue; 
  string  petPrice =  string  .Empty; 
  string  available =  string  .Empty; 
 
 MySqlCommand cd_price =  new  MySqlCommand（ String  .Format（  SELECT Price FROM Animals WHERE Specie ='{1}'，ddlcountry.Text，selection_price），cs）; 
 MySqlCommand cd_available =  new  MySqlCommand（ String  .Format（  SELECT Stock FROM FROM WHERE Specie ='{0}'，ddlcountry.Text，selection_stock），cs）; 
 
 cs.Open（）; 
 petPrice = Convert.ToString（cd_price.ExecuteScalar（））; 
 available = Convert.ToString（cd_available.ExecuteScalar（））; 
 cs.Close（）; 
 
 PetPrice.Text =  String  .Format（  A {0}的最低捐款是£{1}。，selection_price，petPrice）; 
 Availble.Text = 字符串 .Format（  {0}你所在国家的Avalible {1}。，selection_stock，available）; 
} 

解决方案

解决方案1的一个至关重要的补充：



术语连接会立即使您的方法变得可疑。是的，这是错误的。实际上，您不使用sting连接来使用某些用户提供的数据来形成查询。这很好， string.Format 与连接没什么关系（重复时效率很低，因为字符串完全是不可变的）。但另一件事情完全不好：你不应该根据用户输入创建查询。它打开了通向名为 SQL注入的众所周知的漏洞的大门。这是它的工作原理：

http://xkcd.com/327 [ ^ ] :-)。



怎么办代替？那么，您需要使用参数化语句： http://msdn.microsoft。 com / en-us / library / ff648339.aspx [ ^ ]。



请参阅我过去的答案以获得进一步的解释：

在com.ExecuteNonQuery（）中重新启动; [ ^ ]，

hi name没有显示在名称中？ [ ^ ]。

-SA

您的查询没有意义。 string.Format未正确使用。它是零指数函数。在WHERE子句中，您要检查Specie是否等于selection_price。您的SELECT语句应如下所示：

 MySqlCommand cd_price = new MySqlCommand（String.Format（SELECT Price FROM Animals WHERE Specie ='{ 0}'，物种价值），cs）; 
 MySqlCommand cd_available = new MySqlCommand（String.Format（SELECT Stock FROM Animals WHERE Specie ='{0}'，specie的值），cs）; 
 

您将需要一个有效值来检查Specie或更改您的查询以实际检查价格WHERE子句。



当然，你不应该将连接字符串放入SQL语句中。您应该使用参数化查询。

I have a table in my data base with 4 columns Specie | Price | Stock | Country. and two drop down list, the first is Country once the country is selected the second drop down Shows the Specie allocated to the country. This all works well.

Problem

I have select index change that shows 2 label's with Price and Specie selected. problem being is that the label is not showing the correct print out of the country selected, I know its the query but I have tried several variations and can not seem to get it to work correct, advise or help will be much appreciated.

Code with query below

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        string selection_price = DdPetPist.SelectedValue;
        string selection_stock = DdPetPist.SelectedValue;
        string petPrice = string.Empty;
        string available = string.Empty;

        MySqlCommand cd_price = new MySqlCommand(String.Format("SELECT Price FROM Animals WHERE Specie ='{1}'", ddlcountry.Text, selection_price), cs);
        MySqlCommand cd_available = new MySqlCommand(String.Format("SELECT Stock FROM Animals WHERE Specie ='{0}'", ddlcountry.Text, selection_stock), cs);

        cs.Open();
        petPrice = Convert.ToString(cd_price.ExecuteScalar());
        available = Convert.ToString(cd_available.ExecuteScalar());
        cs.Close();

        PetPrice.Text = String.Format("Minimum Donation For A {0}  Is £{1}.", selection_price, petPrice);
        Availble.Text = String.Format("{0}'s Avalible {1} In Your Country.", selection_stock, available);
    } 

解决方案

A critically important addition to Solution 1:

The term "concatenation" immediately makes your approach suspicious. And yes, it is wrong. Actually, you don't use sting concatenation to form your query using some user-supplied data. And this is good, string.Format has little to do with concatenation (with is inefficient when repeated, because strings are totally immutable). But the other thing is totally bad: you should not creates queries from the user input at all. It opens wide doors to the well-known exploit called SQL injection. This is how it works:
http://xkcd.com/327[^] :-).

What to do instead? Well, you need to use parametrized statements: http://msdn.microsoft.com/en-us/library/ff648339.aspx[^].

Please see my past answers for further explanations:
EROR IN UPATE in com.ExecuteNonQuery();[^],
hi name is not displaying in name?[^].

—SA

Your query does not make sense. The string.Format is not being used correctly. It is a zero index function. In your WHERE clause you are checking if "Specie" is equal to "selection_price". Your SELECT statements should read like this:

MySqlCommand cd_price = new MySqlCommand(String.Format("SELECT Price FROM Animals WHERE Specie ='{0}'", value for specie), cs);
     MySqlCommand cd_available = new MySqlCommand(String.Format("SELECT Stock FROM Animals WHERE Specie ='{0}'", value for specie), cs);

You will need a valid value to check the "Specie" against or change your query to actually check for price in the WHERE clause.

Of course, you should not be concatenation strings into an SQL statement. You should be using parameterized queries.

这篇关于纠正连接查询。 C＃和MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！