modifying a php sql query to take an ajax paramter

Question

Hello,

I have this code:

<?php
session_start();
if (isset($_GET["cmd"]))
  $cmd = $_GET["cmd"];
else
  die("You should have a 'cmd' parameter in your URL");
$con = mysql_connect("localhost","xxx","xxx");
if(!$con)
{
die('Connection failed because of' .mysql_error());
}
mysql_select_db("ebay",$con);
if($cmd=="GetAuctionData")
{
echo "<table border='1' width='100%'>
<tr>
<th>Seller ID</th>
<th>Start Date</th>
<th>Description</th>
</tr>";
$sql="SELECT * FROM Auctions WHERE ACCESSSTARTS='8.7.2008 17:18:37'";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result))
{
echo "<tr>
<td>".$row['SELLER_ID']."</td>
<td>".$row['ACCESSSTARTS']."</td>
<td>".$row['ARTICLE_NAME']."</td>
</tr>";
}
echo "</table>";
}
mysql_close($con);
?>

Which is called from ajax javascript file, which in turn is called from the main page. I would like to know how to modify this to take a paramater, so instead of hard coding in $sql, it would return he record that was clicked on. If I click on a record in the main page it will pass the primary key to this code and perform the relevant query.

Please do not suggest a framework, as it is not the answer I am looking for, and would like to know how to write what I want regardless.

Answer 1

The quickest, simplest way to do this is like this:

$sql = "SELECT * FROM Auctions WHERE id = " . (int)$_GET['auction_id'];

The (int) part casts the string from the url to an integer so you're safe from SQL injection. The worst that can happen is it will come out "id = 0" and you'll get no results.

Answer 2

You just want something like

if (!$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT)) {
    die('Id is not a valid integer');
}
$sql="SELECT * FROM Auctions WHERE auction_id = $id";

Answer 3

Calls from your AJAX libraries are the same as any other page load, as far as PHP is concerned. So you get parameters the same way you would for any other page: $_POST, $_GET, or $_REQUEST. ALWAYS check your inputs to make sure they are the type you think they are. In this case either force it to int with (int), or use is_numeric($input) depending on what you want to do with failure, and your style.

Something like:

$input = $_REQUEST['AuctionID'];
if (!is_numeric($input) ) {
    $input = 0;
    // Do something smart to report the error...
}
$sql="SELECT * FROM Auctions WHERE AUCtionID=$input";

Also, get your SQL and your HTML separated so they are easier to work on independently. Start fighting bad habits today.