c# - 从 mySQL 数据库中提取记录仅适用于用户 ID，不适用于电子邮件

Question

此功能有效，因为我按用户 ID 搜索:

private void showList_Click(object sender, EventArgs e)
{
   int id = 0;
   for (int i = 0; i <= sqlClient.Count("UserList"); i++)
   {
      Dictionary<string, string> dik = sqlClient.Select("UserList", "userid = " + id);

      var lines = dik.Select(kv => kv.Key + ": " + kv.Value.ToString());
      userList.AppendText(string.Join(Environment.NewLine, lines));
      userList.AppendText(Environment.NewLine);
      userList.AppendText("--------------------------------------");
      id++;
   }
}

此功能不起作用，因为我通过电子邮件搜索:

private void login_Click(object sender, EventArgs e)
{
   string email = lemail.Text;
   Dictionary<string, string> dik = sqlClient.Select("UserList", "firstname = " + email);
   var lines = dik.Select(kv => kv.Key + ": " + kv.Value.ToString());
   logged.AppendText(string.Join(Environment.NewLine, lines));
}

这是我单击登录按钮时收到的错误消息:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@aol.com' at line 1

我在数据库中搜索的电子邮件是“aces@aol.com”，不带引号。错误消息让我相信 @ 符号引起冲突，因为我知道它是一个特殊字符，但我很难弄清楚要搜索什么短语来帮助我。

此外，这是正在调用的函数:

public Dictionary<string, string> Select(string table, string WHERE)
{
   //This methods selects from the database, it retrieves data from it.
   //You must make a dictionary to use this since it both saves the column
   //and the value. i.e. "age" and "33" so you can easily search for values.

   //Example: SELECT * FROM names WHERE name='John Smith'
   // This example would retrieve all data about the entry with the name "John Smith"

   //Code = Dictionary<string, string> myDictionary = Select("names", "name='John Smith'");
   //This code creates a dictionary and fills it with info from the database.

   string query = "SELECT * FROM " + table + " WHERE " + WHERE + "";

   Dictionary<string, string> selectResult = new Dictionary<string, string>();

   if (this.Open())
   {
      MySqlCommand cmd = new MySqlCommand(query, conn);
      MySqlDataReader dataReader = cmd.ExecuteReader();

      try
      {
         while (dataReader.Read())
         {
            for (int i = 0; i < dataReader.FieldCount; i++)
            {
               selectResult.Add(dataReader.GetName(i).ToString(), dataReader.GetValue(i).ToString());
            }
         }
         dataReader.Close();
      }
      catch { }
      this.Close();
      return selectResult;
   }
   else
   {
      return selectResult;
   }
}

我的数据库表名为“UserList”

字段顺序如下:

用户 ID、电子邮件、密码、姓氏、名字

任何帮助将不胜感激。这个网站太棒了!

Answer 1

编辑:史蒂夫说得对，我错过了单引号，但很确定您需要搜索电子邮件字段，而不是名字字段。

您的电子邮件地址存储在名字字段中？

将其更改为您的电子邮件字段

private void login_Click(object sender, EventArgs e)
{
    string email = lemail.Text;
    Dictionary<string, string> dik = sqlClient.Select("UserList", "YOUR_EMAIL_FIELD = " + email);
    var lines = dik.Select(kv => kv.Key + ": " + kv.Value.ToString());
    logged.AppendText(string.Join(Environment.NewLine, lines));
}

此外，请研究 SQL 注入(inject)并使用参数化查询。