利用SQL查询中的用户输入

Question

我正在尝试更新一个给定用户输入的表。一旦用户点击表单上的submit，我希望查询的WHERE部分反映用户输入的邮政编码。到目前为止，我的情况是这样的，但不起作用。任何帮助都将不胜感激！

<form id="user-location" method="post" action="#">
      <input id="addressInput" name="addressInput" type="text">
      <input id="submit" onclick="searchLocations()" value="GO" type="button">
</form>

<?php
$con=mysqli_connect("localhost","######","######","######");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$result = mysqli_query($con,"SELECT * FROM Prospects WHERE zip = 'echo $_POST['addressInput']'");

echo "<table width='540' cellpadding='0' border='0' cellspacing='0'>
<tr>
<th>Under 4</th>
<th>5 - 9</th>
<th>10 - 14</th>
<th>15 - 17</th>
<th>18 - 20</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['cy_pop_04'] . "</td>";
  echo "<td>" . $row['cy_pop_59'] . "</td>";
  echo "<td>" . $row['cy_pop_1014'] . "</td>";
  echo "<td>" . $row['cy_pop_1517'] . "</td>";
  echo "<td>" . $row['cy_pop_1820'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysqli_close($con);
?>

Answer 1

将<input id="submit" onclick="searchLocations()" value="GO" type="button">更改为<input id="submit" value="GO" type="submit" name="submit">，然后使用条件语句。

即：if(isset($_POST['submit']))

这是一个准备好的语句方法。

您现在所做的(或打算使用的)方式将使您对SQL injection开放。

<?php
$con=mysqli_connect("localhost","######","######","######");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

if(isset($_POST['submit'])){
$zip = $_POST['addressInput'];

if($query = $con->prepare("SELECT * FROM Prospects WHERE zip=?")){
    $query->bind_param("s", $zip);
    $query->execute();
}

echo "<table width='540' cellpadding='0' border='0' cellspacing='0'>
<tr>
<th>Under 4</th>
<th>5 - 9</th>
<th>10 - 14</th>
<th>15 - 17</th>
<th>18 - 20</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['cy_pop_04'] . "</td>";
  echo "<td>" . $row['cy_pop_59'] . "</td>";
  echo "<td>" . $row['cy_pop_1014'] . "</td>";
  echo "<td>" . $row['cy_pop_1517'] . "</td>";
  echo "<td>" . $row['cy_pop_1820'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

} // closing brace for if(isset($_POST['submit']))

mysqli_close($con);
?>

脚注：

不要这样做或使用：

WHERE zip = 'echo $_POST['addressInput']'
             ^^^^        ^            ^

在使用prepared statements函数时，使用mysqli_*总是更好。

• 关于使用准备好的语句的Here is a tutorial。