Queries with colon (:) in the search variable using pdo

Question

I have an annoying problem. I'm trying to do something simple as getting a cell value from a db. This is the most basic thing you can do with a db... give me a value where there is a cell with this value....

The problem is that the search query contains a colon (:). I'm using pdo function in a class with prepared statements but no luck. I have tried everything, even dividing up the query so it wont contain a colon but still no luck. I have tried to revert to mysqli but still the same result...

The data table contain values like title -> Morlanda C:2 and sourceID -> S11. btw, if I try to search for an title in phpmyadmin I'll get what I want when I look for Morlanda C:2.

But when I'm calling my function, thisly:

$sourceID = $sources->sourceAvalibe('Morlanda C:2');

I'm accessing my function:

public function sourceAvalibe($sourceTitle){
    try {
        $sql = "SELECT sourceID FROM sources WHERE title=:sourceTitle";
        $core = Core::getInstance();
        $stmt = $core->dbh->prepare($sql);
        $stmt->bindParam(':sourceTitle', $sourceTitle, PDO::PARAM_STR, 32);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row;    
}

Then the result will be empty..

but if I call the function like this:

 $sourceID = $sources->sourceAvalibe('1910 Massachusetts Census');

The result will return what I'm looking for.

The result will be an empty if the query contains a colon (:) but will return the correct sourceID if I it contains something without an colon (:).

I have tried to escape the colon in different ways but it wont find a result then either.

Can you please help me before I go bananas???

Update 1

Hi

Thanks for your answers. The data I'm searching for is exactly the same as in the db, using copy/paste. I have looked for evil white spaces but nothing extra was found. I have now switched to bindValue instead.

Regarding the comment about disable emulating of prepared statements my answer is.. Que? :) I have now found what you where talking about regarding emulating, in this article: Best way to prevent SQL injection?, and have updated my constructor class. I'm still getting the same result tho.. nothing..

I'm using this constructor class for my db connections:

class Core {

    public $dbh;
    private static $instance;

   function __construct(){
        $this->dbh = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass, 
        array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", 
              PDO::ATTR_EMULATE_PREPARES => false));
    }

    public static function getInstance(){
        if (!isset(self::$instance)){
            $object = __CLASS__;
            self::$instance = new $object;
        }
        return self::$instance;
    }

}

Update 2

Hi

When I hard code the search value, Morlanda C:2, into my sql line everything works as intended. So I did a comparison of my php generated title:

  $sourceTitle = $sourcePreTitle." ".$preTitleNumber[0].":". $preTitleNumber[1];

with:

   $orginalString = "Morlanda C:2";

and they didn't match. Did a type check but both came out as stings. I'm starting to think that it's the old UTF8 encoding problem that is messing with me here. My database, table and cell is set to utf8_unicode_ci. The pdo connection is UTF8 and have encoded the php files with 'ANSI with UTF-8' (without BOM) using Notepad++.

The whole project here is to turn a genealogy note to a source so I'm collection a long string from a table, in the same database, and exploding it and then assemble some pieces into tho a sourceTitle like above. I then searching the database to see if the source already exists, if not, then I just create a new one. The collection of the data and searching for the sourceTilte is done by the same pdo class.

Update 3

Hi

It was a stupid white space or something similar.... I did use trim at the $sourceTitle variable but not on each piece in the array from the explode function. When I did that, it worked. I guess it was a endline or something after the last number that messed with me.

Thanks for your help I can now finally convert my 3000 notes to sources :) Next project is how to prevent my scripts custom to choke my VPS...

Answer 1

The data that you bind does not need to be escaped in any way. That's pretty much the whole point of bind parameters. Your problem is not that there is a colon in the data. Most likely, you've typoed the title, either in the code, or in the actual database record.

On an unrelated note, it would be better to use bindValue() instead of bindParam() for your use case:

$stmt->bindValue(':sourceTitle', $sourceTitle);

Get in the habit of using bindValue() unless you know that you need to bind by reference (in this and similar cases, you're only using the value once, so you have no need to bind by reference).