SQL error?

[CarolSEL]

We're getting continuing error notifications when the site owner and other members attempt to log in. New registrants often cannot log in. The message is:

Database error in vBulletin 4.2.4:

Invalid SQL:
SELECT COUNT(*) AS num
FROM vb3_moderation AS moderation
INNER JOIN vb3_post AS post ON (moderation.primaryid = post.postid)
INNER JOIN vb3_thread AS thread ON (post.threadid = thread.threadid)
WHERE type = 'reply' AND forumid IN ( );
MySQL Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 5
Error Number : 1064

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

MySQL version 5.6.35
PHP version 5.5.38
Server Type: Linux
Web Server: Apachi (cgi-fcgi)

Host tells us it's from "poor coding" in the FractalizeR notifications plugin. When we turn that off, Mods don't get notifications. There aren't any coders among us, so I have 2 questions:

1) What is the syntax error that is producing the empty parentheses?
2) In which file will we find it?

Thanks for any help!!

[bridge2heyday]

vBulletin MySQL error messages give info about referrer .. this will be useful in your case .

[CarolSEL]

Script : http://www.catholicforum.com/forums/login.php?do=login
Referrer : http://www.catholicforum.com/forums/content.php

Site owner emailed me saying he got this message after turning off all plugins except notifications:

PHP Warning: implode(): Invalid arguments passed in ..../includes/class_bootstrap.php(1230) : eval()'d code on line 40

PHP Warning: mysqli_query(): (42000/1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 5 in ..../includes/class_core.php on line 1394

(By the way, he can log in from the ACP, but not the forum home page. Regular members obviously cannot do that.)

[bridge2heyday]

This error resulting from code on hook location notifications_list .. looking at the mentioned product I don't find anything that can produce this error .. I guess you have a modified version of this addon .. Can you post the code on this hook location here ?

[CarolSEL]

Hopefully this is what you're asking for (Showing Notifications):

function modForums($vbulletin) {
$userid = $vbulletin->userinfo['userid'];
$ismod_all = "SELECT childlist AS fid FROM ". TABLE_PREFIX ."forum
WHERE forumid IN ( SELECT forumid
FROM ". TABLE_PREFIX ."moderator
WHERE userid = ". $userid ." AND forumid != -1 )";
$fr_result = $vbulletin->db->query_read_slave($ismod_all);
while($fr_entry = $vbulletin->db->fetch_array($fr_result)) {
$fr_forums[] = $fr_entry['fid'];
}
return $fr_forums;
}

function isSuperMod ($vbulletin) {
if($vbulletin->userinfo['permissions']['adminpermissions'] & $vbulletin->bf_ugp_adminpermissions['ismoderator']) {
return true;
} else {
return false;
}
}

$fr_UserGroupsAllowed = explode(',', $vbulletin->options ['fr_items_to_moderate_showtogroups']);
$groupids = explode(',', $vbulletin->userinfo ['usergroupid'].','. $vbulletin->userinfo['membergroupids']);
if(array_intersect($fr_UserGroupsAllowed, $groupids)){

$m_forums = "";
if(!isSuperMod($vbulletin)){
$m_forums = "AND forumid IN ( ". implode(',', modForums( $vbulletin)) ." )";
}
$fr_itemsToTrack = explode(',', $vbulletin->options ['fr_itemstotrack']);

//All moderation items
$fr_modItems = array (
'thread' => array(
'link' => $vbulletin->options['bburl'] . '/moderation.php?do=viewthreads&type=moderated',
'total' => 0
),
'reply' => array(
'link' => $vbulletin->options['bburl'] . '/moderation.php?do=viewposts&type=moderated',
'total' => 0
),
'visitormessage' => array(
'link' => $vbulletin->options['bburl'] . '/' . trim($vbulletin->config['Misc']['modcpdir'], '/') . '/moderate.php?do=messages',
'total' => 0
),
'groupmessage' => array(
'link' => $vbulletin->options['bburl'] . '/moderation.php?do=viewgms&type=moderated',
'total' => 0
),
'picturecomment' => array(
'link' => $vbulletin->options['bburl'] . '/moderation.php?do=viewpcs&type=moderated',
'total' => 0
),
'event' => array(
'link' => $vbulletin->options['bburl'] . '/' . trim($vbulletin->config['Misc']['modcpdir'], '/') . '/moderate.php?do=events',
'total' => 0
),
'user' => array(
'link' => $vbulletin->options['bburl'] . '/' . trim($vbulletin->config['Misc']['admincpdir'], '/') . '/user.php?do=moderate',
'total' => 0
),
'attachments' => array(
'link' => $vbulletin->options['bburl'] . '/' . trim($vbulletin->config['Misc']['modcpdir'], '/') . '/moderate.php?do=attachments',
'total' => 0
),
'picture' => array(
'link' => $vbulletin->options['bburl'] . '/moderation.php?do=viewpics',
'total' => 0
)
);

//#################### Acquiring ####################
//reply - post
if(in_array('reply', $fr_itemsToTrack)) {
$query = "SELECT COUNT(*) AS num
FROM " . TABLE_PREFIX . "moderation AS moderation
INNER JOIN " . TABLE_PREFIX . "post AS post ON (moderation.primaryid = post.postid)
INNER JOIN " . TABLE_PREFIX . "thread AS thread ON (post.threadid = thread.threadid)
WHERE type = 'reply' ". $m_forums;
$fr_result = $vbulletin->db->query_first($query);
$fr_modItems['reply'] ['total'] = $fr_result['num'];
}

//thread
if(in_array('thread', $fr_itemsToTrack)) {
$query = "SELECT COUNT(*) AS num
FROM " . TABLE_PREFIX . "moderation AS moderation
INNER JOIN " . TABLE_PREFIX . "thread AS thread ON (moderation.primaryid = thread.threadid)
WHERE type = 'thread' ". $m_forums;
$fr_result = $vbulletin->db->query_first($query);
$fr_modItems['thread'] ['total'] = $fr_result['num'];
}

//visitormessage, groupmessage, picturecomment
if(count(array_intersect($fr_itemsToTrack, array('visitormessage', 'groupmessage', 'picturecomment')))>0) {
$query = "SELECT COUNT(type) AS total, type
FROM ". TABLE_PREFIX ."moderation
GROUP BY type";
$fr_result = $vbulletin->db->query_read($query);
while($fr_entry = $vbulletin->db->fetch_array($fr_result)) {
$fr_modItems[$fr_entry['type']] ['total'] = $fr_entry['total'];
}
}

//event
if(in_array('event', $fr_itemsToTrack)) {
$query = "SELECT COUNT(*) AS num
FROM ". TABLE_PREFIX ."event
WHERE visible=0";
$fr_result = $vbulletin->db->query_first($query);
$fr_modItems['event'] ['total'] = $fr_result['num'];
}

//user
if(in_array('user', $fr_itemsToTrack)) {
$query = "SELECT COUNT(*) AS num
FROM ". TABLE_PREFIX ."user
WHERE usergroupid = 4";
$fr_result = $vbulletin->db->query_first($query);
$fr_modItems['user'] ['total'] = $fr_result['num'];
}

//picture
if(in_array('picture', $fr_itemsToTrack)) {
$query = "SELECT COUNT(*) AS num
FROM ". TABLE_PREFIX ."picturecomment
WHERE state='moderation'";
$fr_result = $vbulletin->db->query_first($query);
$fr_modItems['picture'] ['total'] = $fr_result['num'];
}

//attachments
if(in_array('attachments', $fr_itemsToTrack)) {
$query = "SELECT COUNT(*) AS num
FROM ". TABLE_PREFIX ."attachment
WHERE state='moderation'";
$fr_result = $vbulletin->db->query_first($query);
$fr_modItems['attachments'] ['total'] = $fr_result['num'];
}

//#################### Placing into notifications area ####################
foreach($fr_modItems as $fr_modItemType => $fr_modItemValue) {
//Checking if we track item
if(!in_array($fr_modItemType, $fr_itemsToTrack)) {
continue;
}

//Checking zero values
//if(($fr_modItemValue['total'] == 0) and (!$vbulletin->options['fr_displayifzero'])) {
if($fr_modItemValue['total'] == 0) {
continue;
}

//Adding to notifications
$vbulletin->userinfo ['fr_items_to_moderate_' . $fr_modItemType] = $fr_modItemValue ['total'];
$notifications ['fr_items_to_moderate_' . $fr_modItemType] = array (
'phrase' => $vbphrase ['fr_items_to_moderate_' . $fr_modItemType],
'total' => $fr_modItemValue ['total'],
'link' => $fr_modItemValue ['link'] . $vbulletin->session->vars ['sessionurl_q'],
'order' => 10);
}
}

[Dave]

The problem is here:

PHP Code:

if(!isSuperMod($vbulletin)){
$m_forums = "AND forumid IN ( ". implode(',', modForums( $vbulletin)) ." )";
} 


the modForums function returns no records, therefore the IN clause will fail. Adding a count check after the isSuperMod check should fix this problem.

[CarolSEL]

Originally Posted by Dave

The problem is here:

PHP Code:

if(!isSuperMod($vbulletin)){
$m_forums = "AND forumid IN ( ". implode(',', modForums( $vbulletin)) ." )";
} 


the modForums function returns no records, therefore the IN clause will fail. Adding a count check after the isSuperMod check should fix this problem.

Thanks, Dave, but a question: How do I add a count check??

[Dave]

You can try changing it to

PHP Code:

if(!isSuperMod($vbulletin) && count(modForums($vbulletin)) > 0){
$m_forums = "AND forumid IN ( ". implode(',', modForums( $vbulletin)) ." )";
} 


[CarolSEL]

A million thanks, Dave! It worked for our site owner. Now we'll see if it helps the regular members log in, too!

[Stingray27]

Originally Posted by Dave

You can try changing it to

PHP Code:

if(!isSuperMod($vbulletin) && count(modForums($vbulletin)) > 0){
$m_forums = "AND forumid IN ( ". implode(',', modForums( $vbulletin)) ." )";
} 


A simpler and more common trick is simply to add a zero at the start of the IN() ;

PHP Code:

if(!isSuperMod($vbulletin)){
$m_forums = "AND forumid IN ( 0". implode(',', modForums( $vbulletin)) ." )";
}