How do I retreive information from MySql ignoring special characters?

I have the following simple search query code:

function explode_search($squery, $column, $db, $link) {
global $conn;

$ven = explode(' ', safeInput(str_replace(',', '', $squery)));
$ven2 = array_map('trim', $ven);
$qy = ''.$column.' LIKE "%'.implode('%" AND '.$column.' LIKE "%', $ven2).'%"';

$query = 'SELECT DISTINCT '.$column.', id, work_composer FROM '.$db.' WHERE '.$qy.' ORDER BY '.$column.' LIMIT 100';
$result = mysqli_query($conn, $query);
while ($row = mysqli_fetch_assoc($result))  {

    echo '<div><span class="mdmtxt" style="margin-bottom:5px;"><a href="'.$link.'.php?id='.$row['id'].'">'.$row[$column].'</a></span> <span class="mdmtxt" style="opacity:0.6;">('.fixcomp(cfid($row['work_composer'], 'cffor_composer', 'composer_name')).')</span></div>';
}   
}

(The safeInput function removes ' and " and other possible problematics)

It works alright up to a point.

When someone looks for 'Stephane' I want them also to find 'Stéphane' (and vice versa) or if they are looking for 'Munich', 'Münich' should show up in the list as well.

Is there a way to make MySQL match those search queries, irrespective of the special characters involved?

Solution

You want to use what's called a "Parameterized Query". Most languages have them, and they are used to safeguard input and protect from attacks.

They're also extremely easy to use after you get used to them.

You start out with a simple query like this

$stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")

and you replace all your actual data with ?s.

Then you bind each parameter.

$stmt->bind_param("s", $city);

Then call $stmt->execute();

More information can be found here: http://php.net/manual/en/mysqli.prepare.php

One time in college, our prof made us use a library that didn't have parameterized queries, so I wrote an implementation myself. Doctrine, which is pretty awesome, handles all this for you. I would always rely on someone else to do this stuff for me instead of writing my own implementation. You'll get into trouble that way. There's also a lot written about not reinventing new types which is basically what you're doing. Types have problems. Types need testing. This kind of thing is well tested in other implementations and not yours.