Entity References is an amazingly good module that I use on all my sites.

I have encountered an error that is similar to some that have been previously reported but I have found out how to trigger it which I thought might be helpful.

When I apply a sort to the Simple (with optional filter by bundle) Entity Selection mode for a content type that uses Entity Reference. I get the following error when logged in as any user other than User 1 but when I remove the sort the error dissapears:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_data_field_assembly_serial_number0.nid' in 'where clause': SELECT field_data_field_assembly_serial_number0.entity_type AS entity_type, field_data_field_assembly_serial_number0.entity_id AS entity_id, field_data_field_assembly_serial_number0.revision_id AS revision_id, field_data_field_assembly_serial_number0.bundle AS bundle FROM {field_data_field_assembly_serial_number} field_data_field_assembly_serial_number0 WHERE (field_data_field_assembly_serial_number0.deleted = :db_condition_placeholder_0) AND (field_data_field_assembly_serial_number0.entity_type = :db_condition_placeholder_1) AND (field_data_field_assembly_serial_number0.bundle IN (:db_condition_placeholder_2)) AND(( ( EXISTS (SELECT na.nid AS nid FROM {node_access} na WHERE (( (na.gid = :db_condition_placeholder_3) AND (na.realm = :db_condition_placeholder_4) )OR( (na.gid = :db_condition_placeholder_5) AND (na.realm = :db_condition_placeholder_6) )OR( (na.gid = :db_condition_placeholder_7) AND (na.realm = :db_condition_placeholder_8) )OR( (na.gid = :db_condition_placeholder_9) AND (na.realm = :db_condition_placeholder_10) )OR( (na.gid = :db_condition_placeholder_11) AND (na.realm = :db_condition_placeholder_12) ))AND (na.grant_view >= :db_condition_placeholder_13) AND (field_data_field_assembly_serial_number0.entity_id = na.nid) )) AND (field_data_field_assembly_serial_number0.entity_type = :db_condition_placeholder_14) )OR (field_data_field_assembly_serial_number0.entity_type <> :db_condition_placeholder_15) )AND ( EXISTS (SELECT na.nid AS nid FROM {node_access} na WHERE (( (na.gid = :db_condition_placeholder_16) AND (na.realm = :db_condition_placeholder_17) )OR( (na.gid = :db_condition_placeholder_18) AND (na.realm = :db_condition_placeholder_19) )OR( (na.gid = :db_condition_placeholder_20) AND (na.realm = :db_condition_placeholder_21) )OR( (na.gid = :db_condition_placeholder_22) AND (na.realm = :db_condition_placeholder_23) )OR( (na.gid = :db_condition_placeholder_24) AND (na.realm = :db_condition_placeholder_25) ))AND (na.grant_view >= :db_condition_placeholder_26) AND (field_data_field_assembly_serial_number0.nid = na.nid) )) ORDER BY field_data_field_assembly_serial_number0.field_assembly_serial_number_value ASC; Array ( [:db_condition_placeholder_0] => 0 [:db_condition_placeholder_1] => node [:db_condition_placeholder_2] => assembly [:db_condition_placeholder_3] => 0 [:db_condition_placeholder_4] => all [:db_condition_placeholder_5] => 29 [:db_condition_placeholder_6] => content_access_author [:db_condition_placeholder_7] => 2 [:db_condition_placeholder_8] => content_access_rid [:db_condition_placeholder_9] => 4 [:db_condition_placeholder_10] => content_access_rid [:db_condition_placeholder_11] => 1 [:db_condition_placeholder_12] => view_unpublished_content [:db_condition_placeholder_13] => 1 [:db_condition_placeholder_14] => node [:db_condition_placeholder_15] => node [:db_condition_placeholder_16] => 0 [:db_condition_placeholder_17] => all [:db_condition_placeholder_18] => 29 [:db_condition_placeholder_19] => content_access_author [:db_condition_placeholder_20] => 2 [:db_condition_placeholder_21] => content_access_rid [:db_condition_placeholder_22] => 4 [:db_condition_placeholder_23] => content_access_rid [:db_condition_placeholder_24] => 1 [:db_condition_placeholder_25] => view_unpublished_content [:db_condition_placeholder_26] => 1 ) in field_sql_storage_field_storage_query() (line 582 of /var/www/html/mysite/modules/field/modules/field_sql_storage/field_sql_storage.module).

I will try using a Views Entity: Filter by entity reference view and see if that works as a workaround that allows me to sort the entities in the select list without causing the error and will post if that works.

Have a great day and thanks again for all your work on this excellent module!

Members fund testing for the Drupal project. Drupal Association Learn more


lonehorseend’s picture

I can confirm that the solution of creating an Entity Reference View and assigning it to the Entity Reference (instead of using the simple selection) works and removes this error.

abhuman’s picture

I believe the root cause of the issue is Entity Reference unconditionally adding a <type>_access tag to the query. It seems that in the case where sorting is by a field, this check is unnecessary as a field access check will be made. I've rolled a simple patch which does this, though I don't fully understand all the security implications.

bkat’s picture

Status: Active » Reviewed & tested by the community
bkat’s picture

This patch works great for fixing the exact some problem I was having on my system.

mojameslittle’s picture

abhuman's patch worked great for issue I was having as well. Thanks for sorting this out. This fix really should be incorporated into the module.

vitalie’s picture

+1 Works indeed.

foredoc’s picture


I have a very similar problem (the similarity resides in the error message), and the patch seems not working.

As described in: https://www.drupal.org/node/2759785, my problem originated from the content access module, and I am not sure if it has something to do with "Sort".

Since I have no idea what's happening, and this is the closest thread of discussion that I can find, I sincerely post my problems here for suggestions.


MorinLuc0’s picture

Status: Reviewed & tested by the community » Needs work

This will need some test cases before it can be committed.

czigor’s picture

Status: Needs work » Reviewed & tested by the community

Tests are nice without a doubt but looking at the last commits of the module it does not seem to be a requirement. To write tests for this would take writing a entityreference_node_access_test module inside entityreference/tests/modules. Let's see what a maintainer says.

The patch works in our case and is also straightforward. Marking this RTBC.

czigor’s picture

Priority: Normal » Major

Based on https://www.drupal.org/node/45111 this is major: "Trigger a PHP error through the user interface, but only under rare circumstances or affecting only a small percentage of all users, even if there is a workaround."

spotzero’s picture

Status: Reviewed & tested by the community » Needs work

I agree its a major, due to that PDO exception.

I agree this case isn't really a good candidate for automated test case. Since it's a dependency interaction PDO exception, it's an arduous task, and I'd forgo a test in exchange for a fix.

However, I wasn't able to reproduce the PDO exception. I'd like to be able to test that this patch fixes something before committing it.

I have some problems with this patch due to the security implications. This patch is literally "doesn't work with node access, disabling node access tags and hope it works out". I can't commit that without a reasonable explanation of why this not a security issue.

For commit:

  1. I need reproduction instructions.
  2. I need to have reasonable assurances I'm not going to be summoned to security.drupal.org for accepting this.
RAWDESK’s picture

2.6 KB

We had a similar Exception (see attached log message).
The patch #3 solved it.
Use cases were both creation as update of a basic detail page from a non admin user.
Entity References are used frequently within our installations, so we have not been able yet to trace down to the root cause of this issue.

RedEight’s picture

I've been having an issue with using drush to migrate into content types that have an entityreference field in the presence of the Taxonomy Access Control Lite module. Specifically, it tries to sort on a field in the referenced entity and it slaps the 0 on the end of the column name which causes it to PDO error. The patch in #3 fixes the issue. I am at a loss as to why it fixes the problem or what caused it in the first place. Is this opening a security vulnerability? Is the issue in the tac_lite module?

RedEight’s picture

RAWDESK, are your entity reference fields using the Simple selector with a sort on your "field_domain" of the referenced content?

For those of you encountering the issue, the workaround mentioned by lonehorseend in #2 definitely works. Create a view of the content you want to make available, have an entity reference display, and choose that instead of Simple (with optional bundle). If you need to apply field sorting you can do that directly from within the view.

bburg’s picture

+1, fixes this bug for me. Big, old, complicated site. Was running into this error on the user registration page. I couldn't begin to tell you what magic combination of modules and settings leads to this. Ran into the bug after applying security updates and I forgot to re-patch.

antims’s picture

+1, thanks a lot.

geoo’s picture

The #3 patch solved the problem for me too!
It should be included in main branch.