Can anyone tell me how to deal with apostrophes in search fields?

I have the following search page: (sorry for the length of code; I thought it best to post the whole page)

<?PHP
session_start();

require_once $_SERVER['DOCUMENT_ROOT'] . "/_dev/_includes/general.php"; 
require_once $_SERVER['DOCUMENT_ROOT'] . "/_dev/_includes/case.php"; 
require_once $_SERVER['DOCUMENT_ROOT'] . '/_dev/_includes/court.php'; 
require_once $_SERVER['DOCUMENT_ROOT'] . '/_dev/_includes/hra_section.php'; 

fCheckAdmin();
?>

<?php
	
	$case = new anu_case();

	//return cases only when the Search button has been clicked 
	//(otherwise display only the Search form - default when first load the page)	
	if ($_REQUEST['form_action']=='search'){
			
		$records_per_page = 10;		
		$page_num = (!empty($_REQUEST['page_num'])) ? $_REQUEST['page_num']: 1;
		
		//collect search parameters from user
		$year = $_REQUEST['year'];
		$keyword = $_REQUEST['keyword'];

		//set the object properties for anu_case()
		$case->citation = $keyword;
		$case->title = $keyword;
		$case->overview = $keyword;
		$case->summary = $keyword;
		$case->other_legislation = $keyword;
		$case->related_links = $keyword;

		$case->court_id= $_REQUEST['court'];
		$case->hra_section_id = $_REQUEST['hra_section'];
		
		//return selected cases
		$rs_case = $case->fPublicSearchCase($year, true, $records_per_page, $page_num);
			
		if(! $rs_case)
			$err_string = '<p>No cases match your search criteria.</p>';
	}
		
	$last_modified = $case->fGetLastModifiedDate();
		
	//data required for filling the court/tribunal and HRA section drop-down lists
	$court = new court();
	$rs_court = $court->fGetCourt();
	
	$hra_section = new hra_section();
	$rs_hra_section = $hra_section->fGethra_section();
		
?>

<script language="javascript1.2">
	<?PHP if ($rs_case) {?>
	
	<!-- Calculate the page number to display -->
	function fDoPageChange(type, oForm){
	
		switch(type){
			case 1:
				oForm.page_num.value = 1;
				break;		
			case 2:
				oForm.page_num.value = <?PHP echo $page_num -1?>;
				break;		
			case 3:
				oForm.page_num.value = <?PHP echo $page_num +1?>;
				break;		
			case 4:
				oForm.page_num.value = <?PHP echo $case->lastpage?>;
				break;		
		}
		oForm.submit();
	}
	<?PHP } ?>

	function fConfirmDelete(title, id){
		if(confirm("You are about to permanently delete the case:\n\"" + title + "\"\nDo you wish to continue?"))
		window.location = 'case_delete.php?id=' + id;
	}

</script>

<!-- START MAIN PAGE CONTENT -->	


	<div class="doublewide">
		<h1><?php echo $title; ?></h1>
		<form class="anuform margintop padtop" action="admin_case.php" method="get" name="frmSearch">
			<fieldset>
				<legend>Search for cases to administer, or add a case</legend>
			
				<input type="hidden" name="form_action" value="search">
				<input type="hidden" name="page_num" value="<?PHP echo $page_num?>">
				
				<p>Filter by</p>
				<p>
					<label for="keyword">keyword</label>
					<input class="text" name="keyword" id="keyword" type="text" size="30" value="<?PHP echo $keyword?>" />
				</p>
				<p>
					<label>year</label>
					<?PHP 
						echo fMakeSelectBoxN('year', $year, 2004 ,date('Y')+1)
					?>
				</p>
				<p>
					<label for="court">court/tribunal</label>
					<select name="court" id="court">
						<option value="">all</option>
						<?PHP foreach ($rs_court as $row){?>
								<option value="<?PHP echo $row['court_id']?>" <?PHP  fIsSelected($row['court_id'], $case->court_id) ?>><?PHP echo $row['court']?></option>			
						<?PHP }?>
					</select>
				</p>
				<p>
					<label for="hra_section">HRA section</label>
					<select name="hra_section" id="hra_section">
						<option value="">all</option>
						<?PHP foreach ($rs_hra_section as $row){?>
								<option value="<?PHP echo $row['hra_section_id']?>" <?PHP  fIsSelected($row['hra_section_id'], $case->hra_section_id) ?>><?PHP echo $row['hra_section']?></option>			
						<?PHP }?>
					</select>				
				</p>
				<p class="instruction">select one or more filters for your search, or just click 'Search' to list all cases</p>
			</fieldset>
			<p class="text-right">
				<input type="submit" name="Submit" value="Search / List all">
				<input type="button" name="Submit" value="Add case" onClick="window.location='case_edit.php';"/>
			</p>				
		</form>
		
		<?PHP
			if ($case->numrows > 1) {
				echo 'The following '.$case->numrows.' cases match your search criteria:<br />';
			} elseif ($case->numrows == 1) {
				echo 'The following case matches your search criteria:<br />';			
			}
		?>
		
		<!-- Calculate and display the start number and end number of cases being displayed -->
		<?PHP
		if ($rs_case){
			$start = (($page_num-1) * $records_per_page) +1;
			$end = ($start-1) + $records_per_page;
			
			if($end >$case->numrows)
				$end = $case->numrows;
		?>
		<p class="hdr-grey right">Displaying <?PHP echo $start?>-<?PHP echo $end?> of <?PHP echo $case->numrows ?> cases</p>
		<?PHP 
			foreach($rs_case as $row){
		?>
		
		<!-- Display the cases (10 per page) -->
		<table class="tbl-uni margintop" style="clear: both">
			<caption>
				<?PHP echo $row['title']?>&nbsp;<?PHP echo $row['citation']?>
				<p class="right readmore">
					<a href="admin_case_summary.php?id=<?PHP echo $row['case_id']?>">&raquo; view case summary</a><br />
				</p>
			</caption>
			<tr>
				<th class="bg-uni25">Date</th>
				<th class="bg-uni25">Court</th>
				<th class="bg-uni25">Other legislation </th>
			</tr>
			<tr>
				<td><?PHP echo fShowMediumSQLDate($row['date'])?></td>
				<td><?PHP echo $row['court']?></td>
				<td><?PHP echo $row['other_legislation']?></td>
			</tr>
			<?PHP if (!empty($row['overview'])) { ?>			
					<tr>
						<th colspan="5">Overview</th>
					</tr>
					<tr>
						<td colspan="5"><p><?PHP echo $row['overview']?></p></td>
					</tr>
			<?PHP
				}
				 if (!empty($row['related_links'])) { ?>
					<tr>
						<th class="bg-uni25" colspan="5">Related links</th>
					</tr>
					<tr>
						<td colspan="5"><p><?PHP 
							$arr_links  = split("\n", $row['related_links']);
							for($i=0; $i < count($arr_links); $i++){
								if (strpos($arr_links[$i], '|') > -1 ){
									list($link_text, $link) = split('\|',$arr_links[$i]);
								}
								else{
									$link_text = $link = $arr_links[$i];
								}
						
								echo "<a href='" . $link . "' target='_blank'>" . $link_text . "</a><br>";
							}
						?></p></td>
					</tr>
			<?PHP } ?>	
		</table>
		<p class="text-right nopadtop">
			<input type="button" name="edit" value="Edit" onClick="window.location='case_edit.php?id=<?PHP echo $row['case_id']?>'" />
			<input type="button" name="delete" value="Delete (you will be asked to confirm)" onClick='fConfirmDelete("<?PHP echo $row['title']?>", <?PHP echo $row['case_id']?>)'/>
		</p>	

		<?PHP } ?>
		<div class="hdr-grey"><a href="#" onClick="fDoPageChange(1,document.frmSearch)">&lt;&lt; first</a>&nbsp; <a href="#" onClick="fDoPageChange(2,document.frmSearch)">&lt; previous</a> &nbsp;&nbsp;&nbsp; <?PHP echo $start?>-<?PHP echo $end?> of <?PHP echo $case->numrows ?> &nbsp;&nbsp;&nbsp; <a href="#" onClick="fDoPageChange(3,document.frmSearch)">next &gt;</a> &nbsp;<a href="#" onClick="fDoPageChange(4,document.frmSearch)">last &gt;&gt;</a></div>
		<?PHP } 
		else
			echo $err_string;
		?>		

	</div>

<!-- END MAIN PAGE CONTENT -->

If the user enters a keyword with an apostrophe, the whole page goes blank. I've checked that Magic Quotes is turned on (

echo get_magic_quotes_gpc();

returns 1) so there shouldn't be any need for addslashes() ... at any rate it makes no difference if I add this - the page still breaks.

Any help would be immensely appreciated!

Thanks
cobberas

I think your best bet would be to debug the fPublicSearchCase function. If that executes the actual query, you may want to start with checking what is actually in the query.

commented: Very useful tip - thanks heaps +2

Thanks pritaeas

Here's the function code:

function fPublicSearchCase($year, $paging, $rows_per_page=0, $pageno=1){

		if (!empty($this->hra_section_id)){
			$and_hra .= " and xhc.hra_section_id = $this->hra_section_id ";
		}


		$sql = "select distinct c.*, ct.court from cases c inner join court ct on c.court_id = ct.court_id " .
				" inner join xref_hra_section_cases xhc on xhc.case_id = c.case_id " . $and_hra . 
				" inner join hra_section hra on xhc.hra_section_id = hra.hra_section_id  ";

		// filters for selecting records containing a particular keyword
		if (!empty($this->citation)){
			$sql .= "where ( lower(c.citation) like '%" . strtolower($this->citation) .  "%' ";
		}

		if (!empty($this->title)){
			$sql .= " or lower(c.title) like '%" . strtolower($this->title) .  "%' ";
		}

		if (!empty($this->overview)){
			$sql .= " or lower(c.overview) like '%" . strtolower($this->overview) .  "%' ";
		}

		if (!empty($this->summary)){
			$sql .= " or lower(c.summary) like '%" . strtolower($this->summary) .  "%' ";
		}

		if (!empty($this->other_legislation)){
			$sql .= " or lower(c.other_legislation) like '%" . strtolower($this->other_legislation) .  "%' ";
		}

		if (!empty($this->related_links)){
			$sql .= " or lower(c.related_links) like '%" . strtolower($this->related_links) .  "%' )";
		}
				
		$sql_filter= ' 2=2 ';
		
		// filter for selecting records of particular year
		if (!empty($year)){
			$sql_filter .= " and year(date) = $year ";
		}

		// filter for selecting records of particular court_id
		if (!empty($this->court_id)){
			$sql_filter .= " and c.court_id = $this->court_id";
		}
		
		// filter for selecting records of particular last_modified
		if (!empty($this->last_modified)) {
			$sql_filter .= " and c.last_modified > '$this->last_modified'";
		}

		$sql .= " and ($sql_filter) ";
		
		$sql .= " order by c.date desc";
		
		
		$this->db->pageno = $pageno;
   		$this->db->rows_per_page = $rows_per_page ;
		$result = $this->db->getData($sql);				
		
		$this->lastpage = $this->db->lastpage;
		$this->numrows = $this->db->numrows;
		
		return $result;
	}

I'm thinking that I need to escape all apostrophes in the user-inputted keyword at some stage - I would think ideally before the query is executed (ie before fPublicSearchCase() gets called; maybe even before the anu_case() object properties are set at line 27.

Your thoughts?

thanks again
cobberas

One solution I've found - thanks to your tip - is to convert keyboard apostrophes in the keyword to a single quote, before setting the object params:

if ($_REQUEST['form_action']=='search'){
			
		$records_per_page = 10;		
		$page_num = (!empty($_REQUEST['page_num'])) ? $_REQUEST['page_num']: 1;
		
		//collect search parameters from user
		$year = $_REQUEST['year'];
		$keyword = $_REQUEST['keyword'];

		//convert keyboard apostrophes to single quote in keyword
		$keyword = fChangeApostr($keyword);

		//set the object properties for anu_case()
		$case->citation = $keyword;
		$case->title = $keyword;
		$case->overview = $keyword;
		$case->summary = $keyword;
		$case->other_legislation = $keyword;
		$case->related_links = $keyword;

		$case->court_id= $_REQUEST['court'];
		$case->hra_section_id = $_REQUEST['hra_section'];
		
		//return selected cases
		$rs_case = $case->fPublicSearchCase($year, true, $records_per_page, $page_num);
			
		if(! $rs_case)
			$err_string = '<p>No cases match your search criteria.</p>';
	}
function fChangeApostr($string) {
		for($i=0;$i<strlen($string);$i++) {
			if ($string[$i] == chr(39)) {
				$string[$i] = chr(146);
			}
			$newString .= $string[$i];
		}
		return $newString;
	}

Not sure if this solution will work elsewhere in the application but it works for this page.

What do you think?

cheers
cobberas

Hard to say for sure.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.