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

[simon2k]

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

[Frederick Cheung]

On Wednesday, June 24, 2015 at 3:21:39 PM UTC+3, simon2k wrote:
> 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.
>
>

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

[simon2k]

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: https://gist.github.com/simon2k/5b4d4043d4b625984ca1

On Wednesday, 24 June 2015 14:57:00 UTC+2, Elizabeth McGurty wrote:

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

[simon2k]

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.

[Frederick Cheung]

On Wednesday, June 24, 2015 at 3:54:56 PM UTC+3, Frederick Cheung 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


> >
> > Regards,
> > Simon

[simon2k]

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.