1

In ActiveRecord (using Ruby on Rails), if i have @products variable where @products = Product.all, and I say:

@products.where("name = 'check123' "), it returns an array of objects matching that condition, if i however go @products.where('name="check123"') i get an error? 

: SELECT "products".* FROM "products" WHERE (name = "check123")
Hirb Error: PG::UndefinedColumn: ERROR:  column "check123" does not exist
LINE 1: SELECT "products".* FROM "products" WHERE (name = "check123"...
                                                      ^

Why is this happening? It seems that I must always use double quotes around everything in the where clause and single quotes for any strings inside there ?

Shouldn't single quotes work here as well, or is there something Im not getting

Some other observations:

@products.where("cost = '23.0'") works, event though 23 has a datatype of integer and not string? @products.where('cost = 23') works, so I know i can use single quotes inside the where clause

NOTE: I am aware of using the '?' syntax inside the where clause to avoid sql injections, I am purposefully trying to execute the query like this.

The Fabio
  • 5,369
  • 1
  • 25
  • 55
TABISH KHAN
  • 1,553
  • 1
  • 19
  • 32

1 Answers1

2

Double quotes are used for naming database objects (table names, columns names, user names, schema names...) whilst single quotes are intended to represent strings to be used as values.

so your UndefinedColumn: ERROR makes sense as when filtering with @products.where('name="check123"') the SQL produced will be .... where name = "check123". The db engine is trying to find a column with the name check123 and match its value to the value of column name. As the column check123 does not exist in your table, you get an "undefined column name" error.

For your other question:

When you filter an integer column by a string value what happens is that the db engine does an implicit conversion of the column values to string in order to perform the search.

Update

The general SQL standard is described by this question's answers. In summary most Db engines follow most of the ANSI standard, and the use of double quotes is usually reserved for database objects.

Community
  • 1
  • 1
The Fabio
  • 5,369
  • 1
  • 25
  • 55
  • 'double quotes - db objects, single quotes - string ' -> is this the cause specifically only for inside WHERE clauses ? also can you please provide a reference for this – TABISH KHAN Aug 21 '15 at 03:59
  • 1
    I've updated the answer with a reference for your, let us know if this is enough info for you – The Fabio Aug 21 '15 at 05:55