Sanitize HTML in SQL query with PHP function

Question

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?

Use http://www.php.net/manual/en/function.utf8-encode.php – Hackerman Jan 03 '14 at 20:21 — Hackerman, Jan 03 '14 at 20:21

score 1 · Answer 1 · edited May 23 '17 at 12:11

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.

Yes, I know I need to escape my SQL queries and ashamed that I haven't yet. I barely know mysqli_*, and I know nothing about PDO. Since this is an internal application for 3-4 users, I'm fine with it as is to get it going. Once functionality is there, I get to back through and secure it and make it pretty. I'm not following with your solution though. I tried adding that string to getsku.php, but I am getting the same behavior. Based on my scripts above, would the correct string of data even be pulled to begin with? As in, where in my script is it getting fudged? — dhorn, Jan 03 '14 at 21:00

score 0 · Answer 2 · answered Jan 03 '14 at 20:42

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.

Sanitize HTML in SQL query with PHP function

2 Answers2