Handling the '.' character passed to a MySQL query via url

I've hit a bit of a snag with one of my projects. Like a lot of nerds, I decided to create my own video game review site. Reviews are stored in the database, and can be retrieved via the title of the game with a url like:

http://www.example.com/reviews/{gameName}/{optional pageOfReview}

Unfortunately, when testing edge cases, I came across an odd error - if the game has a period in its title I can't retrieve it. If the period is the leading character of the title (like .hack), I get a Kohana stack trace error screen telling me the review (or, more accurately, the game) doesn't exist. If it's in the middle or end of the title, I get one of my own error messages saying that the review (game) could not be retrieved. Is there any way around this? Is it an issue of how MySQL parses a period, or something else?

EDIT: All queries are handled via Kohana 2's ORM functionality using the MySQLi driver. Saving a review (admin controller):

public function saveReview()
{
    $this->checkAdmin();

    if (isset($_POST['submit'])) { $this->storeReview(); }
    else { header('Location: /admin'); }
}

private function storeReview($id = null)
{
    if (!preg_match("/^[a-zA-Z0-9\-_:!'. ]*$/", $_POST['gameTitle']) || empty($_POST['gameTitle'])) { $gameTitle = false; }
    else { $gameTitle = ucwords($this->clean($_POST['gameTitle'])); }

    if (!is_numeric($_POST['genre'])) { $genre = false; }
    else { $genre = $_POST['genre']; }

    $platformCheckArray = array_map('is_numeric', $_POST['platforms']);

    $platformCheck = true;
    foreach ($platformCheckArray as $pca)
    {
        if (!$pca)
        {
            $platformCheck = false;
            break;
        }
    }

    $proCheck = true;
    $cleanedPros = array();

    foreach ($_POST['pros'] as $pro)
    {
        if (!preg_match("/^[a-zA-Z0-9\-_:!' ]*$/", $pro))
        {
            $proCheck = false;
            break;
        }

        if (!empty($pro)) { $cleanedPros[] = $this->clean($pro); }
    }

    $conCheck = true;
    $cleanedCons = array();

    foreach ($_POST['cons'] as $con)
    {
        if (!preg_match("/^[a-zA-Z0-9\-_:!' ]*$/", $con))
        {
            $conCheck = false;
            break;
        }

        if (!empty($con)) { $cleanedCons[] = $this->clean($con); }
    }

    if (!is_numeric($_POST['score'])) { $score = false; }
    else { $score = $_POST['score']; }

    if (empty($_POST['content'])) { $content = false; }
    else { $content = true; }

    // save review if all tests pass, display error otherwise

    if ($gameTitle && $genre && $platformCheck && $proCheck && $conCheck && $score && $content)
    {
        $gameTitle = $gameTitle;
        $platforms = $_POST['platforms'];
        $reviewContent = $_POST['content'];
        $prosText = implode(', ', $cleanedPros);
        $consText = implode(', ', $cleanedCons);

        $game = ORM::factory('game');
        $game->title = $gameTitle;
        $game->genre_id = $genre;
        $game->platforms = $platforms;
        $game->save();

        $storedGenre = ORM::factory('genre')->where('id', $genre)->find();
        $storedGenre->platforms = $platforms;
        $storedGenre->save();

        $review = ORM::factory('review', $id);
        $review->content = $reviewContent;
        $review->score = $score;
        $review->game_id = $game->id;
        $review->date_added = date('Y-m-d H:i:s');
        $review->platforms = $platforms;
        $review->save();

        $pros = ORM::factory('pro');
        $pros->review_id = $review->id;
        $pros->text = $prosText;
        $pros->save();

        $cons = ORM::factory('con');
        $cons->review_id = $review->id;
        $cons->text = $consText;
        $cons->save();

        if ($game->saved && $storedGenre->saved && $review->saved && $pros->saved && $cons->saved) { $this->success('review'); }
        else { $this->showError("Something went wrong with saving the review.  Please try again."); }
    }
    else { $this->showError("All fields must contain values.  Please try again."); }
}

Retrieving a review (from the reviews controller):

public function show($id, $page = 1)
{
    if (is_numeric($id)) { $game = ORM::factory('game', $id); }
    else
    {
        $id = ucwords(stripslashes($id));
        $game = ORM::factory('game')->where('title', $id)->find();
    }

    if ($game->loaded) { $this->showReview($game->id, $page); }
    else { HandiError::factory('Could not retrieve the specified review.  Please check that you entered the correct value.'); }
}

private function showReview($id, $page = 1)
{
    $page = (int)$page;

    if ($page < 1) { $page = 1; }

    if ($id)
    {
        $game = ORM::factory('game', $id);
        $review = ORM::factory('review')->where('game_id', $game->id)->find();
        $genre = ORM::factory('genre')->where('id', $game->genre_id)->find();
        $revPlatforms = $this->db->query("SELECT * FROM platforms
                                      INNER JOIN platforms_reviews AS pr ON platforms.id = pr.platform_id 
                                      INNER JOIN reviews ON pr.review_id = reviews.id 
                                      WHERE reviews.id = ?", $review->id);
        $revPros = ORM::factory('pro')->where('review_id', $review->id)->find();
        $revCons = ORM::factory('con')->where('review_id', $review->id)->find();

        $platforms = array();
        foreach($revPlatforms as $rp) { $platforms[] = $rp->name; }
        $pros = explode(', ', $revPros->text);
        $cons = explode(', ', $revCons->text);

        $pages = explode('&lt;split /&gt;', $review->content);
        $count = count($pages);

        if ($page > ($count)) { $content = $pages[0]; }
        else { $content = $pages[$page - 1]; }

        $view = new View('reviews/show_review');
        $view->content = $content;
        $view->gameTitle = $game->title;
        $view->genre = $genre->name;
        $view->platforms = implode(', ', $platforms);
        $view->pros = $pros;
        $view->cons = $cons;
        $view->score = $review->score;
        $view->pages = $pages;
        $view->render(true);
    }
    else { HandiError::factory('Could not retrieve the specified review.  Please check that you entered the correct value.'); }
}

EDIT 2: Well, I found out something about the leading period case:

In my controller's index, I have a few queries that I use to list the reviews by game title, platform, genre, etc. It's basically a poor man's wiki. See:

public function index()
{
    /* show a wiki-like page with reviews listed by title, 
     * game title, genre, and platform
     */

    $numGenres = $this->db->query("SELECT COUNT(id) AS num FROM genres");
    $numPlatforms = $this->db->query("SELECT COUNT(id) AS num FROM platforms"); 

    $genreCount = $numGenres[0]->num;
    $platformCount = $numPlatforms[0]->num;
    $scoreCount = 5;

    $genreResults = array();
    $platformResults = array();
    $scoreResults = array();

    $gameResults = $this->db->query("SELECT LEFT(title, 1) AS letter, COUNT(id) AS count FROM games GROUP BY letter ORDER BY letter ASC");

    for($i = 1; $i < ($genreCount + 1); ++$i)
    {
        $gen开发者_如何学GoreResults[] = $this->db->query("SELECT genres.id AS id, genres.name AS name, COUNT(reviews.id) AS num FROM reviews 
                                            INNER JOIN games ON reviews.game_id = games.id 
                                            INNER JOIN genres ON games.genre_id = genres.id 
                                            WHERE genres.id = ?", $i);
    }

    for($j = 1; $j < ($platformCount + 1); ++$j)
    {
        $platformResults[] = $this->db->query("SELECT platforms.id AS id, platforms.name AS name, COUNT(reviews.id) AS num FROM reviews 
                                               INNER JOIN platforms_reviews AS pr ON reviews.id = pr.review_id 
                                               INNER JOIN platforms ON pr.platform_id = platforms.id 
                                               WHERE platforms.id = ?", $j);
    }

    for($k = 1; $k < ($scoreCount + 1); ++$k)
    {
        $scoreResults[] = $this->db->query("SELECT score, COUNT(id) AS num FROM reviews WHERE score = ?", $k);
    }

    $view = new View('reviews/index');
    $view->gamesByLetter = $gameResults;
    $view->genres = $genreResults;
    $view->platforms = $platformResults;
    $view->scores = $scoreResults;
    $view->render(true);
}

When I pass the results of those queries to the view, I loop through them and create links based on the meta category. So, it shows how many games start with the letter A, B, etc., and clicking on one of those links brings the user to a list of links, each one with a review (So, A->Afterburner (among others)->review for Afterburner).

When I mouse over the group that has the leading period, my status bar shows that the period is missing from the link, even though it shows up in the source. So, even though the source shows the link as site.com/reviews/game/. the browser shows it as site.com/reviews/game/ This makes me believe that the period isn't even being passed into the method, and the stack trace seems to confirm (it claims there's a missing argument, which would be the period).

EDIT 3: Okay, I took a look at my routes, and can't find anything there. That said, I do have a .htaccess file that mod_rewrites the routes to look pretty for SEO, so I'm wondering if that could be the problem. I've never written a mod_rewrite file myself - the people on the Kohana forums gave me this, and it worked, so I went with it. I can understand some of the regEx involved, but my regEx Fu is weak. I believe the last line does the 'magic'.

# Turn on URL rewriting
Options +FollowSymlinks
RewriteEngine On

# Put your installation directory here:
# If your URL is www.example.com/, use /
# If your URL is www.example.com/kohana/, use /kohana/
RewriteBase /

# Do not enable rewriting for files or directories that exist
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d

# For reuests that are not actual files or directories,
# Rewrite to index.php/URL

# Original rule:
# RewriteRule ^(.*)$ index.php/$1 [PT,L]

# Alternative rule:
# RewriteRule .* index.php/$0 [PT,L]

# 2nd alternative rule that works on ICDSoft:
RewriteRule .* index.php?kohana_uri=$0 [PT,QSA,L]

If I'm reading this right, the '.' simply means any single character.

Can a '.' be used in a well-formed URL aside from where it denotes a file extension or web suffix (.com, .org, etc.)? I mean, they aren't appearing in Firefox's status bar when I hover over a link with them, which leads me to believe it's a browser/well-formedness issue, not a coding one.

Time to check all generated queries with Profiler.

in Controller::__construct() put

new Profiler;

and find the possibly broken query.

Other possible solution: Go trough your code, sometime un-closed/un-terminated Database Query instance may broke (or merged) the other query...

MySQL has no problem with periods in column data. However, the period is used to separate table names from column names: table.column. If your queries are not properly escaped and quoted, the period may be incorrectly interpreted as a table/column separator.

How are you preparing your queries?

I think, that problem is in Kohana framework, not in SQL. Chcek out for filtering parameters from url. Try to print your query and see what it looks exact in the moment when it is going to be executed, and watch what happened with your period.

EDIT: Sorry, I didn't see you are using Kohana version 2.x. I doubt this applies to you.

Just a guess, but did you set your route to allow for periods in the url? By default Kohana doesn't allow periods. You would need to set the third argument of Route::set() to something like this:

Route::set('reviews', 'reviews/<name>(/<page>)', array('name' => '[^/,;?]++', 'page' => '\d+')
    ->defaults(array(
        'controller' => 'reviews',
        'action'     => 'load',
        'name'       => NULL,
        'page'       => 1,
    ));

See forum post http://forum.kohanaframework.org/comments.php?DiscussionID=4320