Utilize user input in SQL query

Question

I'm trying to update a table given user input. Once the user hits submit on the form, I want the WHERE portion of my query to reflect the zip code entered by the user. Here is what I have so far, but it doesn't work. Any help would be greatly appreciated!

<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);
?>

`WHERE zip = '$_POST[addressInput]'` and not `WHERE zip = 'echo $_POST['addressInput']'` plus, you shouldn't be using this method; you're open to SQL injection; use prepared statements. Make sure you also have a valid JS function for your `searchLocations()` — Funk Forty Niner, Mar 14 '14 at 13:33
use `$zip = mysqli_real_escape_string($con,$_POST['addressInput'])` and use `WHERE zip = '$zip'` at-least if not using prepare statements. — Abhik Chakraborty, Mar 14 '14 at 13:36
See [`this link`](http://www.tutorialized.com/tutorial/PHP-5:-MySQLi-Prepared-Statements/41452) on using prepared statements. — Funk Forty Niner, Mar 14 '14 at 13:39
Thanks for the help! I made those changes but it doesn't seem to be updating my table. Is my submit button correct? (I removed the searchLocations() as well) — user2155400, Mar 14 '14 at 13:42
You're welcome. Are you using both your form and PHP inside the same page? @user2155400 If so, change `` to `` then use a conditional statement. — Funk Forty Niner, Mar 14 '14 at 13:43
Also, you say it's not updating your table. That's because you're not doing an INSERT or UPDATE. You're only doing a SELECT @user2155400 In your case, since you're using a WHERE clause, you need to use UPDATE and you need to choose which column to UPDATE. — Funk Forty Niner, Mar 14 '14 at 13:46
I posted something for you below using prepared statements instead. @user2155400 — Funk Forty Niner, Mar 14 '14 at 13:52

score 1 · Answer 1 · edited May 23 '17 at 10:33

Change <input id="submit" onclick="searchLocations()" value="GO" type="button"> to <input id="submit" value="GO" type="submit" name="submit"> then use a conditional statement.

I.e.: if(isset($_POST['submit']))

Here is a prepared statement method.

The way you're doing it now (or intended to use), will leave you open to 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);
?>

Footnotes:

Do not do or use this:

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

It's always better using prepared statements when using mysqli_* functions.

Here is a tutorial on using prepared statements.

Utilize user input in SQL query

1 Answers1