Error when search words contain special characters in php

Question

I'm getting a fatal PHP error when searching terms with special characters (example John O'Malley). Mysql database character set is utf8_unicode_ci

dbconn.php file

<?php
$DB_host = "removed";
$DB_user = "removed";
$DB_pass = "removed";
$DB_name = "removed";
    try
    {
        $DBcon = new PDO("mysql:host={$DB_host};dbname={$DB_name};charset=utf8",$DB_user,$DB_pass); 
        $DBcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    catch(PDOException $e)
    {
        echo "";
    }

php page

<?php
    $stmt = $DBcon->prepare("SELECT * FROM player WHERE player_name LIKE '%$Search%' LIMIT 25");
    $stmt->execute();
?>

Error message I get

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Malley%' LIMIT 25' at line 1' in /../results.php:167 Stack trace: #0 /../results.php(167): PDOStatement->execute() #1 {main} thrown in /../results.php on line 167

Answer 1

You are halfway there. Prepared statements will allow you to execute this query BUT you must use them correctly. To use them correctly you must parameterize the query. All values should be placeholders in the query, then they should be bound.

Try:

$stmt = $DBcon->prepare("SELECT * FROM player WHERE player_name LIKE concat('%', ?, '%') LIMIT 25");
$stmt->execute(array($Search));