I'm trying to do a live search with JQuery, PHP, and MySQL. I'm no expert but I know and understand enough to be dangerous. At any rate, everything seems to be working except for when any of my search results contain single or double quotes. For example, my search results may contain:
Contact, door 3/4" recessed
Motion, detector 35' x 50'
And so on and so on.
My code is:
<script type="text/javascript">
function lookup(inputString) {
if(inputString.length == 0) {
// Hide the suggestion box.
$('#suggestions').hide();
} else {
$.post("get_parts.php", {queryString: ""+inputString+""}, function(data){
if(data.length >0) {
$('#suggestions').show();
$('#autoSuggestionsList').html(data);
}
});
}
}
function fill(thisValue) {
$("#inputString").val(thisValue);
setTimeout("$('#suggestions').hide();", 200);
}
</script>
and
<?php
if(isset($_POST['queryString'])) {
$queryString = $_POST['queryString'];
if(strlen($queryString) >0) {
$query = "SELECT short_desc
FROM equipment
WHERE short_desc
LIKE '$queryString%'
ORDER BY short_desc
ASC LIMIT 10";
$result = mysql_query($query) or die("There is an error in database");
while($row = mysql_fetch_array($result)){
$escaped_desc_html = htmlentities($row['short_desc']);
$escaped_desc_escape = addslashes($row['short_desc']);
echo
"<li onClick=\"fill('".$escaped_desc_html."');\">".$escaped_desc_html."</li>";
};
};
};
?>
I've tried storing the data in MYSQL like this:
Contact, door 3/4\" recessed
Motion, detector 35\' x 50\'
But then it comes out on the page looking just like that.
I've tried addslashes(), htmlentities(), combinations of both, and it keeps breaking. I tried escape() in the javascript too but then I don't get any formatting and the list is a continuous block of jumbled text.
My problem is when the results of my search are passed back to Jquery, that quote or double quotes makes it think that the command if over and I get an error about a missing ). Is there a way to make Jquery ignore any html, slashes, or quotes that come back as data? Maybe have it treat it like an object instead of commands. Even with htmlentities() the &quo; still breaks the code. It's driving me crazy! The problem always breaks in these two places:
$('#autoSuggestionsList').html(data);
and$("#inputString").val(thisValue);
You should use mysql_real_escape_string for escaping variable in MySQL queries. More importantly, though, you need to be very careful to properly escape both content you put in your database and also content that you render to the user... you should never trust input provided by the user, because if you do, you will come across at least one malicious hacker who exploits such a vulnerability.
The best way to avoid this is to:
Unit test each component, and exercise validation in unit tests (make sure that there is proper validation / escaping code in place and that it actually works, at each component).
Use fuzzers to automatically generate inputs to try to find these issues. For example, could a user generate an input such that it can delete your entire database?
You leave yourself WIDE OPEN to SQL injection attacks with the code above. You need to call mysql_real_escape_string() on the value before you execute the SQL statement.
I had a similar problem. As my problem was only with double quotes i converted them to " on input using:
$OUTPUT = str_replace("\"",""",$INPUT);
If this causes a problem for you javascript you could use the same code to replace the " with a "-" or something.
When you do get to the user input stage be sure to protect yourself from injection attacks like others have mentioned!
Sorry just seen the date on this one!! hope this helps someone though