SQLAlchemy 不区分大小写的基于 IN 的搜索查询? [英] SQLAlchemy case insensitive IN based search query?

问题描述

如何以安全的方式在 SQLAclhemy ORM 中进行不区分大小写的 IN 搜索?

How do I do a case insensitive IN search in the SQLAclhemy ORM in a way that is secure?

我自己和我项目中的其他人都在寻找这个，但我们似乎找不到任何适合我们需要的东西.

Both myself and others on my project have looked for this, but we cant seem to find anything that fits our needs.

在原始 SQL 中我可以这样做:

In raw SQL I could do:

 SELECT * FROM TABLENAME WHERE UPPER(FIELDNAME) IN (UPPER('foo'), UPPER('bar'));

..如果在未知情况下 FOO 和 BAR 不是用户输入.事实上，我担心以下几点:

..if FOO and BAR were not user input in unknown case. As it is, I am worried about the following:

1. 安全性:我不希望 Bobby Tables 访问 (http://xkcd.com/327/) 以 SQL 注入攻击的形式.而且我找不到告诉我如何在 SQLAlchemy 中转义字符串的文档，否则我会觉得加入字符串更安全(但仍然觉得这样做很脏).
2. 速度主要是通过索引来处理的，但很明显，在发出查询之前在 RAM 中进行大小写更正会比告诉数据库这样做更快，所以除非我真的必须这样做，否则我不会在查询中使用 UPPER.然而，以上是展示我想做的事情的最佳方式.但是 sti;;，它不应该做任何疯狂的事情.
3. 平台不可知的代码.我将在多种数据库类型上运行它 - 并且将对其进行全面测试，我对此事有任何发言权 - 我不希望查询绑定到特定的 SQL 对话框.毕竟，这就是我使用 SQLAlchemy 的原因.:)

1. Security: I don't want a visit from Bobby Tables (http://xkcd.com/327/) in the form of an SQL INjection Attack.and I cant find the documentation that tells me how to escape strings in SQLAlchemy or I would feel safer joining strings (But still feel dirty doing it).
2. Speed is handled largely by indexing but obviously, doing case corrections in RAM before issuing the query would be faster than telling the DB to do it, so I would not do the UPPER in the query unless I really had to. The above was however the best way to show what I want to do. But sti;;, it shouldn't do anything crazy.
3. Platform agnostic code. I will be running this on multiple database types - and its going to be fully tested is I have any say on the matter - and I don't want the query to be bound to a specific dialog of SQL. That is, after all, why I am using SQLAlchemy. :)

如果有帮助，由于我们使用其他库，我们目前绑定到 8.4 版本的 SQLAlchemy.

If it helps we are currently bound to the 8.4 version of SQLAlchemy due to our use of other libraries.

推荐答案

这应该完全编译...

query( models.Object )\
.filter( 
     sqlalchemy.func.upper( models.Object.fieldname )\
     .in_( (sqlalchemy.func.upper(foo) , sqlalchemy.func.upper(bar), ) )
)\
.all()

<小时>

1. 你也可以只传入大写文本.就个人而言，我会做 in_( foo.uppercase() , bar.uppercase() )

SqlAlchemy 与 DBAPI 一起将绑定参数传递到您的后端数据存储中.转换 -- 值会自动转义.

SqlAlchemy works with the DBAPI to pass bind parameters into your backend datastore. Translation -- values are automatically escaped.

<小时>

如果你想做一个字符串列表，这样的事情应该可以工作

---

if you want to do a list of strings , something like this should work

.in_( [ i.upper() for i in inputs ] )
.in_( [ sqlalchemy.func.upper(i) for i in inputs ] )

<小时>

只想补充一点，如果您想优化这些选择以提高速度，并且在 Postgres 或 Oracle 上，您可以创建一个函数索引"

---

Just want to add that if you want to optimize these selects for speed, and are on Postgres or Oracle, you can create a 'function index'

CREATE INDEX table_fieldname_lower_idx ON table(lower(fieldname))

查询规划器(在数据库中)将知道在针对 lower(fieldname) 查询进行搜索时使用该 lower(fieldname) 索引.

the query planner (in the database) will know to use that lower(fieldname) index when searching against a lower(fieldname) query.

这篇关于SQLAlchemy 不区分大小写的基于 IN 的搜索查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！