Ignoring apostrophes in mysql searches

Question

I want to take a url that does not have any apostrophes, commas or ampersands in it and match it with a record in a database that may have one of those characters.

For example:

mywebsite.com/bobs-big-boy
mywebsite.com/tom--jerry
mywebsite.com/one-two-three

rewrite to

index.php?name=bobs-big-boy
index.php?name=tom--jerry
index.php?name=bobs-big-boy

Then in php I want to use the $_GET['name'] to match the records

bob's big boy
tom & jerry
one, two, three

Now my query looks like this:

"SELECT * from the_records WHERE name=$NAME";

I can't change the records, because they're business names. Is there a way I can write the query to ignore ampersands, commas and apostrophes in the db?

Answer 1

Yes you can but I'm pretty sure it will ignore any indexes you have on the column. And it's disgusting.

Something like

SELECT * FROM the_records 
WHERE replace(replace(replace(name, '''', ''), ',', ''), '&', '') = $NAME

By the way taking a get variable like that and injecting it into the mysql query can be ripe for sql injection as far as I know.

pg, I know you said you can't change/update the content in the database you're selecting from, but does anything preclude you from making a table in another database you do have write access to? You could just make a map of urlnames to business names and it'd only be slow the first time you do the replace method.

Answer 2

Greetings,

This one took me a few minutes to puzzle out! There are actually a few specifics missing on you requirements, so I've tried to work through the problem with different assumptions, as stated below.

Here is the set of assumed input from the URL, as pulled from your example, along with a MySQL injection attack (just for giggles), and variations on the business names. The keys are the expected URLs and the values are the database values to match.

<?php
$names = array(
  'bobs-big-boy'=>"bob's big boy",
  'tom--jerry'=>'tom & jerry',
  'tomjerry'=>'tom&jerry',
  'one-two-three'=>'one, two, three',
  'onetwothree'=>'one,two,three',
  "anything' OR 'haxor'='haxor"=>'die-haxor-die',
);
?>

One clever way to do an end-run mySQL's lack of regex replacement is to use SOUNDEX, and this approach would seem to mostly work in this case depending on the level of accuracy you need, the density of and similarity of customer names, etc. For example, this generates the soundex values for the values above:

$soundex_test = $names;
$select = 'SELECT ';
foreach ($soundex_test as $name=>$dbname) {
  echo '<p>'.$name.': '.soundex($name).' :: '.$dbname.': '.soundex($dbname).'</p>';
  $select .= sprintf("SOUNDEX('%s'),", $name);
}
echo '<pre>MySQL queries with attack -- '.print_r($select,1).'</pre>';

So, assuming that there are not customers named 'one, two, three' and separate one named 'onetwothree', this approach should work nicely.

To use this method, your queries would look something like this:

$soundex_unclean = $names;
foreach ($soundex_unclean as $name=>$dbname) {
  $soundex_unclean[$name] = sprintf("SELECT * from the_records WHERE name SOUNDS LIKE '%s';", $name).' /* matches name field = ['.$dbname.'] */';
}
echo '<pre>MySQL queries with attack -- '.print_r(array_values($soundex_unclean),1).'</pre>';

However, here is a run that DOES deal with the injection attack (note the new line). I know this isn't the focus of the question, but ajreal mentioned the issue, so I thought to deal with it as well:

$soundex_clean = $names;
foreach ($soundex_clean as $name=>$dbname) {
  // strip out everything but alpha-numerics and dashes
  $clean_name = preg_replace('/[^[:alnum:]-]/', '', $name);
  $soundex_unclean[$name] = sprintf("SELECT * from the_records WHERE name SOUNDS LIKE '%s';", $clean_name).' /* matches name field = ['.$dbname.'] */';
}
echo '<pre>MySQL queries with attack cleaned -- '.print_r($soundex_unclean,1).'</pre>';

If this approach does not suit, and you decided that the inline replacement approach is sufficient, then do remember to add a replacement for comma to the mix as well. As an example of that approach, I'm assuming here that the single quote, double quote, ampersand, and comma (i.e. ', ", &, and ,) are the only four special characters are included in the database but deleted from the URL, and that any other non-alpha-numeric character, spaces included, are converted to a dash (i.e. -).

First, a run that does not deal with the injection attack:

$unclean = $names;
foreach ($unclean as $name=>$dbname) {
  $regex_name = preg_replace('/[-]+/', '[^[:alnum:]]+', $name);
  $unclean[$name] = sprintf("SELECT * from the_records WHERE REPLACE(REPLACE(REPLACE(REPLACE(name, ',', ''), '&', ''), '\"', ''), \"'\", '') REGEXP '%s'", $regex_name);
}
echo '<pre>MySQL queries with attack -- '.print_r($unclean,1).'</pre>';

Second, a run that DOES deal with the attack:

$clean = $names;
foreach ($clean as $name=>$dbname) {
  $regex_name = preg_replace('/[^[:alnum:]-]/', '', $name);
  $regex_name = preg_replace('/[-]+/', '[^[:alnum:]]+', $regex_name);
  $clean[$name] = sprintf("SELECT * from the_records WHERE REPLACE(REPLACE(REPLACE(REPLACE(name, ',', ''), '&', ''), '\"', ''), \"'\", '') REGEXP '%s'", $regex_name);
}
echo '<pre>MySQL queries with attack cleaned -- '.print_r($clean,1).'</pre>';

Aaaand that's enough brainstorming for me for one night! =o)