Another eyeZM issue: SQL-ERR dbFetchOne no result

Forum for questions and support relating to the 1.27.x releases only.
Locked
windracer
Posts: 61
Joined: Sat Jul 23, 2011 1:15 am

Another eyeZM issue: SQL-ERR dbFetchOne no result

Post by windracer »

I just downloaded and re-compiled 1.27.1 from source on my Ubuntu 14.04 box to get libvlc support. Everything went fine except zmupdate.pl tried to upgrade 1.27.0 to 1.27 and ran into a SQL error (I had to run it with the --version=1.27.1 flag). Despite that, everything seemed to be working fine.

I had to re-apply the xml/skin.php fix for eyeZM to be able to connect on my iPhone, but now all of the thumbnails (and "live" views) in the app show:

SQL-ERR dbFetchOne no result, statement was ''

and I see the same thing in zoneminder.log:

Jun 11 16:28:12 servername web_php[29040]: FAT [SQL-ERR dbFetchOne no result, statement was '']

Any ideas on what I broke?
windracer
Posts: 61
Joined: Sat Jul 23, 2011 1:15 am

Re: Another eyeZM issue: SQL-ERR dbFetchOne no result

Post by windracer »

Looking at eyezm.log, this is the URL that is causing the error:

zm/?skin=xml&view=actions&action=feed&monitor=3&protoVer=2&scale=100&fps=3&width=160&height=120&vcodec=mjpeg

If I put that into my browser I get the same error:

SQL-ERR dbFetchOne no result, statement was ''

Trying to figure out what the actual SQL statement is now ...
windracer
Posts: 61
Joined: Sat Jul 23, 2011 1:15 am

Re: Another eyeZM issue: SQL-ERR dbFetchOne no result

Post by windracer »

Found where the error is coming from, line 124 of database.php:

Code: Select all

function dbFetchOne( $sql, $col=false, $params=NULL )
{
        $result = dbQuery( $sql, $params );
        if ( ! $result ) {
                Fatal( "SQL-ERR dbFetchOne no result, statement was '".$sql."'" . ( $params ? 'params: ' . join(',',$params) : '' ) );
                return false;
        }

    if ( $result && $dbRow = $result->fetch( PDO::FETCH_ASSOC ) )
        return( $col?$dbRow[$col]:$dbRow );
    return( false );
}
Now to figure out why $sql is empty. Sorry for all the posts, just documenting as I troubleshoot. ;)
windracer
Posts: 61
Joined: Sat Jul 23, 2011 1:15 am

Re: Another eyeZM issue: SQL-ERR dbFetchOne no result

Post by windracer »

Ok, got it! It's specific to the XML skin (of course).

I tracked the problem down to the SQL queries in the isMonitor and getMonitorDims functions in includes/functions.php for the skin, specifically, the results of escapeSql. It looks like the escapeSql function is clearing the entire $query variable, which then is passed to dbFetchOne and since it's an empty string, there's no query to be executed.

I put in some logXml debug calls to confirm this:

Code: Select all

[Thu, 12 Jun 2014 12:58:32 -0400] XML_LOG (NOTICE): WINDRACER-DEBUG
[Thu, 12 Jun 2014 12:58:32 -0400] XML_LOG (NOTICE): before escapeSql: select Id from Monitors where Id = 4
[Thu, 12 Jun 2014 12:58:32 -0400] XML_LOG (NOTICE): after escapeSql:
[Thu, 12 Jun 2014 12:58:32 -0400] XML_LOG (NOTICE): WINDRACER-DEBUG
[Thu, 12 Jun 2014 12:58:32 -0400] XML_LOG (NOTICE): before escapeSql: select Width,Height from Monitors where Id = 4
[Thu, 12 Jun 2014 12:58:32 -0400] XML_LOG (NOTICE): after escapeSql:
If I take out the escapeSql call, everything starts working normally again:

Code: Select all

/** Returns true when monitor exists */
function isMonitor($monitor)
{
        $query = "select Id from Monitors where Id = ".$monitor;
        //$res = dbFetchOne(escapeSql($query));
        $res = dbFetchOne($query);
        if ($res) return TRUE;
        logXml("Monitor ID ".$monitor." does not exist");
        return FALSE;
}
/** Returns the width and height of a monitor */
function getMonitorDims($monitor)
{
        $query = "select Width,Height from Monitors where Id = ".$monitor;
        //$res = dbFetchOne(escapeSql($query));
        $res = dbFetchOne($query);
        return $res;
}
So, the escapeSql function in includes/functions.php looks like this:

Code: Select all

/*
 * Escape an SQL string, with optional parameters for max. length
 */
function escapeSql($str, $maxlen = 0) {
        if (!$maxlen) $maxlen = 512;
        $string = substr($str, 0, $maxlen);
        return (get_magic_quotes_gpc())?mysql_real_escape_string(stripslashes($string)):mysql_real_escape_string($string);
}
I checked the maxlen stuff thinking maybe a 0 was sneaking in there, but it seems to be fine. So it's something with the return line that's completely truncating the $string.

tldr version: removing escapeSql call in isMonitor() and getMonitorDims() fixed my problem.

Thoughts?
vlad2005
Posts: 31
Joined: Fri Jun 27, 2014 6:45 pm

Re: Another eyeZM issue: SQL-ERR dbFetchOne no result

Post by vlad2005 »

I have something similar like this but with dbFetchAll. Anyway, i don't find any escapeSql function in functions.php
Any advice?
windracer
Posts: 61
Joined: Sat Jul 23, 2011 1:15 am

Re: Another eyeZM issue: SQL-ERR dbFetchOne no result

Post by windracer »

What version of ZM are you using? I'm running 1.27.1 and see escapeSql calls in the following places for the xml skin:

Code: Select all

22:41:14 /usr/local/zoneminder/skins/xml:$ grep -R escapeSql *
includes/functions.php:function escapeSql($str, $maxlen = 0) {
includes/functions.php: //$res = dbFetchOne(escapeSql($query));
includes/functions.php:        //logXml(escapeSql($query));
includes/functions.php: //$res = dbFetchOne(escapeSql($query));
includes/functions.php:        //logXml(escapeSql($query));
views/actions.php:              $event = dbFetchOne(escapeSql($eventsSql));
views/actions.php:              $event = dbFetchOne(escapeSql($eventsSql));
views/actions.php:                      $frameSql = escapeSql("select * from Frames as F where (F.EventId = ".$eid.") ");
User avatar
iconnor
Posts: 2881
Joined: Fri Oct 29, 2010 1:43 am
Location: Toronto
Contact:

Re: Another eyeZM issue: SQL-ERR dbFetchOne no result

Post by iconnor »

the xml skin is kinda crappy... and hasn't been fully updated to use PDO like the rest. I have just pushed a couple of changes dealing with these lines.
vlad2005
Posts: 31
Joined: Fri Jun 27, 2014 6:45 pm

Re: Another eyeZM issue: SQL-ERR dbFetchOne no result

Post by vlad2005 »

Ok, i found'it in xml directory. I will wait for full modifications!
Locked