Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Column name must be quoted #829

Closed
sanami opened this issue Apr 11, 2017 · 2 comments
Closed

Column name must be quoted #829

sanami opened this issue Apr 11, 2017 · 2 comments
Labels
bug breakages in functionality that is implemented

Comments

@sanami
Copy link

sanami commented Apr 11, 2017

Search fails in postgres if any column name is not all downcased/

https://github.com/thoughtbot/administrate/blob/master/lib/administrate/search.rb#L23

it should be

def query
  search_attributes.map do |attr|
    "lower(#{ ActiveRecord::Base.connection.quote_column_name(attr) }) LIKE ?"
  end.join(" OR ")
end

https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)

@BenMorganIO
Copy link
Collaborator

@sanami would you like to get a PR in with this fix?

@BenMorganIO BenMorganIO added the bug breakages in functionality that is implemented label Apr 12, 2017
@klaseskilson
Copy link
Contributor

On it in #830

01navneet added a commit to 01navneet/administrate that referenced this issue Apr 13, 2017
iarie pushed a commit to iarie/administrate that referenced this issue Jun 17, 2017
* Add table name to search query

Problem: When default scopes with joins are used, the generated
search query can lead to ambiguity. For instance, the column
`id` may exist in two tables, which will lead to a SQL error when it is
impossible to decide which table to use.

Solution: Add `resource_class`'s `table_name` to the generated search
query. This ensures that the generated query produces the format
`table_name.id` instead of `id` in the where clause generated for the
search.

* Quote table and column name in search

Closes thoughtbot#829
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug breakages in functionality that is implemented
Projects
None yet
Development

No branches or pull requests

3 participants