c# - 搜索查询中的引号错误

Question

我正在使用 SQL 查询显示报告。在此报告中，用户可以按客户搜索。所以我创建了一个搜索功能。搜索字段是客户(从下拉列表中选择)，搜索词是用户输入文本框进行搜索的内容:

StringBuilder SQL = new StringBuilder(SearchSQL);
if (SearchFieldKey != null && SearchFieldKey.Length > 0)
{
  if (SearchTerms != null)
  {
    SQL.Append(" HAVING ");
    for (int i = 0; i < SearchFieldKey.Length; i++)
    {
      if (SearchFields.ContainsKey(SearchFieldKey[i]))
      {
        SQL.Append(SearchFields[SearchFieldKey[i]] + " LIKE ?parameter" + i.ToString());
        param.Add(new MySqlParameter("parameter" + i.ToString(),
          "%" + SearchTerms[i] + "%"));

        if (i != SearchFieldKey.Length - 1)
          SQL.Append(" OR ");
      }
      else
        throw new Exception("Error: Attempted to search on invalid field. Check SearchFields Argument.");
    }
  }
}

SQL.Append(" '); ");
SQL.Append ("prepare stmt from @sql; execute stmt; deallocate prepare stmt;");

此函数将 HAVING 查询添加到显示报表的查询 (SearchSQL) 的末尾。问题在于 LIKE 附近的引号。查询的末尾返回:

WHERE c.Company_ID = ', 135,
' GROUP BY c.ID  HAVING c.Name LIKE "%TEST%" ');

但是因为引号在语句的末尾，它无法读取传递给它的参数，所以我得到了这个错误:

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 '?parameter0' at line 23

所以我需要这样的查询:

WHERE c.Company_ID = ', 135,
' GROUP BY c.ID HAVING c.Name LIKE', "%TEST%" );

看到报价已移至 LIKE 之后并添加了一个逗号。这是我需要做的来使查询工作。但是当我尝试在我当前的代码中执行此操作时，它会导致错误。

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 '%TEST%' at line 23

那么我需要在搜索功能中做什么呢？

我将查询粘贴到 MySQL Workbench 中，以便更清楚地了解发生了什么。

问题出在这里:

看看 LIKE "%PRL%"是如何全绿的，因为它包含在引号中，所以它没有读取参数。

现在看看它应该是怎样的:

在 like 之后关闭引号，现在可以读取参数。所以问题出在我的代码上。我需要更改什么才能让它发挥作用？

这是完整的 SearchSQL 函数:

private static string SearchSQL
{
  get
  {
    return @"SET group_concat_max_len=10000000;
             set @sql = null;
             select group_concat(distinct
               concat('MAX(CASE WHEN pt.Code = ''', 
                             pt.Code ,
                           ''' THEN jp.AdvisedQty ELSE 0 END) AS `',
                           pt.Code, '`')
                     ) into @sql
                                                                                            FROM customer c
                 LEFT JOIN job_address ja ON c.AccountCode = ja.Code AND c.Company_ID = ja.Company_ID
                 JOIN  AddressType jat ON ja.AddressType = jat.ID and jat.Description = 'Debtor'
                 LEFT JOIN job_new jn ON ja.JobID = jn.ID
                 LEFT JOIN job_pieces jp ON ja.JobID = jp.ID
                 LEFT JOIN piecestype pt on jp.TypeID = pt.ID
                 WHERE c.Company_ID = ?compid;

                 set @sql = concat('select c.Name, COUNT(distinct jn.ID) as Jobs,
                   SUM((select COUNT(ID) from jobstat where Status = ''DEL'' AND JobID = jn.ID)) as Delivered,
                  SUM((select COUNT(ID) from jobstat where Status = ''POD'' AND JobID = jn.ID)) as POD,
                  (select COUNT(job_debriefs.ID) from job_debriefs WHERE JobID = jn.JobNo) as Debriefs,
                  sum(jn.OutTurn) as Outturn,
                  SUM(jn.ActualWeight) as GrossWt,
                  SUM(jn.CBM) as CBM,
                  jn.Department,
                  (SELECT Name FROM job_address WHERE AddressType =3 AND JobID = jn.ID) as CollectName,
                  (SELECT Name FROM job_address WHERE AddressType =2 AND JobID = jn.ID) as DeliverName,
                  ', @sql, ' 
                  FROM customer c
                   LEFT JOIN job_address ja ON c.AccountCode = ja.Code AND c.Company_ID = ja.Company_ID
                   JOIN  AddressType jat ON ja.AddressType = jat.ID and jat.Description = ''Debtor''
                   LEFT JOIN job_new jn ON ja.JobID = jn.ID
                   LEFT JOIN job_pieces jp ON ja.JobID = jp.ID
                   LEFT JOIN piecestype pt on jp.TypeID = pt.ID
                   WHERE c.Company_ID = ', ?compid,
                    ' GROUP BY c.ID";
  }
}

Answer 1

下面是这个问题的答案:

 StringBuilder SQL = new StringBuilder(SearchSQL);
            if (SearchFieldKey != null && SearchFieldKey.Length > 0)
            {
                if (SearchTerms != null)
                {
                    SQL.Append(" HAVING ");
                    for (int i = 0; i < SearchFieldKey.Length; i++)
                    {
                        if (SearchFields.ContainsKey(SearchFieldKey[i]))
                        {

                            SQL.Append(SearchFields[SearchFieldKey[i]] + " LIKE ', ?parameter" + i.ToString());
                            param.Add(new MySqlParameter("parameter" + i.ToString(), "\'%" + SearchTerms[i] + "%\'"));

                            if (i != SearchFieldKey.Length - 1)
                                SQL.Append("', OR ");

                        }
                        else
                            throw new Exception("Error: Attempted to search on invalid field. Check SearchFields Argument.");
                    }
                }

            }
            else
            {
                SQL.Append("'");
            }

            SQL.Append("); ");
            SQL.Append ("prepare stmt from @sql; execute stmt; deallocate prepare stmt;");

我在 LIKE 后遗漏了一个空格，我添加了一个 else 来结束引用