logo标签列表

PHP MySQL 查询参数化问题

phphtmlmysqljsonparameters
3

问题

您好。我有以下完整的代码：

<?php
     require("config.inc.php");

     // initial query
     $query = "SELECT * FROM `appdrgreenthumb_plant_species`";

     // counter for the number of conditions filled by user
     // useful for determining when to add "AND" keywords to the query
     $numConditions = 0;

     // check if any of the fields are filled
     if (!isset($_POST['btnSubmit']))
     {
?>

<!DOCTYPE html>
<html>
  <head>
    <title>Search Dr. Green Thumb's Plant Infobook</title>
    <style>
      body { 
        font-family: "Courier New", sans-serif;
        font-size: 1em;
        }
      input[type="submit"] {
        font-family: "Courier New", sans-serif;
        font-size: 1em;
        width: 100%;
        height: 1.5em;
        }
      .wrapper { 
        width: 30em;
        padding: 0.5em;
        margin: 0 auto;
        }
      legend {
        font-size: 150%;
        }
      form {
        }
      .include_items {
        width: 50%;
        float: left;
        margin: 0.5em 0 0.5em;
      }
      .form-group {
        margin: 0.3em 0 0.3em;
        }
      .form-group label {
        float: left;
        width: 30%;
        text-align: right;
        margin-right: 0.1em;
        }
      .form-group input {
        width: 68%;
        }
    </style>
  </head>
  <body>
    <div class="wrapper">
      <form method="post" action="dictionary_search.php">
        <fieldset>
          <legend>Search</legend>
          <div class="form-group">
            <label>Plant ID:</label>
            <input type="text" name="id" value="" />
          </div>
          <div class="form-group">
            <label>Plant Name:</label>
            <input type="text" name="name" value="" />
          </div>
          <div class="form-group">
            <label>Plant Family:</label>
            <input type="text" name="family" value="" />
          </div>

          <div class="include_items">
            <fieldset>
              <input type="checkbox" name="cb_id" value="id">Plant ID<br />
              <input type="checkbox" name="cb_name" value="name">Plant Name<br />
              <input type="checkbox" name="cb_family" value="plant">Plant Family<br />
            </fieldset>
          </div>

          <!-- submit button -->
          <input type="submit" value="Search" name="btnSubmit" />
        </fieldset>
      </form>
    </div>
  </body>
</html>

<?php
     }
     else
     {
          if (!empty($_POST['id']) ||
              !empty($_POST['name']) ||
              !empty($_POST['family'])
              )
          {
               $query = $query . " WHERE ";

               // check if the ID field is filled
               if (!empty($_POST['id']))
               {
                    $numConditions++;
                    $query = $query . InfoBookUtilities::appendAND($numConditions) . "`id`" . " = " . ":id";
                    $query_params[':id'] = $_POST['id']; // this is to avoid SQL injection
               }

               // check if the NAME field is filled
               if (!empty($_POST['name']))
               {
                    $numConditions++;
                    # $query = $query . InfoBookUtilities::appendAND($numConditions) . "`name`" . " LIKE " . "'%:name%'";
                    # $query_params[':name'] = $_POST['name']; // this is to avoid SQL injection
               }

               // check if the FAMILY field is filled
               if (!empty($_POST['family']))
               {
                    $numConditions++;
                    # $query = $query . InfoBookUtilities::appendAND($numConditions) . "`family`" . " LIKE " . "'%:family%'";
                    # $query_params[':family'] = $_POST['family']; // this is to avoid SQL injection
               }

               # echo $query;
          }
?>

<?php
// Run the query:
     try 
     {
        // These two statements run the query against your database table. 
        $stmt   = $db->prepare($query);
        $result = $stmt->execute($query_params);
     }
     catch (PDOException $ex) 
     {
        $response["success"] = 0;
        $response["message"] = "Database Error! <br />Exception: " . $ex->getMessage();
        die(json_encode($response));
     }

     // finally, we can retrieve all of the found rows into an array using fetchAll 
     $rows = $stmt->fetchAll();

     if ($rows) 
     {
        $response["success"] = 1;
        $response["message"] = "Records Available!";
        $response["plants"]   = array();

        foreach ($rows as $row) {
            $plant                           = array();
            if (isset($_POST['cb_id']))
                 $plant["id"]                     = $row["id"];
            if (isset($_POST['cb_name']))
                 $plant["name"]                   = $row["name"];
            if (isset($_POST['cb_family']))
                 $plant["family"]                 = $row["family"];
            if (isset($_POST['cb_price_floor']) || !empty($_POST['cb_price_ceiling']))     
                 $plant["price"]              = $row["price"];
            if (isset($_POST['cb_introduction']))
                 $plant["introduction"]           = $row["introduction"];
            if (isset($_POST['cb_climate_and_soil']))
                 $plant["climate_and_soil"]       = $row["climate_and_soil"];
            if (isset($_POST['cb_temperature_min']))
                 $plant["temperature_min"]        = $row["temperature_min"];
            if (isset($_POST['cb_temperature_max']))
                 $plant["temperature_max"]        = $row["temperature_max"];
            if (isset($_POST['cb_soil_type']))
                 $plant["soil_type"]              = $row["soil_type"];
            if (isset($_POST['cb_soil_ph_min']))
                 $plant["soil_ph_min"]            = $row["soil_ph_min"];
            if (isset($_POST['cb_soil_ph_max']))
                 $plant["soil_ph_max"]            = $row["soil_ph_max"];
            if (isset($_POST['cb_recommended_varieties']))
                 $plant["recommended_varieties"]  = $row["recommended_varieties"];
            if (isset($_POST['cb_land_preparation']))
                 $plant["land_preparation"]       = $row["land_preparation"];
            if (isset($_POST['cb_crop_management']))
                 $plant["crop_management"]        = $row["crop_management"];
            if (isset($_POST['cb_care']))
                 $plant["care"]                   = $row["care"];
            if (isset($_POST['cb_nutrition_management']))
                 $plant["nutrition_management"]   = $row["nutrition_management"];
            if (isset($_POST['cb_water_management']))
                 $plant["water_management"]       = $row["water_management"];
            if (isset($_POST['cb_harvest_management']))
                 $plant["harvest_management"]     = $row["harvest_management"];
            if (isset($_POST['cb_pests']))
                 $plant["pests"]                  = $row["pests"];
            if (isset($_POST['cb_diseases']))
                 $plant["diseases"]               = $row["diseases"];
            if (isset($_POST['cb_date_sow_begin']))
                 $plant["date_sow_begin"]         = $row["date_sow_begin"];
            if (isset($_POST['cb_date_sow_end']))
                 $plant["date_sow_end"]           = $row["date_sow_end"];
            if (isset($_POST['cb_growth_min']))
                 $plant["growth_min"]             = $row["growth_min"];
            if (isset($_POST['cb_growth_max']))
                 $plant["growth_max"]             = $row["growth_max"];
            if (isset($_POST['cb_image_location']))
                 $plant["image_location"]         = $row["image_location"];

            // update our repsonse JSON data
            array_push($response["plants"], $plant);
        }

        // echoing JSON response
        echo json_encode($response);
     }

     else 
     {
        $response["success"] = 0;
        $response["message"] = "No Records Available!";
        die(json_encode($response));
     }
?>

<?php 
     }
?>

<?php
     class InfoBookUtilities {
          public static function appendAND ($numberOfConditions) {
               if ($numberOfConditions == 1) 
                    return ""; 
               else
                return " AND ";
          }
     }
?>

长话短说：当我使用“id”或“name”文本框过滤结果时，即使应该有记录也没有。假设我的表中有这个记录：
enter image description here
然后，我在名称文本框中输入单词“carrot”，并选中所有复选框。我认为应该能得到一条记录，但实际上却没有。
{"success":0,"message":"No Records Available!"}

我应该在什么时候获取这个？

{"success":1,"message":"Records Available!","plants":[{"id":"1","name":"Carrot","family":"Umbelliferae"}]}

另一个问题是，当我尝试在两个文本字段一起搜索时，我会收到以下错误：

{"success":0,"message":"Database Error! 
Exception: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens"}

我认为这只是因为我添加参数的方式有问题，因为当我像这样编写代码时（虽然这是不好的实践）：

// check if the NAME field is filled
           if (!empty($_POST['name']))
           {
                $numConditions++;
                # $query = $query . InfoBookUtilities::appendAND($numConditions) . "`name`" . " LIKE " . "'%:name%'";
                # $query_params[':name'] = $_POST['name']; // this is to avoid SQL injection
                $query = $query . InfoBookUtilities::appendAND($numConditions) . "`name`" . " LIKE " . "'%" . $_POST['name'] . "%'";
           }

           // check if the FAMILY field is filled
           if (!empty($_POST['family']))
           {
                $numConditions++;
                # $query = $query . InfoBookUtilities::appendAND($numConditions) . "`family`" . " LIKE " . "'%:family%'";
                # $query_params[':family'] = $_POST['family']; // this is to avoid SQL injection
                $query = $query . InfoBookUtilities::appendAND($numConditions) . "`family`" . " LIKE " . "'%" . $_POST['family'] . "%'";
           }

我完成了工作。

其他细节：

我可以很好地过滤出id字段（使用我在顶部粘贴的代码——是的，那个冗长的代码）。


我通过HTML表单的文本框字段自定义（设置WHERE子句条件）我的MySQL查询。复选框允许我过滤掉每行中要保留的“属性”，然后最终堆积到我的JSON数组中。

它看起来像这样：
What the Web Page Actually Looks Like

我已经看了几个小时了，但我真的搞不清楚问题出在哪里。 :/

取消注释此行 # echo $query; 并检查查询是否符合您的预期？ - user2417483
哦，我为了这篇文章取消了注释。查询看起来对我来说很好。按照我的“胡萝卜”示例：查询看起来像这样：“SELECT * FROM appdrgreenthumb_plant_species WHERE name LIKE '%:name%'”。我不知道:name部分是否应该像那样显示，还是应该是'%carrot%'，因为当我使用ID文本字段进行搜索时，我有查询：“SELECT * FROM appdrgreenthumb_plant_species WHERE id = :id”，它是一个成功的搜索。 :/ - user4835507
1
从占位符周围删除单引号，因为这将由系统自动完成。然后尝试将通配符添加到占位符值中，而不是在占位符周围使用它们 -> ... \name`" . " LIKE " . ":name";和$query_params[':name'] = "%".$_POST['name']."%";` - Sean
明白了。非常感谢。 - user4835507
@IReallyWantToStayAnonymous，问题解决了吗？如果解决了，我可以将其添加为答案以便关闭。 - Sean
@Sean：是的，没错。请执行。 - user4835507
1个回答
0
通过将您的占位符包装在'%..%'中，您的最终值将如下所示 - '%'carrot'%'。相反，您希望将通配符附加到$_POST值上，即"%".$_POST['name']."%"，并允许PDO为您添加引号。因此，现在您的代码应该是这样的 - 

           // check if the NAME field is filled
           if (!empty($_POST['name']))
           {
                $numConditions++;
                $query = $query . InfoBookUtilities::appendAND($numConditions) . "`name`" . " LIKE " . ":name";
                $query_params[':name'] = "%".$_POST['name']."%"; // this is to avoid SQL injection
           }

           // check if the FAMILY field is filled
           if (!empty($_POST['family']))
           {
                $numConditions++;
                $query = $query . InfoBookUtilities::appendAND($numConditions) . "`family`" . " LIKE " . ":family";
                $query_params[':family'] = "%".$_POST['family']."%"; // this is to avoid SQL injection
           }
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接