忽略mysql搜索中的撇号 [英] Ignoring apostrophes in mysql searches

问题描述

我想获取一个没有任何撇号，逗号或与号的网址，并将其与数据库中可能包含这些字符之一的记录进行匹配.

I want to take a url that does not have any apostrophes, commas or ampersands in it and match it with a record in a database that may have one of those characters.

例如:

mywebsite.com/bobs-big-boy
mywebsite.com/tom--jerry
mywebsite.com/one-two-three

重写为

index.php?name=bobs-big-boy
index.php?name=tom--jerry
index.php?name=bobs-big-boy

然后在php中，我想使用$ _GET ['name']来匹配记录

Then in php I want to use the $_GET['name'] to match the records

bob's big boy
tom & jerry
one, two, three

现在我的查询如下:

"SELECT * from the_records WHERE name=$NAME";

我无法更改记录，因为它们是公司名称.有没有办法我可以编写查询来忽略数据库中的&"号，逗号和撇号?

I can't change the records, because they're business names. Is there a way I can write the query to ignore ampersands, commas and apostrophes in the db?

推荐答案

是的，但是我敢肯定，它将忽略您在该列上拥有的所有索引.真令人恶心.

Yes you can but I'm pretty sure it will ignore any indexes you have on the column. And it's disgusting.

类似

SELECT * FROM the_records 
WHERE replace(replace(replace(name, '''', ''), ',', ''), '&', '') = $NAME

据我所知，采用这样的get变量并将其注入mysql查询的方式对于sql注入可能已经成熟.

By the way taking a get variable like that and injecting it into the mysql query can be ripe for sql injection as far as I know.

pg，我知道您说过您不能更改/更新要从中选择的数据库中的内容，但是是否有什么阻止您在您具有写权限的另一个数据库中创建表的原因?您可以将urlnames映射到公司名称，并且第一次使用replace方法只会很慢.

pg, I know you said you can't change/update the content in the database you're selecting from, but does anything preclude you from making a table in another database you do have write access to? You could just make a map of urlnames to business names and it'd only be slow the first time you do the replace method.

这篇关于忽略mysql搜索中的撇号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！