6 Replies - 737 Views - Last Post: 12 January 2013 - 02:56 PM Rate Topic: -----

#1 Kalor  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 06-January 13

Apostrophe in db searches

Posted 12 January 2013 - 02:15 PM

I am using a passed variable to search my db for a specific title. The title stored in the db has an apostrophe in the title (i.e. "Am I My Brother's Keeper") A var_dump shows the result 'string(26) "Am I My Brother\'s Keeper?"'

Obviously this causes the query to return no results as it seems to read the apostrophe in the variable as a escape (\?)

Is there a way to format the variable to recognize the apostrophe or is it just easier to rename all the title fields in the database (and the mp3 the stored path points to) to remove apostrophe's?

Is This A Good Question/Topic? 0
  • +

Replies To: Apostrophe in db searches

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Apostrophe in db searches

Posted 12 January 2013 - 02:31 PM

Assuming you're using MySQL, you'll need to use mysql_real_escape_string or mysqli_real_escape_string depending on which set of function you use to connect to the DB.

Alternately, you could use PDOs, since mysql_* statements are deprecated as of PHP 5.4, and mysqli_* statements are designed for a single DB engine, which is kind of limiting when it comes to development. Dormilich has written two excellent tutorials on PDOs, located here and here. The benefits of PDOs include that:
  • They are current in terms of PHP coding standards
  • They can hook into different DB engines
  • There is no need to escape strings
  • You are protected from SQL injection
  • Recurring queries are executed slightly quicker due to statement preparation

Was This Post Helpful? 0
  • +
  • -

#3 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3541
  • View blog
  • Posts: 10,238
  • Joined: 08-June 10

Re: Apostrophe in db searches

Posted 12 January 2013 - 02:35 PM

View PostKalor, on 12 January 2013 - 10:15 PM, said:

Is there a way to format the variable to recognize the apostrophe or is it just easier to rename all the title fields in the database (and the mp3 the stored path points to) to remove apostrophe's?

the point is, your data are not what they’re supposed to be. design-wise the only choice is to fix the data.
Was This Post Helpful? 2
  • +
  • -

#4 Kalor  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 06-January 13

Re: Apostrophe in db searches

Posted 12 January 2013 - 02:39 PM

I am using PDO's.

$selSpeaker = trim($_POST['SpeakerMenu']);
$selDate = trim($_POST['DateMenu']);
$selTitle = trim($_POST['TitleMenu']);
$isSpeaker = isChecked("OptCheckBox", "Speaker");
$isDate = isChecked("OptCheckBox", "Date");
$isTitle = isChecked("OptCheckBox", "Title");

// function to determine what checkboxes are checked
function IsChecked($chkname,$value)
    {
        if(!empty($_POST[$chkname]))
        {
            foreach($_POST[$chkname] as $chkval)
            {
                if($chkval == $value)
                {
                    return true;
                }
            }
        }
        return false;
    }
// connect to database server
$mySQL = "mysql:host=www.springroadcoc.com;dbname=soundrm_Website";
$opt = array(
	//    any occurring errors wil be thrown as PDOException
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	//   an SQL command to execute when connecting
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"
);

try
{
	$pdo = new PDO($mySQL, "myuser", "mypass", $opt);

	try
	{
		$SQL = "Select * from Sermons Where";
		If ($isSpeaker == True)
		{
			$SQL .= " Speaker_ID = :ID";
		}
		If ($isDate == True)
		{
			if ($isSpeaker == True)
			{
				$SQL .= " and Date = :DATE";
			}
			else
			{
				$SQL .= " Date = :DATE";
			}
		}
		If ($isTitle == True)
		{
			if (($isSpeaker == True) or ($isDate == True))
			{
				$SQL .= " and Title = :TITLE";
			}
			else
			{
				$SQL .= " Title = :TITLE";
			}
		}
		//	create the sql
		//	create a prepared statement
		$Result = $pdo->prepare($SQL);
		if ($isSpeaker == True)
		{
			$Result->bindValue(':ID', $selSpeaker);
		}
		if ($isDate == True)
		{
			$Result->bindValue(':DATE', $selDate);
		}
		if ($isTitle == True)
		{
			$Result->bindValue(':TITLE', $selTitle);
		}
		$Result->setFetchMode(PDO::FETCH_NAMED);
		$Result->execute();

		// $text = "Default";
		foreach ($Result as $row)
		{
			$text .= $row["Title"] . "<br>";
		//	$text = "TEST";
		}
		echo $text;
	}
	catch (Exception $e)
	{
		echo '<P Class="error">Oops, we have encountered a problem, but we will deal with it. Promised.</P>'.$e->getMessage();
		// send_error_mail($e->getMessage());
	}
}
catch (Exception $e)
{
	echo '<P Class="error">Oops, we have encountered a problem, but we will deal with it. Promised.</P>'.$e->getMessage();
	// send_error_mail($e->getMessage());
}


Note the query executes successfully (returns results) when using $selSpeaker.
Was This Post Helpful? 0
  • +
  • -

#5 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Apostrophe in db searches

Posted 12 January 2013 - 02:50 PM

If the data itself has an escape character embedded in it (i.e. SELECT field FROM table from the DB command line shows the escape character) then Dormilich is correct, the data will need to be cleaned. There are alternatives, for example catering for the dirty data in PHP, but you would be far better off cleaning your data so that you don't have to compensate for it's problems with your code.
Was This Post Helpful? 1
  • +
  • -

#6 Kalor  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 06-January 13

Re: Apostrophe in db searches

Posted 12 January 2013 - 02:52 PM

okay. Thankfully I only have 140 records or so to clean. :)

Thanks for the advice.
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3488
  • View blog
  • Posts: 11,902
  • Joined: 12-December 12

Re: Apostrophe in db searches

Posted 12 January 2013 - 02:56 PM

I found the apostrophes were inserted by magic-quotes being ON by default for older versions of PHP. I use the following code in a file that I include in most/all pages to remove them.

if (get_magic_quotes_gpc()) {
    $process = array(&$_GET, &$_POST, &$_COOKIE, &$_REQUEST);
    while (list($key, $val) = each($process)) {
        foreach ($val as $k => $v) {
            unset($process[$key][$k]);
            if (is_array($v)) {
                $process[$key][stripslashes($k)] = $v;
                $process[] = &$process[$key][stripslashes($k)];
            } else {
                $process[$key][stripslashes($k)] = stripslashes($v);
            }
        }
    }
    unset($process);
}

but you would still (I believe) need to clean existing data.

This post has been edited by andrewsw: 12 January 2013 - 02:58 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1