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

Hi guys,

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

https://gist.github.com/simon2k/5b4d4043d4b625984ca1

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

Hi guys,

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

sample rails app for bug · GitHub

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.

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

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

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.

> > 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

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.