All geek questions in one place

SQLAlchemy case insensitive IN search query?

How to make the case insensitive to IN in SQLAclhemy ORM in a safe way?

Both I and others in my project were looking for this, but we cannot find anything that fits our needs.

In raw SQL, I could do:

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

.. if FOO and BAR were not entered by the user in an unknown case. Be that as it may, I am worried about the following:

  • Security: I do not need a visit from Bobby Tables ( http://xkcd.com/327/ ) in the form of SQL INjection Attack.and I can not find the documentation that tells me how to avoid strings in SQLAlchemy, or I will feel more safe joining of strings (but still feel dirty).
  • The speed is processed mainly by indexing, but obviously making corrections of the case in RAM before issuing the request will be faster than telling the database to do this, so I will not do UPPER in the request if I really shouldn't. However, the above was the best way to show what I want to do. But sti ;; he should not do anything crazy.
  • Agnostic code of the platform. I will run this on several types of databases - and it will be fully tested if I have any questions about this - and I do not want the query to bind to a specific SQL dialog box. That is, after all, why I use SQLAlchemy. :)

If this helps, we are currently linked to SQLAlchemy 8.4 due to use by other libraries.

+4
1

...

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

  • . in_( foo.uppercase() , bar.uppercase() )

  • SqlAlchemy DBAPI backend . - .

, -

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

, , Postgres Oracle, " "

CREATE INDEX table_fieldname_lower_idx ON table(lower(fieldname))

( ) , lower(fieldname) lower(fieldname).

+8

Source: https://habr.com/ru/post/1525875/

More articles:


All Articles