handling data from database that has quotes

[Notretsam]

I think I posted asking about this before, not 100% certain.

 

so one of my members has registered the company name Morgoth's Dungeon , which is in the companyDirectory database table.

 

Am working on a company favourties feature where people can add company to there faves list.

 

I have the below PHP code to show hints as there typing in the input field, which the form coding is below to and the javascript function as well.

 

The part of code $hint="<a href='?corpname=$name'>$name</a><br> on the PHP code below is the issue.

 

As the $name within the link tags (<a>) is showing as Morgoth's Dungeon.

 

However the corpname=$name part shows as Morgoth.

 

Tried stripslashes and mysqli_escape_string, nothing working.

 

anyone got a suggestion on how to get the $name to be correctly added on corpname= part.

<?php
$feeMemb=$_REQUEST["feeMem"];
    /* create a prepared statement */
   $stmtgethintwrest = $conn->prepare("SELECT companyID , companyName FROM companyDirectory ORDER BY companyName");
    /* bind parameters for markers */
    $stmtgethintwrest->bind_param;
    /* execute query */
    $stmtgethintwrest->execute();
    $stmtgethintwrest->bind_result($companyID , $companyName);

    /* fetch value */
   while ($row = $stmtgethintwrest->fetch()) {
	$a[]="$companyName"; 
	}

// get the q parameter from URL
$q=$_REQUEST["q"]; $hint="";

// lookup all hints from array if $q is different from "" 
if ($q !== "")
  { $q=strtolower($q); $len=strlen($q);
    foreach($a as $name)
    { 
	if (stristr($q, substr($name,0,$len)))
      { if ($hint==="")
        { 
		$hint="<a href='?corpname=$name'>$name</a><br>"; }
        else
        { 
		$hint .= "<a href='?corpname=$name'>$name</a><br>"; }
      }
    }
  }
    /* close statement */
	$stmtgethintwrest->close();
// Output "no suggestion" if no hint were found
// or output the correct values 
echo $hint==="" ? "no suggestion" : $hint;
?>

    <form id="myformlarge" action='theme/company/corpopts.php?do=addfave' method='post'>
  <input id="wreID" name="wreID" type="hidden" value="<?php echo "$visitID"; ?>">
<div><input type="text" name="coAdd" value="<?php echo "$corpname"; ?>" size="30" class="edit" maxlength="45" onkeyup="showcorpHint(this.value)">
<div id='txtcorpHint' class='showhint'></div>
<p class="centertext"><input type="submit" name="log" value="Add" size="15" class="button"></p>	
</div>
  
</form>

  <script>
function showcorpHint(str)
{
  if (str=="") {
    document.getElementById("txtcorpHint").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("txtcorpHint").innerHTML=xmlhttp.responseText;
    }
  }
  var feeMem = document.getElementById('wreID').value;
  xmlhttp.open("GET","theme/getcorphint.php?q="+str+ "&feeMemb=" + feeMem,true);
  xmlhttp.send();
  }
</script>

[Notretsam]

FYI

 

I do know the ' in Morgoth's dungeon is acting as the closing ' in this line $hint="<a href='?corpname=$name'>$name</a><br>

 

Just not sure how to format that so it doesn't.

[dsonesuk]

You would have add a slash to escape from being treated as closing tag, else use encoding to convert it from " ' " to ' or hex, decimal encoding code.

 

addslashes()

htmlspecialchars()

Edited May 14, 2016 by dsonesuk

[Notretsam]

using addslashes() I get corpname=Morgoth\

 

using htmlspecialchars , I still get corpname=Morgoth

 

tried , also same with addslashes

$name = htmlspecialchars("$name");
$hint="<a href='?corpname=$name'>$name</a><br>";

Also I manually went into the database and changed the value to Morgoth\'s Dungeon and still shows as corpname=Morgoth

[dsonesuk]

You have to use entity flag for quotes these both htmlentities(), htmlspecialchars() should work

        //$name = htmlentities("Who's Peter Griffin?", ENT_QUOTES);
        $name = htmlspecialchars("Who's Peter Griffin?", ENT_QUOTES);
        echo $name;
        $hint = "<a href='?corpname=$name'>$name</a><br>";
        echo $hint;

[Notretsam]

I presume I replace who's Peter with $name

 

off to try and thank you dsoneuk

[Notretsam]

yup that did the trick, thank you

[justsomeguy]

If the information is going in a URL, the urlencode and rawurlencode are specifically for that.

[Notretsam]

lol love when coding makes you feel like an idiot

 

Had this all working when I posted this, but went to make a change yesterday when I been awake for around 18 hours, never a good idea, so messed it up.

 

Went back to it after a power nap, one of my first thoughts

 

"Instead of sending the company name over, why don't I send the company ID over"

 

would have saved myself a lot of trouble if I had thought of that sooner lol

 

now have below code working , so no longer matters if people put quotes in there company names.

<?php
/* database connection code here */

    /* create a prepared statement */
   $stmtgethintcorp = $connfizzleshizzle->prepare("SELECT companyID, companyName FROM companyDirectory ORDER BY companyName");
    /* execute query */
    $stmtgethintcorp->execute();
    $stmtgethintcorp->bind_result($companyID, $companyName);

    /* fetch value */
	$id = "1";
   while ($row = $stmtgethintcorp->fetch()) {
     $a[]="$companyName"; 
	 $corpid[$id] = "$companyID";
	 $id++;
	}



// get the q parameter from URL
$q=$_REQUEST["q"]; $hint="";
$i = "0";
// lookup all hints from array if $q is different from "" 
if ($q !== "")
  { $q=strtolower($q); $len=strlen($q);
    foreach($a as $name)
    { if (stristr($q, substr($name,0,$len)))
      { if ($hint==="")
        { 
		$i++;
		$hint="<a href='?corpid=$corpid[$i]'>$name</a><br>"; 
		}
        else
        { 
		$i++;
		$hint .= "<a href='?corpid=$corpid[$i]'>$name</a><br>"; 
		}
      }
    }
  }
      /* close statement */
	$stmtgethintcorp->close();
// Output "no suggestion" if no hint were found
// or output the correct values 
echo $hint==="" ? "no suggestion" : $hint;
?>

Edited May 30, 2016 by Notretsam

[Notretsam]

drat, turns out that code doesn't work to well

 

moved it over to another area and turns out it will attach wrong id to name, if you type "O" in field , it adds an id but could be id related to any info with the O.

[justsomeguy]

$id = "1";

while ($row = $stmtgethintcorp->fetch()) {

$a[]="$companyName";

$corpid[$id] = "$companyID";

$id++;

}

What are you doing there? How about this:

 

   $corps = [];
   while ($row = $stmtgethintcorp->fetch()) {
     $corps[$companyID] = $companyName;
   }

Now you can loop through that array and get the database ID and name for each company.

 

Ideally though, your query would include the search term so that the only results you return from the database are those that match what they're searching for.

[Notretsam]

You know like a form select field, the value could be the corp id and the output is the name?

 

I did get an ID outputted with the name results, but sadly it didn't correctly match the result.

 

Just went back to name only and it is working , even with quotes in the outputted result.

 

Was thinking, if I could still get the results via the search query, but actually have the ID sent over instead of name, it make life easier when people use single or double quotes.

Edited June 1, 2016 by Notretsam

[justsomeguy]

Yeah I thought that's exactly what we were just talking about. That's why I suggesting building an array to link the database ID with the database name for each record instead of building multiple arrays and trying to keep them synchronized. You only need 1 array to hold both the ID and name, like I showed. And yes, it's just like the option list for a select element.

[Notretsam]

thank you, I took a note of that and remember it in future, working on something else now, most likely change over to it at some point, but not a big hurry.