Search string with apostrophe

That snippet strips out non-alphabetic chars from $searchq, except for [\' -] which also may remain. Doesn’t have anything to do with finding last names. The problem lies elsewhere.

I wasn’t clear. User types in Last Name to retrieve record. Last name is checked by the snippet for security. Except the snippet does not return $searchq when the Last name is O’Brien. It accepts Salzmann-Talbi and van Peltz and every other name. ??

For $searchq = "O’Brien";, it’ll return “OBrien”, and fail to match. Note the “curly” or “slanted” apostrophe, not the “straight” '. Try:
$searchq = preg_replace("#[^A-Za-z\'’ -]#i","",$searchq);

Thank you. Tried

$searchq = preg_replace("#[^A-Za-z\'’ -]#i","",$searchq);
$searchq = preg_replace("#[^A-Za-z\'’ -]#i","",$searchq);
$searchq = preg_replace("#[^A-Za-z\'’ -]#i","",$searchq);

To no avail. The entry O’Brien is a straight quote. If the listing isting is OBrien
the edit page can find it.

• This reply was modified 3 years, 9 months ago by bcworkz.

A simple test:

$searchq = "O'Brien";
$searchq = preg_replace("#[^A-Za-z\' -]#i","",$searchq); 
echo $searchq; 

Outputs: O'Brien
Similar result if we include ’ in both the sample and regexp. If that’s representative of what is saved in the DB, we’re back to the problem lies elsewhere. The preg_replace() is not corrupting the search term as we suspected.

Are you sure O'Brien is exactly what is stored in the DB? Not some variant of O\x043Brien or similar? You can use phpMyAdmin to verify.

You are right, of course,
$searchq = “O’Brien”;
$searchq = preg_replace(“#[^A-Za-z\’ -]#i”,””,$searchq);
echo $searchq;
got O’BrienO’BrienCould not search!
echo $searchq;
So $searchq = O’Brien, and the answer IS elsewhere, here’s the $query, it must be ‘%searchq%’, what is it choking on? it works for every other name….

$query = mysqli_query($link,”SELECT ID, FirstName, LastName FROM AAUW_Members WHERE FirstName LIKE ‘%$searchf%’ AND Lastname LIKE ‘%$searchq%'”) OR die(“Could not search!”);

The apostrophe is seen as a sole single quote in SQL and causes a syntax error. The search term $searchq should be run through mysqli_real_escape_string() to escape it into \'.

OK. Will search Auchincloss, Salzmann-Talbi, etc. returns FirstName and data but only the O in O’Brien.

$searchf = $_POST['searchf'];
$searchf = preg_replace("#[^A-Za-z\' -]#i","",$searchf); //only characters  and spaces, -
	$searchq = $_POST['search'];
	$searchq = preg_replace("#[^A-Za-z\' -]#i","",$searchq); 
	echo "<br>$searchf<br>";
	echo "<br>$searchq<br>";
	$firstName = mysqli_real_escape_string($link, $searchf);
	$lastName = mysqli_real_escape_string($link, $searchq); //escape special characters
	$query = mysqli_query($link,"SELECT ID,  FirstName, LastName FROM AAUW_Members WHERE FirstName LIKE  '%$firstName%' AND LastName LIKE '%$lastName%' ") OR die("Could not  search!");

If you look at the immediate query results, I think you’ll see that O’Brien was found OK, otherwise the “O” wouldn’t appear at all. Some other code down the line is now treating the apostrophe like a quote so you only get ‘O’ as a result.

You may need to run the results through htmlspecialchars() before further processing. Hard to say without knowing the code involved, but this needs to eventually be done prior to output anyway.

$sql = "SELECT ID, Title, FirstName, LastName, Address1, City, State, Zip, HomePhone, CellPhone, Email, College1, ";
		$sql .= "College2, College3, Birth_Day, Birth_Month, Mem_Type, Honorary, Joined_Local, ";
		$sql .= "Joined_Natl, Mailings, Positions_Held, Notes, ";
		$sql .= "Referred, Retired, Employer, Occupation, Positions "; 
		$sql .= "FROM AAUW_Members WHERE ID = '$ID' ";

fetches the data.
then $LastName = $row[“LastName”];
then echo “<tr><th>Last Name</th><td><input type=’text’ name=’LastName’ value='”.$row[‘LastName’].”‘></td></tr>”; displays it
then `$sql = “UPDATE AAUW_Members SET ID = ‘$ID’, Title = ‘$Title’, FirstName = ‘$FirstName’, LastName = ‘$LastName’, Address1 = ‘$Address1’, City = ‘$City’, State = ‘$State’, Zip = ‘$Zip’, HomePhone = ‘$HomePhone’, CellPhone = ‘$CellPhone’, Email = ‘$Email’, College1 = ‘$College1’, College2 = ‘$College2′, College3 =’$College3’,Birth_Day = ‘$Birth_Day’, Birth_Month = ‘$Birth_Month’, Mem_Type = ‘$Mem_Type’, Honorary = ‘$Honorary’, Joined_Local = ‘$Joined_Local’, Joined_Natl = ‘$Joined_Natl’, Mailings = ‘$Mailings’, Positions_Held = ‘$Positions_Held’, Notes = ‘Notes’,Referred = ‘$Referred’, Retired = ‘$Retired’, Employer = ‘$Employer’,Occupation = ‘$Occupation’, Positions = ‘$Positions’
WHERE ID = ‘$ID’ “;`
updates in the formhandler.
Shouldn’t I be able to somehow insulate the quote in searchq in the beginning so it would carry through?

There’s no one single way to insulate (or “escape” in coder-speak). How to do so changes over the course of the data flow. Before any form data is used in any SQL, whether select or update, it should be run through mysqli_real_escape_string() to escape quotes and similar chars.

With data returned from a SELECT query, run it through htmlspecialchars() to avoid quote conflicts. The function will change the quote to &#O39; which will still render as an apostrophe in browsers.

Doing so complies with the general rule that we should escape all input, then escape again when output. What constitutes proper escape varies by context.

• This reply was modified 3 years, 9 months ago by bcworkz.

OK, thank you. Will try htmlspecialchars() next.