So I have a PHP page with a drop down form that when an option is selected, it uses an AJAX script to query a result from the same MySQL table. For the most part, it works like expected. However, some results (specifically, options that have " or ' in the name) are not being set properly to the variable for the AJAX/GET script. Here is my main PHP script:
<html>
<head>
<link rel="stylesheet" type="text/css" href="css/styles.css" media="screen" />
<title>Add Inventory</title>
<script>
function showUser(str)
{
if (str=="")
{
document.getElementById("txtHint").innerHTML="";
return;
}
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","getsku.php?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>
<?php
session_start();
require_once('includes/config.inc.php');
require_once('includes/functions.inc.php');
// Check login status -- if not logged in, redirect to login screen
if (check_login_status() == false) {
redirect('login.php');
}
$thisPage='add';
include('includes/navbar.inc.php');
?>
<h1>Add New Inventory Record</h1>
<form method="POST" action="submitadd.php" />
<table id="add">
<tr>
<td class="headings"><b>Species:</b></td>
<td><select name="species:" onchange="showUser(this.value)">
<option value="select">Choose a Species</option>
<?php
$prodquery="SELECT name FROM products ORDER BY name ASC";
$result=mysqli_query($con,$prodquery) or die(mysqli_error($con));
while ($row = mysqli_fetch_array($result)) {
echo "<option value='" . $row['name'] . "'>" . $row['name'] . "</option>";
}
?>
</select>
</td>
</tr>
<div id='txtHint' />
<tr>
<td class="headings"><b>Fry Count:</b></td>
<td><input type="text" name="frycount" value="<?php echo $row['quantityfry']; ?>" size="35" maxlength="4" /></td>
</tr>
<tr>
<td class="headings"><b>Juvie Count:</b></td>
<td><input type="text" name="juviecount" value="<?php echo $row['quantityjuv']; ?>" size="35" maxlength="4" /></td>
</tr>
<tr>
<td class="headings"><b>Adult Count:</b></td>
<td><input type="text" name="adultcount" value="<?php echo $row['quantityadult']; ?>" size="35" maxlength="4" /></td>
</tr>
<tr>
<td class="headings"><b>Notes:</b></td>
<td><input type="text" name="notes" value="<?php echo $row['notes']; ?>" size="35" maxlength="255" /></td>
</tr>
<tr>
<td class="headings"><b>Location:</b></td>
<td><select name="location">
<?php
$options = set_and_enum_values($con, 'inventory', 'location');
foreach($options as $option):
?>
<option><?php echo $option ?></option>
<?php endforeach; ?>
</select></td>
</tr>
<tr>
<td class="headings"><b>Owner:</b></td>
<td><select name="owner">
<?php
$options = set_and_enum_values($con, 'inventory', 'owner');
foreach($options as $option):
?>
<option><?php echo $option ?></option>
<?php endforeach; ?>
</select></td>
</tr>
</table>
<br />
<input type="submit" name="submit" value="submit" class="button1" />
</form>
</body>
</html>
And here is getsku.php, which is called by the AJAX script:
<?php
$q = html_entity_decode($_GET['q']);
require_once('includes/config.inc.php');
$sql="SELECT sku FROM products WHERE name = '".$q."'";
$result = mysqli_query($con,$sql);
while($row = mysqli_fetch_array($result))
{
echo "<td><input type='hidden' name='sku' value='" . $row['sku'] . "' readonly='readonly' size='35' /></td>";
}
mysqli_close($con);
?>
I've been doing some testing in Firebug and here is a specific example. The row of data is: name = Lethrinops albus "Kande Island" sku = HAP002
There is other data, but not of a concern for this. So when the dropdown selects Lethrinops albus "Kande Island", I want HAP002 set to a hidden field and passed to the submit button on this form. Using Firebug, I can see this under Params: q Lethrinops albus "Kande Island"
Which is correct. Here is another row of data: name = Cynotilapia afra "Lion's Cove" sku = MBN002
But within Firebug, I see this under Params: q Cynotilapia afra "Lion
Which is not correct. I'm assuming I need to sanitize the HTML result, and I found a function that may help:
function htmlsan($htmlsanitize){
return $htmlsanitize = htmlspecialchars($htmlsanitize, ENT_QUOTES, 'UTF-8');
}
But I'm not sure if this is what I need, and how to use it. Can anyone point me in the right direction please?
First of all, you should never construct an SQL query this way:
$q = html_entity_decode($_GET['q']);
$sql="SELECT sku FROM products WHERE name = '".$q."'";
It's not a good practice, and there are security issues like SQL Injection
To solve all your sanitations problems (and many others) I recommend you to use PHP Data Objects (PDO) to all your SQL connections.
Especially take a look at this answer How can I prevent SQL injection in PHP?
Use this function mysql-real-escape-string.php to sanitize you input data in MySQL query.
[EDITED] Answer your question.
The problem is not in your SQL query. It is at <option value='problem is here!'>.
You should use htmlentities to correctly escape the single-quote when name = Cynotilapia afra "Lion's Cove".
echo "<option value='" . htmlentities($row['name']) . "'>" . $row['name'] . "</option>";
You may need to use html_entity_decode to decode (the reverse operation) in getsku.php.
Since you're passing data via GET you need to urlencode() the strings to pass them to the next page. Do not decode them in the next script. The superglobals are already decoded.
Once you're in the next script you should use your DB extension's escape functions to use the $_GET param in your SQL query.