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:
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]

The problem is here:
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?? :o

[Dave]

You can try changing it to
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]

You can try changing it to
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() ;

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

[CarolSEL]

The fix allowed our site owner to log in without errors. But we're still hearing from registered members that their login is being rejected, even after successfully changing their password. Is this related to the same thing? I'm getting no system notices about their failure. We've had a large influx of new members, but if they can't login, we're in trouble. Unless they contact us, we have no way of knowing how many are being refused.