Bizarre MySQL Wildcard Behavior

Question

I've run into a strange behaviour that I've been unable to figure out in my PHP code

using this code (omitting the entirety for the moment) -->

WHERE ss.showName LIKE '%".$searchFor."%'

$searchFor is defined by user input and sent via an AJAX request. The result is stored in $result as $tvLine. It is then displayed in html with $tvLine['showName'];

Now let's say I'm looking for the ss.showName "Joni and Friends"... If I type any amount of the letters used in "Joni and" I receive the correct result --> "Joni and Friends" As well, if I type any amount of the letters used in "riends" I receive the correct result --> "Jonie and Friends".

However, if I type simply "F" or "f" I receive no results.

How and why could this be?

Thank you

if (  isset($_GET['userInput']))  {

        $searchFor = $_GET['userInput']; // the actual user search content
        $searchDate = $_GET['sDate']; // ascertains the correct day for search
        $searchTz = $_GET['sTz'];   // ascertains the correct timezone for search
        // conditional statement to check time difference for MySQL
            if ($searchTz == "UK"){ $searchTz = 0;} 
                else { $searchTz = 5; }

        global $wpdb; 
        $now = date("Y-m-d H:i:s");
        $params = array($now);
        $sql = "SELECT se.*, ss.showName, TIMEDIFF(NOW(), UTC_TIMESTAMP) as server_date
            FROM showpress_episodes 
            AS se LEFT JOIN showpress_shows AS ss 
            ON se.showId=ss.id 
            WHERE ss.showName LIKE '%".$searchFor."%' 
            AND DATE(episodeStartTime) = CURRENT_DATE() + INTERVAL $searchDate DAY
            AND episodeStartTime >= NOW() - INTERVAL $searchTz HOUR
            ORDER BY episodeStartTime 
            ";
        $result = $wpdb->get_results($wpdb->prepare($sql, $params), ARRAY_A);

?>
<h2>Search Results &nbsp; &nbsp; <a id="search_close"  onclick="closeForm()">X</a></h2>
<?php
// create table to hold SQL     
        if (count($result) < 1){
            echo ('<p>
                    <table border="0">
                    <tr><td>Show Name</td></tr>
                    <tr ><td colspan="3">NO RESULTS </td> </tr>
                    <tr><td>'.$searchFor.'</tr></td>
                    </table>');         
        }
        else{
            $display_string .= '<p>
                    <table border="0" id="ajax-table">';
                    //$display_string .= "<tr><td>".$searchFor."</tr></td>"; 
            foreach ($result as $tvLine) {

                $display_string .= "<tr><td>"; 
                //$display_string .= $tvLine[server_date];
                $display_string .= "<a id='". $tvLine[id] ." 'class='pointer search now-playing'>".$tvLine['showName']."</a>";
                $display_string .= "</td></tr>";

            }
            $display_string .= "</table></p>";
        }
        echo $display_string;

        exit();

Your code is at risk from SQL Injection. You should [use prepared statements](https://stackoverflow.com/questions/18527659/php-mysqli-prepared-statement-like) to correct this ASAP. — Martin, Jan 16 '20 at 16:32
Output the final, fully escaped query and check. My suspicion is you're running it through something like sprintf somewhere and %F is seen as a control character. — ceejayoz, Jan 16 '20 at 16:32
Added code: Ok.. i think the control character issue must be what's happening as when I add a second % to the beginning.. then the search functions well. I guess I don't understand control characters if you can explain. thanks — gjnave, Jan 16 '20 at 16:43

score 2 · Accepted Answer · answered Jan 16 '20 at 16:57

You should read: wpdb::prepare( string $query, mixed $args )

The code should look like this (untested, I don't have a wordpress to test with):

    global $wpdb; 
    //$now = date("Y-m-d H:i:s");
    $params = array("%".$searchFor."%", $searchDate, $searchTz);
    $sql = "SELECT se.*, ss.showName, TIMEDIFF(NOW(), UTC_TIMESTAMP) as server_date
        FROM showpress_episodes 
        AS se LEFT JOIN showpress_shows AS ss 
        ON se.showId=ss.id 
        WHERE ss.showName LIKE %s 
        AND DATE(episodeStartTime) = CURRENT_DATE() + INTERVAL %d DAY
        AND episodeStartTime >= NOW() - INTERVAL %d HOUR
        ORDER BY episodeStartTime 
        ";
    $result = $wpdb->get_results($wpdb->prepare($sql, $params), ARRAY_A);

Bizarre MySQL Wildcard Behavior

1 Answers1