Dot (Full Stop) in Get Value Breaks the SQL Query (PDO)

Question

I'm fetching results from MySQL database using PDO and I use value from $_GET request method as a condition. Everything works fine but if there is any fullstop (dot) in the $_GET value, MySQL returns 0 rows.

Here is my sample:

<?php
    function filter($val) {
        $f = htmlentities($val);
        $f = filter_input(INPUT_GET, $f);
        return strip_tags($f);
    }

    $dev = filter("dev");

    function DevFetch($dev) {
        $q = $this->link->prepare("SELECT app FROM table WHERE dev = ?");
        $q->bindValue("1", $dev);
        $q->execute();
        if($q->rowCount() > 0) {
            return $q->fetchAll();
        } else {
            return false; 
        }
    }
?>

Here are some examples.

Case 1:

results.php?developer=Google+Inc // works fine

Case 2:

results.php?developer=Google // works fine

Case 3:

results.php?developer=Google+Inc. // doesn't work with dot at the end

Please help with this. Note that I'm encoding (urlencode()) the $_GET value as well as filtering it using filter_input() function. Without filtering / encoding also doesn't work.

There's a difference between `the query fails` and `returns 0 rows`, too. A query that returns 0 rows has worked, just not found anything that matches. — andrewsi, Oct 23 '15 at 15:48
Is there a record that matches `results.php?developer=Google+Inc.`? — chris85, Oct 23 '15 at 16:13
`filter_input` is useful to fend off XSS-injection, but it's not as effective to defend against SQL injection. think square pegs and round holes, use the right tool for the job: ie prepared statements — Elias Van Ootegem, Oct 23 '15 at 16:27
@EliasVanOotegem I already use prepared statements but for maintaining the front layout even if someone provides a script tag in get request, I filter and strip the input. Please check my code sample, I have updated the question. — Rehmat, Oct 23 '15 at 16:32
@rehmat: I think it better to use `striptags` to remove any `script` tags in user input, but that's your choice... Either way, posted an answer that, I think, explains your problem — Elias Van Ootegem, Oct 23 '15 at 16:37
*Without filtering / encoding also doesn't work* ? what encoding? what exactly doesn't work? — Alex, Oct 23 '15 at 16:57

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Taken directly from the docs:

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

This means that this statement (being a SELECT):

$this->link->prepare("SELECT app FROM table WHERE dev = ?");

does not affect the return value of rowCount. To get the row count, you'll have to resort to mysqli or write:

$rows = $stmt->fetchAll();
$rowCount = count($rows);

If what you say is indeed true, and only the value with a dot on the end doesn't return a value for rowCount, then here's a couple of things you really ought to check:

PDO dsn string: specify the charset (add ;charset=utf8 to the end of the DSN string. details here
Set the error mode to have PDO throw exceptions on failure: PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
Check your DB for rows with the value that has the dot on the end, if it isn't there, than your code works as expected, simply because there are no results to work with

Thank you for your answer, but rowCount() works if there isn't any dot in the $_GET parameter. I even tried without rowCount() condition, still no luck. — Rehmat, Oct 23 '15 at 16:44
@rehmat: updated my answer Actually mentioning the most critical thing to check last: Is there a record where `dev = 'Google+Inc.'` in your DB at all? if not: your code works, because there simply is nothing to fetch/return... — Elias Van Ootegem, Oct 23 '15 at 16:51
.htaccess is removing the dot from $_GET value. Thank you for your answer, it taught me something. I was missing the charset in my dsn, added that. Thanks again. — Rehmat, Oct 23 '15 at 17:07

score 1 · Answer 2 · answered Oct 23 '15 at 16:47

1

Since you use prepared statements, you don't need that filter function.

Just that simple:

 function DevFetch($dev) {
    $q = $this->link->prepare("SELECT app FROM table WHERE dev = ?");
    $q->bindValue(1, $dev);
    $q->execute();
    $result = $q->fetchAll();
    if(count($result) > 0) {
        return $result;
    } else {
        return false; 
    }
}

$input = $_GET["dev"];
DevFetch($input);

answered Oct 23 '15 at 16:47

Alex

16,739
1
28
51

Well, if OP has filled the DB with all that filter guff, he'll need to carry on using it to retrieve his data. The critical thing to ask here is if there _is_ a record for the query (`WHERE dev = 'Google+Inc.'`), maybe that's the problem to begin with... – Elias Van Ootegem Oct 23 '15 at 16:52
Figured it out, .htaccess file is doing something bad with the $_GET value. It is removing the dot. Will need to look into that. Thanks for your answer! – Rehmat Oct 23 '15 at 17:06

Dot (Full Stop) in Get Value Breaks the SQL Query (PDO)

2 Answers2