[phpesp-dev] [ phpesp-Bugs-2674394 ] Dashboard returns no surveys due to ambiguous column in SQL

[SourceForge.net <no...@so...>]

Bugs item #2674394, was opened at 2009-03-09 04:03
Message generated for change (Comment added) made by bishopb
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=108956&aid=2674394&group_id=8956

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL
Group: None
>Status: Closed
>Resolution: Fixed
>Priority: 7
Private: No
Submitted By: kswartz (kswartz)
Assigned to: bishop (bishopb)
Summary: Dashboard returns no surveys due to ambiguous column in SQL

Initial Comment:
I'm using PHP version 2.1.2 (which isn't in the Group box, by the way -- that should be fixed.)

This SQL is executed in survey_get_in_realm():

SELECT DISTINCT tblSurvey.id, tblSurvey.name, tblSurvey.title, tblSurvey.owner, tblSurvey.open_date, tblSurvey.close_date
FROM survey AS tblSurvey
JOIN access AS tblAccess
  ON tblAccess.survey_id=tblSurvey.id
WHERE (tblSurvey.realm = 'auto' OR tblAccess.realm = 'auto')
AND $where

However, $where is computed in survey_fetch_limiting_where as this:

(((status & 3)) AND (NOT (status & 4)) AND (owner='kswartz' OR realm IS NULL)) ORDER BY id DESC

When you combine the two, the SQL statement fails because of an ambiguous reference to realm, which appears in both of the tables being joined. As a result, when users log in to take a survey in my install, they never see anything listed.

(I'm using MySQL 5.0, but this error would happen with any database.)

The easiest fix is one line:
184c184
<      $where = survey_fetch_limiting_where();
---
>      $where = survey_fetch_limiting_where('status','owner','tblSurvey.realm');


----------------------------------------------------------------------

>Comment By: bishop (bishopb)
Date: 2009-03-23 06:17

Message:
Franky said> is this a valid fix? Then it should get in asap ...

A more general fix was called for: specifically, the user's realms needed
to be checked against both the survey's explicit realm and the realms
granted access to the survey via the access table.

I've committed my fix to trunk (rev 1108).

kswartz, I'd appreciate a ping back to confirm this fix works in your use
case.  Thanks, and thanks for helping improve phpESP!

----------------------------------------------------------------------

Comment By: Franky Van Liedekerke (liedekef)
Date: 2009-03-22 08:53

Message:
Bishop,

is this a valid fix? Then it should get in asap ...

Franky

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=108956&aid=2674394&group_id=8956