Why Mysqli prepared statement does'nt escape wildcards (% and _) and Backslash ( \ )?

Question

As a security measure I am using Mysqli prepared statement and bindvariables.

My code is shown below:

$what = trim($_GET['what']);
$key_word = "%".$what."%";  
$where_clause = " WHERE chair.company LIKE ? OR chair.business_category LIKE ? OR chair.summary LIKE ?";
$query = "SELECT chair.id_user, chair.company   FROM chair" . $where_clause; 
$con = @mysqli_connect($hostname,$username,$password, $database_name);
$stmt = mysqli_prepare($con, $query);
$bind = mysqli_stmt_bind_param($stmt, 'sss', $key_word, $key_word, $key_word);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
$bind = mysqli_stmt_bind_result($stmt,$business_id,$company);
while (mysqli_stmt_fetch($stmt))
    {
    echo htmlspecialchars($company)."<br>";
    }

From Documentation:

As an alternative to explicitly escaping special characters, many MySQL APIs provide a placeholder capability that enables you to insert special markers into a statement string, and then bind data values to them when you issue the statement. In this case, the API takes care of escaping special characters in the values for you.

I can use all character in the keyboard (excluding \ % _ ) and escaped and matched properly. My problem is that this prepared statement does not escaping wild cards(% and _) and escape character (\). When I am searching for % It lists all item in the database. And When Iam searching for _ It lists all items . And when searching for (\), It doesn't match the desired item.

I had find a similar Question, but the amswer never meet my requirement because I have to use the wildcard with my query.

Another Question answer says that to escape them use double back slashes

Even this is not a security issue, why prepared statement doesn't escape them? Is there any mistake in the way Iam using it in my code? How to dynamically escape them ? Which function can I use for it?

Answer 1

Prepared statements do escape the \ properly. Otherwise they wouldn’t create properly formatted string literals, which is the main purpose of prepared statements.

Besides that, % and _ are only special characters to the LIKE comparison but not to strings in general. So there is no need to escape them in general. If you need to escape them for a LIKE comparison, do it explicitly:

$what = addcslashes($what, '%_');

Besides that, LIKE does also support the use of an escape character other than \:

mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
        -> 1