Databases
StackOverflow2013
Postsdbo
PORetrieving matched context of MySQL fulltext search in PHP (and security)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORetrieving matched context of MySQL fulltext search in PHP (and security)
    text
    copied!<p>I'm doing a fulltext search on my MySQL table "pages". I'm displaying a list of pages that match the keyword in their "title" (plain text, VARCHAR, 255) or "content" (html, TEXT). When the match is found in the "content" field, I'd like to display the snippet in which the match was found. I have no idea how to go about this. </p> <p>Can you put me in the right direction?</p> <pre><code>$query = ' SELECT *, MATCH(title, content) AGAINST("'.$keyword.'") AS score FROM page WHERE MATCH(title, content) AGAINST("'.$keyword.'") ORDER BY score DESC '; $result = mysql_query($query) or die (mysql_error()); if(mysql_num_rows($result) &gt; 0) { $output .= '&lt;p&gt;Your keyword matches the following pages:&lt;/p&gt;'; while($row = mysql_fetch_assoc($result)){ $title = htmlentities($row['title']); $content = htmlentities(strip_tags($row['content'])); $content = limit_text($content, 250); // Cuts it down to 250 characters plus ... $output .= '&lt;h2&gt;'.$title.'&lt;/h2&gt;'; if(trim($content) != '') { $output .= '&lt;p&gt;'.$content.'&lt;/p&gt;'; // I'd like to place a snippet here with the matched context } } } else { $output .= '&lt;p&gt;Keyword not found...&lt;/p&gt;'; } </code></pre> <p>Also, I have a question regarding security. Right now I'm checking <code>$keyword</code> in three ways:</p> <ul> <li>Not blank?</li> <li>More than 2 characters?</li> <li>Not dangerous? (see below)</li> </ul> <p>I use a regular expression to match the following, to see if the user input is dangerous</p> <pre><code>&lt;script|&amp;lt;script|&amp;gt;script|document.|alert|bcc:|cc:|x-mailer:|to:|recipient|truncate|drop table </code></pre> <p>This might be a little bit ridiculous and easy to work around, but it is at least a minimal form of protection against XSS exploits. What is the recommended way to secure filter a keyword intended for search? Is <a href="http://phpids.org/" rel="nofollow noreferrer">PHPIDS</a> overkill?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload