在 Rails 3/ActiveRecord 中构建 LIKE 查询时逃避 %% 的正确方法 [英] a proper way to escape %% when building LIKE queries in Rails 3 / ActiveRecord

问题描述

我想将 url 字段与 url 前缀(可能包含百分号)进行匹配，例如.where("url LIKE ?", "#{some_url}%").最常用的 Rails 方式是什么?

I want to match a url field against a url prefix (which may contain percent signs), e.g. .where("url LIKE ?", "#{some_url}%"). What's the most Rails way?

推荐答案

如果我理解正确，您会担心 some_url 中出现%"，这是正确的；您还应该担心嵌入的下划线(_")，它们是."的 LIKE 版本.在正则表达式中.我认为没有任何特定于 Rails 的方法可以做到这一点，所以你只剩下 gsub:

If I understand correctly, you're worried about "%" appearing inside some_url and rightly so; you should also be worried about embedded underscores ("_") too, they're the LIKE version of "." in a regex. I don't think there is any Rails-specific way of doing this so you're left with gsub:

.where('url like ?', some_url.gsub('%', '\\\\\%').gsub('_', '\\\\\_') + '%')

这里也不需要字符串插值.您需要将反斜杠加倍以从数据库的字符串解析器中转义它们的含义，以便 LIKE 解析器将看到简单的\%"并知道忽略转义的百分号.

There's no need for string interpolation here either. You need to double the backslashes to escape their meaning from the database's string parser so that the LIKE parser will see simple "\%" and know to ignore the escaped percent sign.

您应该检查您的日志以确保两个反斜杠通过.我在 irb 中检查东西时得到了令人困惑的结果，使用五个 (!) 得到正确的输出，但我看不出它的意义；如果有人确实看到其中五个的意义，我们将不胜感激.

You should check your logs to make sure the two backslashes get through. I'm getting confusing results from checking things in irb, using five (!) gets the right output but I don't see the sense in it; if anyone does see the sense in five of them, an explanatory comment would be appreciated.

更新:Jason King 为转义字符的噩梦提供了一种简化.这使您可以指定 临时转义字符，以便您可以执行操作像这样:

UPDATE: Jason King has kindly offered a simplification for the nightmare of escaped escape characters. This lets you specify a temporary escape character so you can do things like this:

.where("url LIKE ? ESCAPE '!'", some_url.gsub(/[!%_]/) { |x| '!' + x })

我还切换到了 gsub 的块形式以使其不那么讨厌.

I've also switched to the block form of gsub to make it a bit less nasty.

这是标准的 SQL92 语法，因此适用于任何支持它的数据库，包括 PostgreSQL、MySQL 和 SQLite.

This is standard SQL92 syntax, so will work in any DB that supports that, including PostgreSQL, MySQL and SQLite.

将一种语言嵌入另一种语言总是有点像噩梦般的混杂，对此您无能为力.总会有一些丑陋的小点，你只需要笑着忍受.

Embedding one language inside another is always a bit of a nightmarish kludge and there's not that much you can do about it. There will always be ugly little bits that you just have to grin and bear.

这篇关于在 Rails 3/ActiveRecord 中构建 LIKE 查询时逃避 %% 的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！