Hi,

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!

CommentFileSizeAuthor
#13 PDO-Exception.txt2.6 KBRAWDESK
#3 field-sort-unknown-column-1920998-3.patch1.44 KBabhuman
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Jose Reyero’s picture

Jose Reyero CreditAttribution: Jose Reyero commented

See #1679646: Module throwing wrong query with 'node_access' but without 'nid' (Column not found (nid) in on clause...)

lonehorseend’s picture

lonehorseend CreditAttribution: lonehorseend commented

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

abhuman CreditAttribution: abhuman commented
FileSize
field-sort-unknown-column-1920998-3.patch1.44 KB

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

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

bkat CreditAttribution: bkat commented

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

mojameslittle’s picture

mojameslittle CreditAttribution: mojameslittle commented

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

vitalie CreditAttribution: vitalie commented

+1 Works indeed.

foredoc’s picture

foredoc CreditAttribution: foredoc commented

Hi,

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.

Thanks.

MorinLuc0’s picture

MorinLuc0 CreditAttribution: MorinLuc0 as a volunteer and at Coldfront Labs Inc. commented
Status: Reviewed & tested by the community » Needs work

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

czigor’s picture

czigor CreditAttribution: czigor commented
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

czigor CreditAttribution: czigor commented
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

spotzero CreditAttribution: spotzero at Coldfront Labs Inc. commented
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

RAWDESK CreditAttribution: RAWDESK for Colruyt Group Services commented
FileSize
PDO-Exception.txt2.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.
Thanks!

RedEight’s picture

RedEight CreditAttribution: RedEight at 95Visual commented

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

RedEight CreditAttribution: RedEight at 95Visual commented

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

bburg
he/him
Location Washington D.C.
CreditAttribution: bburg at Forum One commented

+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

antims CreditAttribution: antims commented

+1, thanks a lot.

geoo’s picture

geoo CreditAttribution: geoo commented

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

sinasalek’s picture

sinasalek CreditAttribution: sinasalek at Practicalidea commented
Status: Needs work » Reviewed & tested by the community

Patch #3 fixed the problem

ciss’s picture

ciss CreditAttribution: ciss at yousign GmbH commented
Status: Reviewed & tested by the community » Needs work

@sinasalek Comment #12 by @spotzero has not been addressed yet.

sinasalek’s picture

sinasalek CreditAttribution: sinasalek at Practicalidea commented

@ciss agreed, i just reviewed the patch again. it clearly has security implications, we can't just remove the access tag. the patch is helpful but can not be committed like this. sorry for changing the issue status

tenken’s picture

tenken CreditAttribution: tenken as a volunteer commented

I dont have a solution persay but this is also referenced in another thread and there may be other useful ideas here such as checking that field access are occuring instead of soley a node access check:
https://www.drupal.org/project/entityreference/issues/1935552#comment-11...