2

I want to search a word from the database with single quote like a word can't and if I search a word like cant without single quote, can't still be displayed. 

    <?php
    // Attempt search query execution
    try{

        if(isset($_REQUEST['term'])){
            // create prepared statement
            $sql = "SELECT * FROM countries WHERE name LIKE :term";
            $stmt = $pdo->prepare($sql);
            $term = '%' . $_REQUEST['term'] . '%';
            // bind parameters to statement
            $stmt->bindParam(':term', $term);
            // execute the prepared statement
            $stmt->execute();
            //fetch the data if there is a name and else no matches found
            if($stmt->rowCount() > 0){
                while($row = $stmt->fetch()){
                    echo "<p>" . $row['name'] . "</p>";
                }
            } else{
                echo "<p>No matches found</p>";
            }
        }  
    } catch(PDOException $e){
        die("ERROR: Could not able to execute $sql. " . $e->getMessage());
    }
    // Close statement
    unset($stmt);
    // Close connection
    unset($pdo);
    ?>
janejoe
  • 33
  • 8

1 Answers1

1

You can replace all unwanted characters like this:

REPLACE(name, '#','')

In your case that would be:

SELECT * FROM countries WHERE REPLACE(name, '\'','') LIKE :term

If you want more special chars to be deleted you need to do it like this:

SELECT * FROM countries WHERE REPLACE(REPLACE(name, '\'',''), '*', '') LIKE :term
Marvin Fischer
  • 2,552
  • 3
  • 23
  • 34
  • Can you please echo the quoted string and see if thats the term you search? (`echo $pdo->quote($_REQUEST['term'])`) – Marvin Fischer Feb 28 '18 at 08:35
  • I applied your code and even a single word displays No matches found. – janejoe Feb 28 '18 at 08:36
  • So please read my comment again. Please echo the quoted string that we send to the database – Marvin Fischer Feb 28 '18 at 08:38
  • I got this result %'can\'t'%. – janejoe Feb 28 '18 at 08:40
  • Try with `$term = '%' . addslashes($_REQUEST['term']) . '%';` pdo prepared statements should take care of the escaping anyways – Marvin Fischer Feb 28 '18 at 08:45
  • 1
    I think you misunderstood the question. As far as I understand it, she wants to search for `cant` (without single quote) and get all rows that contain `can't` (with a single quote) as well. – simon Feb 28 '18 at 08:48
  • @Marvin Fischer I already tried this one and got the same result. I'm trying to search the term can't without the ' like cant. Is there a solution on this one or I just leave it here? – janejoe Feb 28 '18 at 08:50
  • Is that only for ' or all special characters? If its only ' you can use this: https://stackoverflow.com/questions/1289178/search-column-in-sql-database-ignoring-special-characters – Marvin Fischer Feb 28 '18 at 09:12
  • Also is the ' in the database or the user string? – Marvin Fischer Feb 28 '18 at 09:14
  • @MarvinFischer in the database. – janejoe Feb 28 '18 at 09:17
  • Then try the link from 3 comments above – Marvin Fischer Feb 28 '18 at 09:18
  • @MarvinFischer It works now, by the way can you explain the code? What if I want to ignore all special characters? Thanks. – janejoe Feb 28 '18 at 09:38
  • It just replaces every ' with an empty string so `a'b''c` would just be replaced to `abc`, you need to make a new replace for every special char, ill edit it how to do it – Marvin Fischer Feb 28 '18 at 13:02
  • @MarvinFischer I got a problem, now that I want to search with ' it shows no result but when I searching for without ' it shows. When I searching for the word cant it shows the word can't but when I search for the word can't it shows no result. What should I do? – janejoe Mar 01 '18 at 09:01
  • You should also replace the ' characters in the search term – Marvin Fischer Mar 01 '18 at 09:05