Understanding MySQL Search using LIKE Clause

Introduction

When implementing a search feature in a web application, one of the most common queries that come up is how to find records in a database table based on specific keywords. In this article, we will explore how to achieve this using MySQL’s LIKE clause and discuss some common pitfalls and best practices.

Background

MySQL’s LIKE clause allows us to search for patterns within a string column. The basic syntax of the LIKE clause is as follows:

SELECT * FROM table_name WHERE column_name LIKE value;

In our case, we want to search for records in the posts table where the title column contains any of the given keywords.

SQL Injection and Prepared Statements

When building dynamic queries using user-provided input, it’s essential to use prepared statements to protect against SQL injection attacks. Prepared statements allow us to separate the SQL code from the user-provided input, which helps prevent malicious input from being executed as part of the query.

In our example, we’re using PHP and PDO (PHP Data Objects) to connect to a MySQL database. We’re also using prepared statements to execute our queries.

$stmt = $pdo->prepare($query);
$stmt->execute($keywordArray);

The Problem with LIKE Clause

The LIKE clause in MySQL is a powerful tool, but it can sometimes lead to unexpected results. In our case, we’re trying to search for records that contain any of the given keywords in the title column. However, the problem arises when we try to use multiple keywords.

For example, if we have two keywords: “hit” and “fifa”. We might expect the query to return all records that contain either of these words. But what happens when we try to search for both words together? In our current implementation, the LIKE clause is not doing what we expect it to do.

The Problem with Quotes

In the code snippet provided in the Stack Overflow question, there’s an issue with quotes. The single quotes around the keywords are causing problems.

$keywordArray[0] = "'%".$keywordArray[0]."%'";
for($i=1;$i<$n;$i++){
  $keywordArray[$i] = "'%".$keywordArray[$i]."%'";
  $query.=" OR title LIKE ?";
}

By putting the quotes around the keywords, we’re actually treating them as part of the value inside the parameter. This can lead to unexpected results and SQL injection vulnerabilities.

The Solution

To fix this issue, we need to remove the quotes from the keywords. Instead, we should use parameterized queries that handle quoting and escaping for us.

$query = "SELECT * FROM posts WHERE title LIKE ?";
$keywordArray[0] = "%".$keywordArray[0]."%";
for($i=1;$i<$n;$i++){
  $keywordArray[$i] = "%".$keywordArray[$i]."%";
  $query .= " OR title LIKE ?";
}

By using parameterized queries, we ensure that the quotes are handled correctly and that our SQL code is protected against SQL injection attacks.

Example Use Cases

Here’s an example of how you can use this approach in your PHP application:

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
  $charset = "utf8mb4";
  $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
  $opt = [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];
  $pdo = new PDO($dsn, $user, $pass, $opt);
  $keywords = $_POST['keywords'];
  if (empty($keywords)) {
    echo "<h2 style='color:red;'>No results!</h2>";
    return;
  }
  $keywordArray = explode(' ', $keywords);
  $query = "SELECT * FROM posts WHERE title LIKE ?";
  for ($i = 0; $i < count($keywordArray); $i++) {
    $keywordArray[$i] = "%".$keywordArray[$i]."%";
    $query .= " OR title LIKE ?";
  }
  $stmt = $pdo->prepare($query);
  $stmt->execute($keywordArray);
  $res = $stmt->fetchAll();
  if ($res) {
    echo "<h1>SEARCH RESULTS:</h1>";
    foreach ($res as $row) {
      echo "<li>".$row['date']."<a href='viewpost.php?postid=".$row['id']."'>".$row['title']."</a></li><br>";
    }
  } else {
    echo "<h2 style='color:red;'>No results!</h2>";
  }
}

In this example, we’re using a prepared statement to execute our query. We’re also checking if the $keywords variable is empty before proceeding with the search.

Best Practices

When building dynamic queries, it’s essential to follow best practices to ensure security and performance.

• Always use prepared statements when building dynamic queries.
• Use parameterized queries to handle quoting and escaping for you.
• Validate user input to prevent SQL injection attacks.
• Optimize your database queries to improve performance.

By following these guidelines, you can build robust and secure web applications that provide a great user experience.

Last modified on 2023-07-16