What are the best practices for search?

[Fluoresce]

I want to add a search feature to my site so that users can search for videos.

Let's say that a user conducts a search and I GET their search query:

$squery = isset($_GET['query']) ? $_GET['query'] : '';

Now what should I do?

 

What are the best practices to ensure the security of my database and to provide the most relevant results to the user?

 

Here's what I've got so far.

// Make sure a search query was entered.
if($squery == '') {
    echo "<p>You didn't enter a search query.</p>";
}
    
// Strip HTML tags.
$squery = strip_tags($squery);

// Trim white space.
$squery = trim($squery);
    
// Set minimum query length.
$min_length = 3;
   
// Make sure query length is more than minimum.    
if(strlen($squery) < $min_length) {
    echo "<p>The search query you entered is too short. The minimum number of characters is ".$min_length.".</p>";
}

// Connect to MySQL.
// Select database.

// Escape search query.
$squery = mysql_real_escape_string($squery);

// Break query into keywords.            
$keywords = explode(' ', $squery);

// Count number of keywords.
$no_of_keywords = count($keywords);

// If just one keyword, then build statement.
if($no_of_keywords == 1) {
    $sql = "SELECT whatever
    FROM `video_table`
    WHERE (col1 LIKE '%.$squery.%' OR col2 LIKE '%.$squery.%')";            
}

// If multiple keywords, then build statement.
else {
    $sql = "SELECT whatever
    FROM `video_table`
    WHERE ";
                        
    for($i = 0; $i < $no_of_keywords; $i++) {
        $sql .= "(col1 LIKE '%.$keywords[$i].%' OR col2 LIKE '%.$keywords[$i].%')";
        if($i < $no_of_keywords) {
            $sql .= " OR ";
        }
    }
}

// Run mysql query.
$raw_results = mysql_query($sql, $con);

// Put results into an array for later use.        
$results = mysql_fetch_array($raw_results);

Can this code's security be improved?

 

How can it be altered to provide more relevant results?

 

Should I omit words such as "to" and "the" from the query? If so, how do I do it?

 

Should I remove punctuation?

 

As always, I appreciate your help. You guys have taught me LOADS!

Edited November 6, 2014 by Fluoresce

[Fluoresce]

By the way, I'm aware that I shouldn't be using LIKE statements and that I should be using FULLTEXT indexes with MATCH() ... AGAINST() statements, but I can't.

[Psycho]

First off, you should stop using the mysql_ extension - it is deprecated. Use mysqli_ or, better yet, PDO. Then, when you do convert, use Prepared Statements for anything that will include variable data that could be a security risk (such as $_GET values). This is the best way to ensure user data will not create SQL Injection problems.

 

Now, as to the best ways of searching, that is not an easy answer as it all depends on the context of what the user is searching for. Sometimes, you have to make an educated guess on the best business rules and try it out for a while and get some feedback. You can also add some logging of searches done to see any patters. Are users performing multiple searches before selecting a record? What did they search for and what did they ultimately select? How could you change the search logic so that what they ultimately selected would have been in the results of the first search?

 

EDIT: If you are not going to use Full Text searching then you should at least explode the search words and create a multi-conditional where clause. What you have now requires the user to enter the search words in the exact order that they would appear in the fields being searched. E.g.:

 

SELECT whatever
FROM `video_table`
WHERE
    (col1 LIKE '%.$word1.%' AND
     col1 LIKE '%.$word2.%' AND
     col1 LIKE '%.$word3.%')
    OR
    (col2 LIKE '%.$word1.%' AND
     col2 LIKE '%.$word2.%' AND
     col2 LIKE '%.$word3.%')

 

But, yeah, you really need to use Full Text

Edited November 6, 2014 by Psycho

[Fluoresce]

First off, you should stop using the mysql_ extension - it is deprecated. Use mysqli_ or, better yet, PDO.

 

Yes, I'm a bit behind.

 

 

Then, when you do convert, use Prepared Statements for anything that will include variable data that could be a security risk (such as $_GET values). This is the best way to ensure user data will not create SQL Injection problems.

 

Thanks! I shall look into this. I know nothing of prepared statements.

 

 

Now, as to the best ways of searching, that is not an easy answer as it all depends on the context of what the user is searching for. Sometimes, you have to make an educated guess on the best business rules and try it out for a while and get some feedback. You can also add some logging of searches done to see any patters. Are users performing multiple searches before selecting a record? What did they search for and what did they ultimately select? How could you change the search logic so that what they ultimately selected would have been in the results of the first search?

 

Thanks, but I don't need anything that complex. I just need something rudimentary that provides relatively relevant results.

 

 

EDIT: If you are not going to use Full Text searching then you should at least explode the search words and create a multi-conditional where clause.

 

That's what I thought my code above does!

 

It's supposed to generate a search clause like this:

SELECT whatever
FROM `video_table`
WHERE
    (col1 LIKE '%.$word1.%' OR col2 LIKE '%.$word1.%') OR
    (col1 LIKE '%.$word2.%' OR col2 LIKE '%.$word2.%') OR
    (col1 LIKE '%.$word3.%' OR col2 LIKE '%.$word3.%')

[Fluoresce]

Anyone know any tutorials or other resources that might help me with the questions that I presented above? I'm still struggling with this problem.