CODEWITHSUNDEEP
phpsqlpdoprepared-statement
doubleJ
doubleJ

Reputation: 1216

"&" And "And" In A Prepared Statement Failing

I have this chunk of PHP code (abridged for brevity) as a prepared statement that allows the user to search through a database using a series title...

$series = null;
if (isset($_GET["series"])) $series = $_GET["series"];

try {
    $dbh = new PDO("sqlsrv:Server=localhost;Database=Radio", "", "");
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    if (isset($series)) {
        $sql = "SELECT *
            FROM dbo.Schedule
            WHERE dbo.Schedule.Series LIKE :series
            ORDER BY dbo.Schedule.Date ASC";
    }
    $stmt = $dbh->prepare($sql);
    if (isset($series)) {
        $stmt->execute(array(":series" => $series));
    }
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    $dbh = null;
}
catch(PDOException $e) {
    echo $e->getMessage();
}

It works perfectly fine for any series title that DOES NOT include &, but it fails (produces no results) for any series title that does.

For instance, Abounding Ability works but Receiving & Ministering Healing does not (even though both are in the Series field of the database.

I tried htmlspecialchars(rawurlencode($_GET["series"])) and each one individually.

On the SQL server, passing the same query (using the title) works just fine.

SELECT *
FROM [Radio].[dbo].[Schedule]
where Series like 'Receiving & Ministering Healing'

My guess is that the problem is in the execution of the prepared statement or in binding the parameters.

I did a search on PHP's Prepared Statements page for &, but didn't see any results.

Edit:

I used this to get it working...

$series = null;
if (isset($_GET["series"])) $series = $_GET["series"];
$queries = parse_url($_SERVER['QUERY_STRING']);
if (substr($queries["path"], 7) == "Receiving%20&%20Ministering%20Healing") $series = "Receiving & Ministering Healing";

I'm sure it's a terrible way to do it, but no other option was working. Of course, that only works in this particular case. I welcome a better solution.

Edit 2:

I found that a series with the WORD And in it (Graces And Places) presents the same issue. I ended up duplicating my "fix" for Receiving & Ministering Healing for it.

I doesn't seem to have anything to do with the URL query (or the word And wouldn't matter), but the prepared statement.

Upvotes: 0

Views: 89

Answers (1)

ppeterka
ppeterka

Reputation: 20726

There can be multiple causes for this. The & character is evil on multiple levels, in this case, these two seem relevant:

It can be that your request looks like this (& signs highlighted under)

http://somedomain.com/somepage.php?param=something&series=Receiving & Ministering Healing
                                                  ^ (valid)         ^ (unintended!)

because the title is not escaped for URLs. This would mean that the & character marks a new parameter name - and would truncate the series to be Receiving instead of the full title Receiving & Ministering, resulting in the query:

SELECT *
FROM [Radio].[dbo].[Schedule]
where Series like 'Receiving '

You should probably show us how you assemble the URL for the request to be able to recommend more approporiate solution, but you should use urlencode() for escaping values to be used in GET parameters.

Or the problem could be (however it is not this case now) that you are writing the values wrongly.

A single & character is not valid in HTML! The correct escaping is &

You should HTML encode the output from the SELECT queries with htmlentities:

echo "<h1>".htmlentities($resultComingFromQuery)."</h1>";

EDIT

A bad request will likely result in a bad response (GIGO)... This:

http://www.flcbranson.org/api/radio/?series=Receiving%20%26%20Ministering Healing

is the proper request. This is URL encoded. You have to decode it:

$decodedTitle = urldecode($_GET['series']);

And use that in the query...

Upvotes: 2

Related Questions