另一个脑筋急转弯。
这不是通常的“但是撇号破坏了我的查询”，因为我知道所有关于转义和清理的知识，所以我也会做对的。
许多东西都存储在数据库中，名称如“Assassin's”和“Babe's”，最终变成“Assassin\'s”和“Babe\'s”，恢复很好，但通过文本搜索......很痛苦。
LIKE "%Babe\'s%" 没有结果，"%Babe\\'s" 没有结果，"%Babe\''s" 没有结果。
但是如果我直接访问服务器那么它们都会产生结果。
换句话说，完全未经编辑的相同查询将直接在 MySQL 引擎中工作，但通过 php 的 mysql api 发送，它不会产生匹配的结果。
有什么想法可能导致这种情况吗？ （我已经检查了 100 倍的斜线等，是否存在字符集问题？”
提前很多很多很多感谢。
编辑：
我想我最好让自己更清楚：
“从游戏中选择标题，其中标题LIKE "%assassin\\\'s%"; （因为 SQL 应该转义撇号，并且斜杠之一将变成存储的斜杠，因为我们正在寻找“Assassin\'s Creed”在这个例子中）
edit2：我们发现这是由于清理不当而导致实际数据库中的转义造成的。
edit3：看来。就像神奇的引号以某种方式打开一样......我发誓它被关闭了，但这不仅仅是这个网站的数据库包装器有一个干净的事件和一个导致问题的预清理。现在已经修复，现在我正在运行一个脚本来（希望）清理数据库......
another brain teaser.
This isn't the usual "but Apostrophes are breaking my Queries" as I know all about escaping and sanitising so I'll get right too it.
Many many things are stored in a db with names like "Assassin's" and "Babe's" which end up "Assassin\'s" and "Babe\'s", recovering is good but searching via text is... painful.
LIKE "%Babe\'s%" has no results, "%Babe\\'s" has no results, "%Babe\''s" has no results.
BUT if I go to the server directly then they will all produce results.
In other words, the SAME query totally unedited will work directly in the MySQL engine but sent via php's mysql api it produces no matched results.
Any ideas what could cause this? (I've checked 100 times the amount of slashes etc, is there a character set issue?"
Many many many many thanks in advance.
edit:
I think i better make myself more clear:
"SELECT title FROM games WHERE title LIKE "%assassin\\\'s%"; (Since SQL should escape the apostrophe and one of the slashes will turn into the slash that was stored, since we're looking for "Assassin\'s Creed" in this example)
edit2: Sow we've figured out this is caused by having escaped escapes in the actual db caused by poor sanitising. Currently I'm in the process of trying to cleanup the db and the input method.
edit3: It seems like magic quotes were on somehow... I swear that was turned off! However it wasn't just that. The DB wrapper which is in place for this site has a clean event and also a pre-clean which caused the issue. That has been fixed now and now I'm running a script to (hopefully) cleanse the db...
发布评论
评论（5）
我会认真考虑修复您数据库中的数据。
现在，话虽如此，MySQL 将
\'和''识别为转义撇号。也就是说，除非服务器模式设置为使用严格 SQL，在这种情况下仅识别''。数据肯定会在输入时进行双重转义，很可能在用户输入数据时进行一次转义（通过
magic_quotes_gpc，它使用addslashes) 并再次通过mysql_real_escape_string当你调用它时。这会将
刺客信条转变为刺客信条，最终将存储为刺客信条。如果可以的话，我强烈建议禁用
magic_quotes_gpc，因为它导致的问题比它解决的问题还要多。I would seriously consider fixing the data in your database.
Now, having said that, MySQL recognizes both
\'and''as an escaped apostrophe. That is, unless the server mode is set to use strict SQL, in which case only''is recognized.The data is most certainly getting double-escaped on entry, most likely once when the user enters it (by
magic_quotes_gpc, which usesaddslashes) and again bymysql_real_escape_stringwhen you call it.This would turn
Assassin's CreedintoAssassin\\\'s Creed, which would ultimately get stored asAssassin\'s Creed.I highly recommend disabling
magic_quotes_gpcif you can, as it causes more problems than it fixes.您是否尝试使用 mysql_real_escape_string ，还要检查 magic_quotes 是否启用并弄乱您的字符串
Did you try to use mysql_real_escape_string, also check if magic_quotes is enabled and messing with your strings
当您转义撇号以便将其存储在 MySQL 中时，反斜杠不会被存储。
所以试试这个：
而不是：
When you escape an apostrophe so that you can store it in MySQL, the back slash isn't stored.
So try this:
Instead of:
从游戏中选择标题，其中标题正则表达式“刺客”；
将 REGEXP 视为 MySql 中更强大、更不挑剔的“LIKE”......不是最重要的，而是商店中的另一个很酷的工具。
SELECT title FROM games WHERE title REGEXP "assassin\'s";
Think of REGEXP as a more powerful, less picky "LIKE" in MySql....Not the be-all, end-all, but another cool tool in the shop.
正如文档 所述，斜线在 LIKE 和REGEXP 子句必须加倍
编辑：错误链接已更正
As documentation says, slashes in LIKE and REGEXP clauses must be doubled
Edit: wrong link corrected