Adhoc queries work but not stored queries - quoting issue

[akarjp]

Hi folks,

Happy ZoneMinder user here, delurking for the first time. My problem is
that certain types of filters work just fine in adhoc mode, but when they are stored. I see the following in the log:

zmfilter[7774]: ERR [Can't execute filter 'select E.Id,E.MonitorId,M.Name as Moni
torName,M.DefaultRate,M.DefaultScale,E.Name,E.Cause,E.Notes,E.StartTime,unix_timestamp(E.StartTime) as
Time,E.Length,E.Frames,E.AlarmFrames,E.TotScore,E.AvgScore,E.MaxScore,E.Archived,E.Videoed,E.Uploaded
,E.Emailed,E.Messaged,E.Executed from Events as E inner join Monitors as M on M.Id = E.MonitorId where
not isnull(E.EndTime) and ( M.Name = 'Basement' and E.Cause = Motion ) and ( E.Emailed = 0 ) order by
E.StartTime desc limit 0, 10', ignoring: Unknown column 'Motion' in 'where clause']

I believe E.Cause = Motion
should be E.Cause = 'Motion'

Thanks,
Bob

[akarjp]

I forgot to mention that I am using version 1.22.2

[jameswilson]

Welcome to the unlurked!?!
Glad to see you. I beleive there might be some sql string error but im afraid the main man is off lline fo a bit taking a well deserved holiday. Maybe Corey can shed some light but im afraid i cant, I agrre with your diagnosis however

[akarjp]

Well I dusted off the old perl skills and read the DBI docs. and came up with with what I think is a fix for this issue. Warning: this has not been extensivly tested. Use at your own risk! I changed one line in zmfilter.pl. Below is a context diff of the change:

Code: Select all

*** zmfilter.pl.orig    2006-05-25 10:02:15.000000000 -0400
--- zmfilter.pl 2006-06-03 12:16:04.000000000 -0400
***************
*** 287,292 ****
--- 287,293 ----
                                }
  
                                ( my $stripped_value = $value ) =~ s/^["\']+?(.+)["\']+?$/$1/;
+ 
                                foreach my $temp_value ( split( '/["\'\s]*?,["\'\s]*?/', $stripped_value ) )
                                {
                                        if ( $filter_terms{$attr_name} =~ /^Monitor/ )
***************
*** 335,341 ****
                                        }
                                        else
                                        {
!                                               $value = $temp_value;
                                        }
                                        push( @value_list, $value );
                                }
--- 336,342 ----
                                        }
                                        else
                                        {
!                                               $value = $dbh->quote($temp_value);
                                        }
                                        push( @value_list, $value );
                                }

Funny thing, as soon as I fixed this ZoneMinder immediately did just what I asked it to and sent out 350+ emails.

[zoneminder]

Thanks for posting this. I will fix it and put it in the main release.