Laravel 4口若悬河地返回错误ID

I have 3 tables in my database:

我的数据库中有3个表:

Campaigns
活动
Users
用户
Companies
公司

One company may have some users. One user may have some campaigns. A user (with admin rights) can do some actions with any campaign that belongs to his company. So, I want to check whether he's doing these actions with his campaign or not (in the last case I return something like "access denied").

一个公司可能有一些用户。一个用户可能会有一些活动。一个用户(拥有管理权限)可以对任何属于他的公司的活动进行一些操作。因此，我想检查一下他是否在他的竞选活动中采取了这些行动(在上一个例子中，我返回类似“访问被拒绝”的内容)。

My condition

我的条件

Campaign::join('users', 'users.id', '=', 'campaigns.user_id')
        ->where('users.company_id', '=', Auth::user()->company->id)
        ->where('campaigns.id', '=', Input::get('id'))
        ->first();

So if I got unique campaign - it's ok, if I got null - something's wrong and I send "access denied" to user as he's dealing with other company campaign.

如果我有一个独特的活动，如果我得到null，就没问题了，有什么问题，我将"访问被拒绝"发送给用户，因为他正在处理其他公司的活动。

This code produces next query:

此代码生成下一个查询:

array(3) {
  ["query"]=>
  string(148) "select * from `campaigns` inner join `users` on `users`.`id` = `campaigns`.`user_id` where `users`.`company_id` = ? and `campaigns`.`id` = ? limit 1"
  ["bindings"]=>
  array(2) {
    [0]=>
    string(1) "2"
    [1]=>
    string(2) "13"
  }
  ["time"]=>
  float(0.42)
}

Using phpmyadmin I tried the same query and got a campaign with ID = 13. But when I was debugging my application I found out that

使用phpadmin myi尝试了相同的查询，得到了ID = 13的活动。但当我调试应用程序时，我发现了这一点

dd($campaign->id);

returns 8 instead. 8 also equals campaigns.user_id (The record has both campaigns.id and campaigns.user_id = 8).

返回8。8也等于运动。user_id(记录有两个活动。id和运动。user_id = 8)。

I can't figure out why it's happening. Even if something wrong with my SQL query (what I doubt as phpmyadmin returned right results), I got where condition campaigns.id = Input::get('id'), where Input::get('id') = 13. Why id is being changed?

我搞不懂为什么会这样。即使我的SQL查询出了问题(我怀疑作为phpmyadmin返回的结果是正确的)，我也得到了where condition campaign。id =输入::(id),输入::(id)= 13。为什么要更改id ?

Of course I can do this security check in two steps, like first get the campaign, then check $campaign->user->company->id = Auth::user()->company->id but just wondering ...

当然，我可以通过两个步骤来进行安全检查，比如首先获取活动，然后检查$campaign—>用户—>公司—>id = Auth: user()—>公司—>id，但是我想知道……

3 个解决方案

#1

If you run this query in phpMyAdmin you should probably be able to see that the result contains multiple columns by the name "id". When PHP parses the query result to a associative array or object, keys must be unique! If keys are colliding, the last column will be used!

如果您在phmypadmin中运行这个查询，那么您应该能够看到结果包含多个列，名称为“id”。当PHP将查询结果解析为关联数组或对象时，键必须是唯一的!如果键发生冲突，将使用最后一列!

Example:

例子:

SQL result:

SQL结果:

id    user_id    name    id    name    company_id
1     2          Camp1   2     Pelle   1

PHP result:

PHP结果:

array (size=1)
  0 => 
    object(stdClass)[131]
      public 'id' => string '2' (length=1)
      public 'user_id' => string '2' (length=1)
      public 'name' => string 'Pelle' (length=5)
      public 'company_id' => string '1' (length=1)

To solve this you could add a select clause to only select the campaign columns:

要解决这个问题，你可以添加一个select子句，只选择campaign列:

Campaign::select('campaigns.*')
    ->join('users', 'users.id', '=', 'campaigns.user_id')
    ->where('users.company_id', '=', Auth::user()->company->id)
    ->where('campaigns.id', '=', Input::get('id'))
    ->first();

#2

This seems like a limitation in the Eloquent library. Instead of using the last "id", it should be more proactive in searching for the "id" of the main table of the query. Or use "as" in the SQL statements.

这似乎是有说服力的图书馆的一个限制。与其使用最后的“id”，不如更主动地搜索查询的主表的“id”。或者在SQL语句中使用“as”。

Another solution would be to name each table's id field uniquely. e.g. user.user_id, campaigns.campaign_id. This would collide with the foreign keys in other tables, so name foreign keys as campaigns.user_fid.

另一种解决方案是对每个表的id字段进行唯一命名。如用户。user_id campaigns.campaign_id。这会与其他表中的外键发生冲突，因此将外键命名为campaign .user_fid。

#3

If you want control over the actual table who's duplicate column is used, just add the tables to an array in a specific order. The last one will be the ->id property.

如果您想要控制实际使用了重复列的表，只需按特定的顺序将表添加到数组中。最后一个是->id属性。

        ->select([
            'table1.*',
            'table2.*',
            'tags.*',
            'companies.*',
            'users.*',
        ])

This way you preserve all unique columns that are selected with the join. Should you want specific columns from a specific table you can use an AS:

这样，您就可以保存使用联接选择的所有惟一列。如果您想从特定的表中获取特定的列，可以使用AS:

        ->select([
            'table1.*',
            'table2.*',
            'tags.*',
            'companies.*',
            'users.*',
            'table1.id AS table1_id',
            'tags.name AS tag_name',
            'companies.name AS company_name',
        ])

#1