c# - 无法从 MYSQL 读取

Question

我想在 Visual Studio 2010 的窗口窗体中显示基于 dateTimePicker 的 P_IN 总数，但出现错误，程序无法从 MySQL 读取数据。下面是我的代码。

cmd = new MySqlCommand("SELECT DATE, P_IN FROM Database.CounterDb WHERE DATE = '" + dateTimePicker1.Text + "'", con);
                      MySqlDataAdapter sdaa = new MySqlDataAdapter(cmd);
                      DataSet dataa = new DataSet();
                      sdaa.Fill(dataa, "Database.CounterDb");
                      reader = cmd.ExecuteReader();
                      int sum = 0;
                       while (reader.Read())
                        {

                             sum = reader.GetInt32(1) + sum;

                          }
                          LCounter.Text = sum.ToString();

Answer 1

这段代码确实很危险，您不得将用户输入直接添加到查询中不要使用

cmd = new MySqlCommand("SELECT DATE, P_IN FROM Database.CounterDb WHERE DATE = '" + dateTimePicker1.Text + "'", con);

用户输入可能会破坏查询，日期文本格式无法采用正确的格式

使用这个(用您的日期格式替换日期格式):

DateTime dt = DateTime.ParseExact(dateTimePicker1.Text, "yyyy-MM-dd HH:mm", CultureInfo.InvariantCulture);
   MySqlCommand cmd = new MySqlCommand();
   cmd.Connection = con;
   cmd.CommandText = "SELECT DATE, P_IN FROM Database.CounterDb WHERE DATE = @dt";
   cmd.Prepare();
   cmd.Parameters.AddWithValue("@dt", dt);

否则，如果您需要在某个范围内搜索，例如 2016-02-20 00:00:00 - 2016-02-20 23:59:59，则必须查询该日期内的所有条目:

DateTime dt = DateTime.ParseExact(dateTimePicker1.Text, "yyyy-MM-dd HH:mm", CultureInfo.InvariantCulture).Date;
DateTime dt1 = dt.AddDays(1);
cmd.CommandText = "SELECT DATE, P_IN FROM Database.CounterDb WHERE DATE > @dt and DATE <@dt1";
cmd.Prepare();
cmd.Parameters.AddWithValue("@dt", dt);
cmd.Parameters.AddWithValue("@dt1", dt1);