Prepare query fails to return results when LIKE condition contains more than one non-wildcard character

I'm attempting to search a table of users using prepared statements. When the search string contains more than one character or seemingly random characters no results are returned (ie o and nn fail but n succeeds). The data in the database should return at least one result on the test queries I've used yet it returns none. I'm not certain that it's related to the character length but every search query I've tried that had more than a single character would fail to return results when it should have at least one result.

I've attempted many forms of the query with no luck. I've adjusted collation on the table and the fields to add case insensitivity. I've searched for similar posts but could not find one. I've gone over MySQL prepare and like documentation as well. I initially started with using a PHP PDO connection with a much more complicated query but have narrowed it down to a simple SELECT and isolated it to the MySQL prepare.

*All of these queries were executed in MySQL Workbench

PREPARE stm FROM "SELECT firstName, lastName, displayName FROM `users` WHERE ? LIKE CONCAT('%',?,'%') ORDER BY ? asc";
SET @searchCol = 'firstName';
set @searchQuery = 'o';
set @orderBy = 'lastName';
EXECUTE stm USING @searchCol, @searchQuery, @orderBy;
deallocate prepare stm;

Expected: Trevor, Bloom, Trev Bloom
Recieved: nothing
*I randomly stumbled upon this case. I'm not sure why a single n will work yet a single o won't.

PREPARE stm FROM "SELECT firstName, lastName, displayName FROM `users` WHERE ? LIKE CONCAT('%',?,'%') ORDER BY ? asc";
SET @searchCol = 'firstName';
set @searchQuery = 'nn';
set @orderBy = 'lastName';
EXECUTE stm USING @searchCol, @searchQuery, @orderBy;
deallocate prepare stm;

Expected: Anna, Hogan, Anna Hogan
Recieved: nothing

PREPARE stm FROM "SELECT firstName, lastName, displayName FROM `users` WHERE ? LIKE CONCAT('%',?,'%') ORDER BY ? asc";
SET @searchCol = 'firstName';
set @searchQuery = 'n';
set @orderBy = 'lastName';
EXECUTE stm USING @searchCol, @searchQuery, @orderBy;
deallocate prepare stm;

Expected: Multiple results which match %n%
Recieved: expected results

I have also attempted removing CONCAT('%',?,'%'), replacing it with ?, and changing @searchQuery to be set to %<query>% but the results are the same as above.

No error messages or abnormal logs are generated from what I can tell.

Charset/Collation are set to utf8 and utf_general_ci for the users table as well as applicable columns.

Below are the results of SHOW VARIABLES LIKE "%version%"

'innodb_version', '5.7.26'
'protocol_version', '10'
'slave_type_conversions', ''
'tls_version', 'TLSv1,TLSv1.1'
'version', '5.7.26-0ubuntu0.16.04.1-log'
'version_comment', '(Ubuntu)'
'version_compile_machine', 'x86_64'
'version_compile_os', 'Linux'

Solution

You can't use ? placeholders for column names. Placeholders are used in place of values, and they're replaced with the literal value that you supply when executing the statement.

When you write

PREPARE stm FROM "SELECT firstName, lastName, displayName FROM `users` WHERE ? LIKE CONCAT('%',?,'%') ORDER BY ? asc";
SET @searchCol = 'firstName';
set @searchQuery = 'o';
set @orderBy = 'lastName';
EXECUTE stm USING @searchCol, @searchQuery, @orderBy;

the query that it executes is:

SELECT firstName, lastName, displayName 
FROM `users` 
WHERE 'firstName' LIKE CONCAT('%','o','%') 
ORDER BY 'lastName' asc

It's not testing the firstName column, it's testing it as a literal string, which is never true because there's no o in firstName. And it's not ordering by the column, either; it's ordering by a constant, which means any order.

You need to use ordinary concatenation to substitute column names, not placeholders.

SET @searchCol = 'firstName';
set @searchQuery = 'o';
set @orderBy = 'lastName';
PREPARE stm FROM CONCAT("
    SELECT firstName, lastName, displayName 
    FROM `users` 
    WHERE ", @searchCol, " LIKE CONCAT('%',?,'%') 
    ORDER BY ", @orderBy, " asc");
EXECUTE stm USING @searchQuery;