Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Finding more than "own" records on searches

Quote Reply
Finding more than "own" records on searches
I've done a quick search for this problem surfacing before in DBManSQL but didn't see it on first glance. I run a database that allows folks to record the foods they eat, their weight, etc. Obviously, this is somthing that should be kept private. As such, I have the flags "$auth_view_own" and "$auth_modify_own" set to 1. I just got a report from an unhappy visitor today because when he did a keyword search, he got everyone's results along with his! I tried it out and get the same results. I have not modified the sub query in db.cgi. I did try adding to html_view_search the following:

Code:
if ($auth_view_own and !$per_admin) {
print qq|<input type=hidden name="UserID" value="$db_userid">|;
}
which does insert the "UserID=whoever" into the search string. However, that does not solve the problem. Oh, and as an interesting aside....if you choose "List All", it works correctly. It's only on searches that this problem occurs. And to add further weirdness...on the search, it only happens when using the keyword field. I have a modified html_record_form_search which is included below. The reason for the modification is because there are something like 80 fields in the database and the search needed to be simplified significantly to be usable.


Code:
##########################################################
## Custom Search ##
##########################################################
sub html_record_form_search {
#--------------------------------------------------------
#Search display for Members Database rather than the one with the script
#

my $sort_by = '<option value="---">';
foreach (@db_cols) {
$sort_by .= qq~<option value="$_">$_\n~;
}


print qq~

<STRONG><$font>Search Options:</font></STRONG></P>
<TABLE BORDER="0" WIDTH="100%">
<TR>
<TD WIDTH="25%" valign="top">
<P><B><$font>by Day:</font></B></P>

<SELECT NAME="Day">
<OPTION SELECTED> </OPTION>
<OPTION>Sunday</OPTION>
<OPTION>Monday</OPTION>
<OPTION>Tuesday</OPTION>
<OPTION>Thursday</OPTION>
<OPTION>Friday</OPTION>
<OPTION>Saturday</OPTION>
</SELECT>
</TD>
<TD WIDTH="75%" valign="top">
<P><B><$font>by Date:</font></B></P>
<P><INPUT TYPE="TEXT" NAME="Date" SIZE="12" MAXLENGTH="25">
<$font_small><b><select name="DateSelect">
<option value="" selected>Exact Date</option>
<option value="<">Older Than or Equal To</option>
<option value=">">Newer Than or Equal To</option>
</select>

<b>YYYY-MM-DD (2000-11-25)</b>
</font></P>
</TD>
</TR>
<td colspan="2"><P><B><$font>by Keyword: (will match against all fields)</font></b>
<INPUT TYPE="TEXT" NAME="keyword" SIZE="25" MAXLENGTH="255"></p></td></tr>
</TABLE>
<HR NOSHADE>
<table width="100%" border="0">
<tr><td width="50%">
<INPUT TYPE="CHECKBOX" NAME="ma"><$font_small> Match Any

<INPUT TYPE="CHECKBOX" NAME="ww"> Whole Words

</td>
<td>
<INPUT TYPE="TEXT" NAME="mh" SIZE="3" MAXLENGTH="3" VALUE="$db_max_hits"><$font_small> Maximum
Returned Hits</font>

<$font_small>
Sort By:
<SELECT NAME="sb">
$sort_by
</SELECT>

Sort Order: <SELECT NAME="so">
<OPTION VALUE="asc">Ascending </OPTION>
<OPTION VALUE="desc">Descending </OPTION>
</SELECT></font>
</td></tr></table>
<HR NOSHADE>
<P><B><$font>Search Tips:</B><BR>
<blockquote>- use '*' to match everything in a field)<BR>
- put a '<' or '>' at the beginning to to do range
searches.</font>

- <$font><b>Search for dates in the following format! (2000-11-25 or YYYY-MM-DD)</b></font></blockquote>
~;
}


What am I missing? This needs to be resolved quickly, as it is a confidentiality issue for my users.

Thank you!



Melanie
http://www.somemoorecats.com/ww/
http://www.okhima.org/
Quote Reply
Re: Finding more than "own" records on searches In reply to
I'm still working on finding a solution to this problem and haven't yet come up with one. It appears this may be a bug in the query sub in db.cgi. This only happens with keyword searches. Someone else posted a problem with getting both "validated" and "unvalidated" records when doing a keyword search. Seems like a similar problem.

This is the piece of code from sub_query that has to do with keyword searching:

Code:

# If this is a keyword search, we want to set every field in the database to the
# search term.
if ($in{'keyword'}) {
$in{'ma'} = "on"; $bool = 'OR';
foreach $column (@db_cols) {
next if ($db_lengths{$column} > 255); # Can't search on TEXT/BLOB fields.
next if ($in{'keyword'} !~ /^\d+$/ and $db_is_int{$column});
push (@search_fields, $column); # Search every column
$in{$column} = $in{'keyword'}; # Fill %in with keyword we are looking for.
}
}

What can I modify to make it skip the UserID field when populating all fields with the keyword? There's no need to search on the UserID in my database because the user will only be able to search his/her own records. I don't mind losing the ability to do a keyword search on UserID as an administrator because, frankly, I can't think of a time I've ever needed to do that.

I'd appreciate a nudge in the right direction. Thanks!

Melanie
http://www.somemoorecats.com/ww/
http://www.okhima.org/
Quote Reply
Re: Finding more than "own" records on searches In reply to
Holly cow! I just found this too! It also seems to ignore all other fields that are filled in, and only listens to the almighty keyword :)

I've notified Gossamer-Threads on this issue.

Quote Reply
Re: Finding more than "own" records on searches In reply to
Hi,

Ack, I think the bracketing changed in mysql as this used to work. However, a proper solution fix is to edit line 540 in db.cgi and change

$where .= qq! AND
$auth_user_field = $userid_q!;

to:

$where = qq! ($where) AND
$auth_user_field = $userid_q!;

Let me know if you have any problems with this.

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: Finding more than "own" records on searches In reply to
Thanks, Alex! That seems to have taken care of it! I appreciate it (as do my visitors).



Melanie
http://www.somemoorecats.com/ww/
http://www.okhima.org/
Quote Reply
Re: Finding more than "own" records on searches In reply to
It fixed the authorization issues, thank you. But that darn keyword field is still over-riding any other fields.

Is there any way to search on a field plus the keyword field, without the keyword over-powering everything?

P.S. Should we also apply your fix to sub get_record?



Quote Reply
Re: Finding more than "own" records on searches In reply to
GT, Any thoughts on this? Is there any way to search on a field plus the keyword field, without the keyword over-powering everything?