Quoting a primary key for a relation, where a foreign key is string

Rails
1

Hi guys,

The story is that I have two models Survey & Email Template as defined
in a
sample app:

When I’m calling survey.email_templates, it fails, and I have the
following error:

ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: operator
does not exist: character varying = integer
LINE 1: …M “email_templates” WHERE “email_templates”.“survey_id” = 1
^
HINT: No operator matches the given name and argument type(s). You
might
need to add explicit type casts.
: SELECT “email_templates”.* FROM “email_templates” WHERE
“email_templates”.“survey_id” = 1

I’m not sure whether I should treat it as a rails bug, and that rails
should quote this integer, or not. I could look further into AR, if you
feel, that this case should be handled. Otherwise, I’ll be looking for a
different solution for this challenge.

Also, I may mention source of this issue. Earlier our app was on
EngineYard, where we had custom casting for this, so whenever there was
an
integer, it was casted into a string. Then we moved to RDS AWS, and
unfortunately there we can’t create castings. I dropped all of them, and
I
found this case. So I thought that it might be treated as a rails bug.

Regards,
Simon

2

On Wednesday, June 24, 2015 at 3:21:39 PM UTC+3, simon2k wrote:

Why is survey id a string column? I believe AR is casting as an integer
because the column being compared with is an integer (your primary key
on the other table). Not doing this cast has been at the root of
security problems in the past if my memory is correct.

Fred

3

Thanks, I know this, but I’m asking about case in which survey_id is a
string, not an integer since I need to handle this case.

The example in gist is simplified, but setup of models represents how
they
should be set up. Basically the thing is that I have a polymorphic
relation, but in the gist I simplified it. I’ll update the gist and add
a
real usage for this. Here it
is: sample rails app for bug · GitHub

4

ActiveRecord::Schema.define do
create_table :surveys, force: true do |t|

end

create_table :email_templates, force: true do |t|
t.integer :survey_id
end
end

The auto-generation of primary key in surveys is data type integer.
Therefore the foreign key data type in email_templates should match.
In your models, you might want to declare: self.primary_key = ‘id’. But
I don’t think it’s necessary.
Just to be sure, you should probably go to your database and verify the
contents of your tables:
sql> use ‘your databasename’
sql> describe surveys;
sql> describe email_templates;
sql> SELECT surveys. * , email_templates. *
FROM surveys
INNER JOIN email_templates ON surveys.id = email_templates.survey_id

In irb> Survey.email_templates.to_sql … might be helpful as well for
verification

Hope this helps

5

Earlier, I had a custom cast in the DB, whether it was id of a tfs or a
survey, it was casted appropriately, but since I removed the casting, I
have the issue, that it’s not quoting id of a survey.

6

Okay… this is interesting. I am not familiar with

as: :object_with_email_template
I think that for testing purposes, I would just be more explicit and
stick with polymorphic suffix -able, see
http://www.xyzpub.com/en/ruby-on-rails/3.2/activerecord_polymorphic.html
Now the matter of differing id types… call me crazy, but I would
introduce a new model to manage the differences in id types… It would
serve as an intersection table – storing ids – between all tables that
access email_template.

Liz

7

Thanks Fred, I checked it and it works there without problems. It seems
as
I’ll need to upgrade rails in order to handle this case.

Liz - it’s an interesting idea, thanks.

8

On Wednesday, June 24, 2015 at 3:54:56 PM UTC+3, Frederick C. wrote:

On Wednesday, June 24, 2015 at 3:21:39 PM UTC+3, simon2k wrote:

I’m not sure whether I should treat it as a rails bug, and that rails should
quote this integer, or not. I could look further into AR, if you feel, that this
case should be handled. Otherwise, I’ll be looking for a different solution for
this challenge.

Why is survey id a string column? I believe AR is casting as an integer because
the column being compared with is an integer (your primary key on the other
table). Not doing this cast has been at the root of security problems in the past
if my memory is correct.

I forgot to add - if you are thinking of filing a bug, check that this
occurs on current versions of rails (4.2.x) as the 3.2 branch only
receives severe security updates.

Fred