Apostrophe Error

[EternalSorrow]

I'm a little rusty on my PHP skills, so this (probably simple) problem has me stumped.  I have a search engine which retrieves from a database with titles, of which some of those titles have apostrophes within them.  There can also be several authors for a single title, so I've set up this code to retrieve and give each author a different url to their page:

 

$select_author = mysql_query("SELECT DISTINCT author FROM archives WHERE `title` = '$title' AND `year` = '$year' AND `category` = '$category' AND `group` = '$group' ") or die(mysql_error());

 

However, whenever an apostrophe appears for the $title I get this error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'll Have Lemonade, Please' AND `year` = '2002' AND `category` = 'Lemon' AND `grou' at line 1

 

The full title is "I'll Have Lemonade, Please," and you can see where the problem lies.  I've tried replacing the "`title` = '$title'" with "`title` = '%s'", but then none of the authors will appear.

 

Anyone have any suggestions on how to fix this problem?  Thanks in advance, and here's the full code:

 

<?php

if(isset($_POST[search])) {

$title = strtolower(strip_tags(mysql_escape_string($_POST['title'])));
$author = strtolower(strip_tags(mysql_escape_string($_POST['author'])));
$summary = strtolower(strip_tags(mysql_escape_string($_POST['summary'])));
$category = strip_tags(mysql_escape_string($_POST['category']));
$group = strip_tags(mysql_escape_string($_POST['group']));
$rating = strip_tags(mysql_escape_string($_POST['rating']));
$year = strip_tags(mysql_escape_string($_POST['year']));

$termsArray = array();

if(!empty($author))
{
$termsArray[] = "author LIKE '%$author%'";
}

if(!empty($title))
{
$termsArray[] = "title LIKE '%$title%'";
}

if(!empty($summary))
{
$termsArray[] = "summary LIKE '%$summary%'";
}

if (count($termsArray) > 0){
   $terms = implode(" AND ", $termsArray);
   $terms = " WHERE ".$terms;
   unset($termsArray); //clear memory, cause we're finished using this.
}

$join = (empty($title) && empty($author) && empty($summary)) ? "WHERE" : "AND";

$sql_category = ($category == all) ? "" : "$join `category`='$category'";

if ($sql_category != ""){
   $join = "AND";
}

$sql_group = ($group == all) ? "" : "$join `group`='$group'";

if ($sql_group != ""){
   $join = "AND";
}

$sql_rating = ($rating == all) ? "" : "$join `rating`='$rating'";

if ($sql_rating != ""){
   $join = "AND";
}

$sql_year = ($year == all) ? "" : "$join year='$year'";

$qSearch = "SELECT * FROM archives
$terms $sql_category $sql_group $sql_rating $sql_year
GROUP BY url ORDER BY title ASC, author ASC";

$rsSearch = mysql_query($qSearch) or die(mysql_error());

$end = '';
if (mysql_num_rows($rsSearch) >= 2)
{
$end = 's';
}

if (mysql_num_rows($rsSearch) == 0)
{
print '<p>Sorry, there were no results returned for your search. Please try again.</p>';
}
else
{
print '<center><p><b>'.mysql_num_rows($rsSearch).'</b> title'.$end.' found.</p></center>';

echo '<ol>';

while ($row = mysql_fetch_array($rsSearch))
{
extract($row);

$select_author = mysql_query("SELECT DISTINCT author FROM archives WHERE `title` = '$title' AND `year` = '$year' AND `category` = '$category' AND `group` = '$group' ") or die(mysql_error());

$aut = "";
while ($row3 = mysql_fetch_array($select_author)) {
   $aut .= "<a href=\"author.php?author={$row3[author]}\">$row3[author]</a> & ";
}

$aut = substr($aut,0,-3);

$my_code = urlencode($title);
$my_author = urlencode($author);

echo '<li><a href="info.php?author='.$my_author.'&title='.$my_code.'">'.$title.'</a> by '.$aut.'</li>';

}
echo '</ol>'; 

}
}
?>

[blacknight]

verry simple fix

any var that has or could have ' in it use this addslashes($varnamehere)

ex:

SELECT DISTINCT author FROM archives WHERE `title` = '$title' AND `year` = '$year' AND `category` = '$category' AND `group` = '$group' "
to
SELECT DISTINCT author FROM archives WHERE `title` = 'addslashes($title)' AND `year` = '$year' AND `category` = '$category' AND `group` = '$group' "

 

best bet when adding stuff in to a dabatase allways use addslases() then when displaying use stripslashes() makes life easier i fond

[Pikachu2000]

Do not use addslashes() unless there is no DBMS-specific escaping function available. PHP has those functions for MySQL, which you're using already. The fact that you're using mysql_real_escape_string() and still having problems makes me wonder, have you connected to the database before trying to use mysql_real_escape_string()?

[EternalSorrow]

Addslashes unfortunately does give me the same error message as before (along with making the author names disappear).

 

I haven't received any errors regarding connecting to the database, and the rows from the db appear for a search until it comes to a title with an apostrophe in it.  That's when the error message appears.

[PFMaBiSmAd]

The query that's failing is the one after the extract($row) statement and the $title at that point is not escaped, it is the raw value from the extract() statement.

 

If you have already queried for the matching information, that includes the author, why on earth are you executing another query to get that same information?

[Pikachu2000]

Hang on, which query is causing the error? The second one that uses the results from the previous query? If that's the case, as I suspect it is, you need to escape the values in the second query as well.

 

"SELECT DISTINCT author FROM archives WHERE `title` = '" mysql_real_escape_string($title) . "' AND `year` . . . etc.

[EternalSorrow]

Wow, lots of help  , lemme sort out the questions/tips/suggestions:

 

I definitely know the $select_author query after the extract($row) is the problem child.  I am using this format because there are multiple authors for a single story, and in the database I have given them their own row to avoid using multiple tables.  So it's like this:

 

Title                                     Author

I'll Take Lemonade              FirstAuthor

I'll Take Lemonade              SecondAuthor

 

The $select_author grabs the two rows and places the authors as a list within the echo, so they'd come out like this:

 

I'll Take Lemonade by FirstAuthor & SecondAuthor

 

I tried using '" mysql_real_escape_string($title) . "' instead of the $title, but this error message came up:

 

Parse error: syntax error, unexpected T_STRING in /home/animara/public_html/inufiction/search.php on line 267

 

with line 267 being the line which holds the modified $select_author query, like so:

 

$select_author = mysql_query("SELECT DISTINCT author FROM archives WHERE `title` = '" mysql_real_escape_string($title) . "' AND `year` = '$year' AND `category` = '$category' AND `group` = '$group' ") or die(mysql_error());

[Pikachu2000]

I apparently missed a concatenation operator (dot) between '" and mysql_real_es . . .

[EternalSorrow]

That worked!  Here's the final code for reference, and thanks for all the help, guys!

 

$select_author = mysql_query("SELECT DISTINCT author FROM archives WHERE `title` = '" . mysql_real_escape_string($title) . "' AND `year` = '$year' AND `category` = '$category' AND `group` = '$group' ") or die(mysql_error());