Page 1 of 1
Another eyeZM issue: SQL-ERR dbFetchOne no result
Posted: Wed Jun 11, 2014 8:37 pm
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?
Re: Another eyeZM issue: SQL-ERR dbFetchOne no result
Posted: Thu Jun 12, 2014 3:12 pm
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 ...
Re: Another eyeZM issue: SQL-ERR dbFetchOne no result
Posted: Thu Jun 12, 2014 3:23 pm
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.
Re: Another eyeZM issue: SQL-ERR dbFetchOne no result
Posted: Thu Jun 12, 2014 5:14 pm
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?
Re: Another eyeZM issue: SQL-ERR dbFetchOne no result
Posted: Sat Jun 28, 2014 9:36 pm
by vlad2005
I have something similar like this but with dbFetchAll. Anyway, i don't find any escapeSql function in functions.php
Any advice?
Re: Another eyeZM issue: SQL-ERR dbFetchOne no result
Posted: Mon Jun 30, 2014 2:44 am
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.") ");
Re: Another eyeZM issue: SQL-ERR dbFetchOne no result
Posted: Mon Jul 07, 2014 2:08 pm
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.
Re: Another eyeZM issue: SQL-ERR dbFetchOne no result
Posted: Mon Jul 07, 2014 7:13 pm
by vlad2005
Ok, i found'it in xml directory. I will wait for full modifications!