SQL error on views exposed filter autocomplete error handling [#571234]

Hi,

I've noticed that if a registered user attempts to login and fails with wrong login credentials I get this lovely error message:

* Sorry, unrecognized username or password. Have you forgotten your password?
* user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((n' at line 1 query: SELECT COUNT(*) FROM ( INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'tac_lite') OR (na.gid = 64 AND na.realm = 'tac_lite')))) count_alias in /home/doomrnet/public_html/sites/all/modules/views/includes/view.inc on line 729.
* user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((n' at line 1 query: INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'tac_lite') OR (na.gid = 64 AND na.realm = 'tac_lite'))) LIMIT 0, 6 in /home/doomrnet/public_html/sites/all/modules/views/includes/view.inc on line 755.

The error is coming out of the Views code as you can see.

Here's some background:

I have an image gallery that is a view displayed in a block called 'mini gallery'. The view shows thumbnails of images that are of a CCK image node type. The view has taxonomy term exposed so the user can select an image category. Furthermore I use tac_lite to ensure that certain images are only available to registered users. Each image has a term privacy and only images marked public are viewable by all. The view does not attempt to filter on the privacy term - I leave that to tac_lite. When the error occurs the block fails to display - no errors in the block - the block is just not there.

I have other similar views on the site that don't filter by taxonomy and these are fine, also when the user inputs the correct credentials or browses as a guest the view works fine.

I'm pretty sure that this used to work fine and I've only noticed the issue since updating the version of views - unfortunately I upgraded tac_lite at the same time. Clearly there's some sort of unresolved conflict between the two modules over what should be displayed on a failed login (?).

All modules are using latest stable versions - views 6.x-2.6, tac_lite 6.x -1.3, drupal 6.13

I accept that this is possibly a symptom and the real culprit might be tac_lite but your thoughts would be appreciated.

If you want to see this issue for yourself you can see it on my live site. The image gallery is called mini-gallery on the right panel. Enter any bogus credentials to login and you should see the problem - http://www.doomritual.com

Thanks

Comment	File	Size	Author
#31	views-0.patch	553 bytes	adr_p
#31
#30	views-0.patch	553 bytes	adr_p
#30
#20	views-executed.patch	787 bytes	dawehner
#20
#19	views-executed.patch	547 bytes	dawehner
#19
#17	views.access_0.patch	597 bytes	dawehner
#17
#16	views.access.patch	593 bytes	vitaminless
#16
#13	views.access.patch	880 bytes	rokr
#13
#14	views.access_1.patch	664 bytes	rokr
#14

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

merlinofchaos CreditAttribution: merlinofchaos commented 8 September 2009 at 13:11

It looks like an empty query is getting rewritten and then run, which is weird. It's hard to say why that might be happening. Do you know which view is causing this?

Comment #2

doomritual CreditAttribution: doomritual commented 8 September 2009 at 14:14

Yes I believe the view is one in a block (block header on site is Mini Gallery and view name as below is gallery3)- an export of the code is below if that helps.

If I disable this view then I no longer get the error. The only difference between this view and other image views on the same page is that this one filters on taxonomy and as I mention above I'm wondering if it's conflicting in some way with the authentication done by tac_lite which hides private images from guests.

Hope this helps:

$view = new view; $view->name = 'gallery3'; $view->description = 'mini gallery'; $view->tag = ''; $view->view_php = ''; $view->base_table = 'node'; $view->is_cacheable = FALSE; $view->api_version = 2; $view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */ $handler = $view->new_display('default', 'Defaults', 'default'); $handler->override_option('fields', array( 'field_picture_fid' => array( 'label' => '', 'link_to_node' => 0, 'label_type' => 'none', 'format' => 'imagefield__lightbox2__midsizesquarethumb__displayimage', 'multiple' => array( 'group' => TRUE, 'multiple_number' => '', 'multiple_from' => '', 'multiple_reversed' => FALSE, ), 'exclude' => 0, 'id' => 'field_picture_fid', 'table' => 'node_data_field_picture', 'field' => 'field_picture_fid', 'override' => array( 'button' => 'Override', ), 'relationship' => 'none', ), )); $handler->override_option('sorts', array( 'created' => array( 'order' => 'DESC', 'granularity' => 'second', 'id' => 'created', 'table' => 'node', 'field' => 'created', 'override' => array( 'button' => 'Override', ), 'relationship' => 'none', ), )); $handler->override_option('filters', array( 'status' => array( 'operator' => '=', 'value' => '1', 'group' => '0', 'exposed' => FALSE, 'expose' => array( 'operator' => FALSE, 'label' => '', ), 'id' => 'status', 'table' => 'node', 'field' => 'status', 'override' => array( 'button' => 'Override', ), 'relationship' => 'none', ), 'tid' => array( 'operator' => 'and', 'value' => array( '45' => '45', '43' => '43', '72' => '72', '46' => '46', '41' => '41', '85' => '85', '77' => '77', '50' => '50', '73' => '73', ), 'group' => '0', 'exposed' => TRUE, 'expose' => array( 'use_operator' => 0, 'operator' => 'tid_op', 'identifier' => 'tid', 'label' => 'Select filter:', 'optional' => 0, 'single' => 1, 'remember' => 1, 'reduce' => 1, ), 'type' => 'select', 'limit' => TRUE, 'vid' => '16', 'id' => 'tid', 'table' => 'term_node', 'field' => 'tid', 'hierarchy' => 0, 'relationship' => 'none', 'reduce_duplicates' => 1, 'override' => array( 'button' => 'Override', ), ), 'type' => array( 'operator' => 'in', 'value' => array( 'imagecck' => 'imagecck', ), 'group' => '0', 'exposed' => FALSE, 'expose' => array( 'operator' => FALSE, 'label' => '', ), 'id' => 'type', 'table' => 'node', 'field' => 'type', 'override' => array( 'button' => 'Override', ), 'relationship' => 'none', ), )); $handler->override_option('access', array( 'type' => 'none', )); $handler->override_option('cache', array( 'type' => 'none', )); $handler->override_option('title', 'gallery'); $handler->override_option('header_format', '3'); $handler->override_option('header_empty', 0); $handler->override_option('use_ajax', TRUE); $handler->override_option('items_per_page', 6); $handler->override_option('use_pager', 'mini'); $handler->override_option('style_plugin', 'grid'); $handler->override_option('style_options', array( 'grouping' => '', 'columns' => '2', 'alignment' => 'horizontal', )); $handler = $view->new_display('page', 'Page', 'page_1'); $handler->override_option('path', 'gallery/tortured-visions'); $handler->override_option('menu', array( 'type' => 'normal', 'title' => 'gallery', 'description' => '', 'weight' => '0', 'name' => 'navigation', )); $handler->override_option('tab_options', array( 'type' => 'none', 'title' => '', 'description' => '', 'weight' => 0, )); $handler = $view->new_display('block', 'Block', 'block_1'); $handler->override_option('block_description', ''); $handler->override_option('block_caching', -1);

Comment #3

AndyF CreditAttribution: AndyF commented 2 November 2009 at 06:57

I think I might have the same problem, though if so it's not tac_lite. I have a content type News article that has a node reference to a content type Image. Currently I'm using an autocomplete widget and had been having no problems. Today I created a quicktabs block that links to three displays of a single view. Basically the view just returns a small thumbnail, the ID, and the caption of Image nodes, as well as exposing the free tags (CCK field linked to Taxonomy using Content Taxonomy) as a filter. The idea is the authors use the view as an explorer to get the ID to enter in the node reference (Nodereference Explorer's been a bit moody with me, so this will be my backup!).

Everything was working fine, but when I mistyped the ID (I forget the square brackets) so that the system tried to interpret nid:21 as a title, I got a similar nasty SQL error from the same lines of views.inc. From reading the OP's problem, I tried removing various blocks, and sure enough the quicktabs block was the culprit (though the Views Slideshow block made no difference, and neither did another Quick Tabs block displaying article links). Note that with a correct title or [nid] there's no problem. Here's the output:



    * Main image: found no valid post with that title.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((n' at line 1 query: SELECT COUNT(*) FROM ( INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 2 AND na.realm = 'workflow_access') OR (na.gid = 3 AND na.realm = 'workflow_access') OR (na.gid = 4 AND na.realm = 'workflow_access_owner')))) count_alias in /var/www/html/sites/all/modules/views/includes/view.inc on line 729.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((n' at line 1 query: INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 2 AND na.realm = 'workflow_access') OR (na.gid = 3 AND na.realm = 'workflow_access') OR (na.gid = 4 AND na.realm = 'workflow_access_owner'))) LIMIT 0, 10 in /var/www/html/sites/all/modules/views/includes/view.inc on line 755.

CCK: 6.x-2.5
Content Taxonomy: 6.x-1.0-rc2
ImageField: 6.x-3.2
Quick Tabs: 6.x-2.0-rc3
Views: 6.x-2.6
Workflow: 6.x-1.3

Let me know if there's anything else I can provide.

Comment #4

smithwib CreditAttribution: smithwib commented 2 February 2010 at 22:51

I had been seeing this in a version 5 site that had a view in a block appearing on an anonymous page where otherwise pages were locked down -- I traced it to node_db_rewrite_sql and changed the first line to check to make sure that there is a query before building the SQL structure:

  if ($query && $primary_field == 'nid' && !node_access_view_all_nodes()) {

Not sure if that's something just in 5 or 6 as well that should be looked at, but that solved the immediate error.

Comment #5

frankie_2304 CreditAttribution: frankie_2304 commented 7 April 2010 at 09:29

works in D6!
i had the same error (on D 6.12) and this fixed my problem.
for more info: HERE you can find the info on where to put the code supplied by smithwib :)
thank you smithwib!

Comment #7

sdelbosc CreditAttribution: sdelbosc commented 15 April 2010 at 07:42

Just to track the issue...

Comment #8

doomritual CreditAttribution: doomritual commented 17 April 2010 at 18:46

hmmmm. I got quite excited reading this, but unfortunately Smithwib's solution doesn't work for me. I still get the same error as described in the first post. :(

Rather than waste time on this I've worked around the problem by changing my view. By not exposing the view taxonomy to allow user filtering I no longer get the sql error.

Comment #9

yang_yi_cn CreditAttribution: yang_yi_cn commented 7 May 2010 at 00:42

Version:

6.x-2.6

» 6.x-2.10

I believe it has some thing to do with the exposed filter, because I'm also getting this error when I have a view with exposed filter on the home page. I'm not using taxonomy filter but a CCK filter.

The workaround in the previous comments doesn't really solve the problem because it only remove the error message when the query is empty. Even if I did that, my view still disappears. This bug still persists in views 6.x-2.10.

I believe normally if there's no module calling db_rewrite_sql this bug is not triggered. But if you have a node access module and an exposed filter, this bug can be duplicated.

I'll try do more investigation on that.

Comment #10

yang_yi_cn CreditAttribution: yang_yi_cn commented 7 May 2010 at 01:11

Status:

Active

» Needs review

I have a patch for it now:

views.inc

  function build($display_id = NULL) {
...

    if ($this->display_handler->uses_exposed()) {
      $this->exposed_widgets = $this->render_exposed_form();
      if ((!($this->exposed_widgets) && form_set_error()) || !empty($this->build_info['abort'])) {  // this line changed
        $this->built = TRUE;
        return empty($this->build_info['fail']);
      }
    }
...

The problem is that form_set_error() will return all form errors, so in cases there are errors caused by other forms, such as failed login in this case, it will stop the view with a exposed form. I don't know if what I did is the best way to check if the error is raised by the exposed form, but it works for me.

Comment #11

doomritual CreditAttribution: doomritual commented 12 May 2010 at 19:31

yang_yi_cn, thank you.

I've tested your proposed patch and I can confirm that it fixes the bug reported in my initial post.

Clearly this fix needs to be incorporated into the module, but otherwise I'm happy for this issue to be closed as fixed.

Thanks again for your help

Comment #12

dawehner

German

CreditAttribution: dawehner commented 16 June 2010 at 19:36

Status:

Needs review

» Needs work

It would be cool to have a real patch file. This might be changed for 3.x, because of the changed exposed form

Comment #13

rokr CreditAttribution: rokr commented 22 June 2010 at 13:44

File	Size
views.access.patch	880 bytes

Changes in #10 works for us.

Steps to reproduce:

Install plain drupal.
Install and enable a nodeaccess module (nodeaccess or content_access), no further config required.
Install and enable views.
Build a simple view of nodes, expose filter "User: Name".
Save your view and try the view with a username which doesn't exist (e.g. "qwert")
View should result with "Unable to find user: qwert".
Create an other user B.
Login as user B and try the view with a non existant username.
View also results with "Unable to find user: qwert" AND:

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((n' at line 1 query: INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 3 AND na.realm = 'content_access_author') OR (na.gid = 2 AND na.realm = 'content_access_rid'))) LIMIT 0, 10 in /sites/all/modules/views/includes/view.inc on line 771.

Attached is a patch which contains changes made in #10.

Changing title hoping it represents the error better.

hth, Ronald

Comment #14

rokr CreditAttribution: rokr commented 22 June 2010 at 13:23

Title:	SQL Error on failed login attempt	» SQL error on views exposed filter autocomplete error handling
Version:	6.x-2.10	» 6.x-2.11

File	Size
views.access_1.patch	664 bytes

Attached is a better patch format. Sorry :-)

Comment #15

dawehner

German

CreditAttribution: dawehner commented 22 June 2010 at 14:00

+++ sites/all/modules/views/includes/view.inc	2010-06-18 22:01:36.000000000 +0200
@@ -587,7 +587,8 @@ class view extends views_db_object {
+      //if (form_set_error() || !empty($this->build_info['abort'])) {

Please remove the comment :) Noone needs it there.

+++ sites/all/modules/views/includes/view.inc	2010-06-18 22:01:36.000000000 +0200
@@ -587,7 +587,8 @@ class view extends views_db_object {
+      if ((!($this->exposed_widgets) && form_set_error()) || !empty($this->build_info['abort'])){

what about !empty exposed_widgets is not a boolean

Comment #16

vitaminless CreditAttribution: vitaminless commented 22 June 2010 at 15:08

File	Size
views.access.patch	593 bytes

Maybe you are right that $exposed_widgets is not a boolean, but as a long as it is not assigned it would eval with false, same thing when it is zero or null.

It works for me and rokr, but I am not sure what's the content of exposed_widgets, I guess it's the form of exposed filters.

Give me some hint so I can see what's wrong, this seems to be dirty fix anyway ....

Comment #17

dawehner

German

CreditAttribution: dawehner commented 22 June 2010 at 15:14

Status:

Needs work

» Needs review

File	Size
views.access_0.patch	597 bytes

The key reason for using non-boolean checks is that it's better readable code.

And empty is a bit faster then converting the string to a boolean first.
It would be cool if someone could check the logic

Comment #18

dawehner

German

CreditAttribution: dawehner commented 23 June 2010 at 05:52

My personal oppinion here is that $this->executed should be set.

Comment #19

dawehner

German

CreditAttribution: dawehner commented 23 June 2010 at 12:59

File	Size
views-executed.patch	547 bytes

Here is a patch.

Comment #20

dawehner

German

CreditAttribution: dawehner commented 23 June 2010 at 13:50

File	Size
views-executed.patch	787 bytes

This adds total_rows, as value for the view object.

Reasons are coming ....

Comment #21

vitaminless CreditAttribution: vitaminless commented 23 June 2010 at 16:26

So, the last patch was a request from me.

The view object contained no field which counts the number of rows, so I wanted dereine to add zero as default value here.
It's not nessesary, but when you write your view and you want to use this field, you don't have to check the exsistens of this field anymore.

Yay dereine!!!

Comment #22

mstef CreditAttribution: mstef commented 22 July 2010 at 17:46

Patch @ #20 isn't applying cleanly for me.

And I think my solution at http://drupal.org/node/682546#comment-3235932 needs to be brought into this.

Comment #23

dawehner

German

CreditAttribution: dawehner commented 22 July 2010 at 17:50

@mikesteff

I would mark your code as needs work

a) you should wrap everything, also the count_query db_rewrite_sql etc
b) you have to create a patch for 3.x, too
c) as you see in this issue, this bug can be fixed on another way. This is the really way to do it.

Rerolling the issue is trivial!

Comment #24

mstef CreditAttribution: mstef commented 22 July 2010 at 18:07

a) I noticed this, but count_query is never touched if !$query - but I agree, it should be..

You guys maintain the module so I trust your judgment. If you think my patch isn't necessary, then ignore it. When do you think we can get a fixed 2.12?

Also, not sure if it's been noted, this error doesn't happen if you're uid = 1. Took me three days to figure that out when trying to reproduce it..