使用pdo在搜索变量中使用冒号(:)进行查询

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....

我有个烦人的问题。我尝试做一些简单的事情，比如从db中获取单元格值。这是你能用db做的最基本的事情……给我一个值,这个值有一个细胞....

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...

问题是，搜索查询包含一个冒号(:)。我在一个有准备好的语句但没有运气的类中使用pdo函数。我尝试了所有的方法，甚至分割了查询，这样它就不会包含冒号，但仍然没有运气。我试图恢复mysqli，但结果还是一样……

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.

数据表包含标题-> Morlanda C:2和sourceID -> S11等值。顺便说一句，如果我想在phpmyadmin里搜索一个标题，我在找Morlanda的时候就会得到我想要的。

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包含没有冒号(:)的内容，则返回正确的sourceID。

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

嗨

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.

谢谢你的答案。我搜索的数据和db中的数据完全一样，使用复制/粘贴。我寻找过邪恶的白色空间，但没有发现任何额外的东西。现在我换成了bindValue。

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..

关于禁止模拟准备好的语句的评论，我的回答是。还有什麽?我现在找到了您在本文中讨论的关于仿真的内容:防止SQL注入的最佳方法是什么?，并更新了构造函数类。我仍然得到和…一样的结果。没有什么. .

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

我正在使用这个构造函数类作为我的db连接:

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

嗨

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:

当我硬编码搜索值Morlanda C:2到我的sql行时，一切都按预期运行。我比较了php生成的标题:

  $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++.

他们不匹配。做了类型检查，但都出来了刺。我开始认为是旧的UTF8编码问题困扰着我。我的数据库、表和单元格被设置为utf8_unicode_ci。pdo连接是UTF8，并使用Notepad++将php文件编码为“ANSI with UTF-8”(没有BOM)。

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.

这里的整个项目是将一个宗谱注释转换成一个源文件，所以我从一个表中，在同一个数据库中，收集一个长字符串，然后爆炸它，然后将一些片段组装成一个sourceTitle，如上面所示。然后我搜索数据库，看看源是否已经存在，如果没有，那么我就创建一个新的。数据的收集和对sourceTilte的搜索是由同一个pdo类完成的。

Update 3

嗨

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.

这是一个愚蠢的空白或类似的....我确实在$sourceTitle变量上使用了trim，但是不会在blow函数数组中的每一块上使用。当我这么做的时候，它成功了。我猜是在最后一个数字之后的端点或其他什么东西把我搞糊涂了。

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...

感谢您的帮助，我现在终于可以把我的3000条笔记转换成来源:)下一个项目是如何防止我的脚本自定义窒息我的VPS…

1 个解决方案

#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:

在不相关的注意事项上，最好使用bindValue()而不是bindParam()作为您的用例:

$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).

养成使用bindValue()的习惯，除非您知道您需要通过引用绑定(在这个和类似的情况下，您只使用一次值，所以您不需要通过引用绑定)。

#1