MyBB Community Forums

It occurs in many places, for example when sending mass mail to users.

MyBB tries to use CONCAT in SQL queries, but it is not defined in PostgreSQL. My suggestion is to define it while installing/upgrading with:

CREATE FUNCTION CONCAT(text,text)
RETURNS text
LANGUAGE SQL
AS $$ SELECT 'a'||'b' $$;

CREATE FUNCTION CONCAT(text,text,text)
RETURNS text
LANGUAGE SQL
AS $$ SELECT 'a'||'b'||'c' $$;

That's not how we do it - We use a php switch statement based on the dbms. Where are these errors happening?

But that's what has been done wrong - there are a lot of SQL queries called independently of db_pgsql.php definitions. For example concat is called in so many places outside of db_*.php files! So you can search for 'CONCAT' in all MyBB files and replace the queries with something database-dependent, or create the functions above.

The occurences of CONCAT:

petersa@boss ~/.homepage/forum $ grep -R 'CONCAT' *
forumdisplay.php:                               $query = $db->simple_select("modtools", 'tid, name', "(CONCAT(',',forums,',') LIKE '%,$fid,%' OR CONCAT(',',forums,',') LIKE '%,-1,%' OR forums='') AND type = 't'");
inc/functions_massmail.php:                                     $additional_sql .= " OR CONCAT(',',additionalgroups,',') LIKE '%,{$usergroup},%'";
inc/functions_search.php:                                                       WHERE INSTR(CONCAT(',',parentlist,','),',$forum,') > 0 AND active!=0 AND (ISNULL(p.fid) OR p.cansearch=1)
inc/functions_search.php:                                                       WHERE INSTR(CONCAT(',',parentlist,','),',$forum,') > 0 AND active!=0 AND (ISNULL(p.fid) OR p.cansearch=1)
inc/class_moderation.php:                                               $additional_groups .= " OR CONCAT(',',u.additionalgroups,',') LIKE ',{$group['gid']},'";
lalal/modules/forum/management.php:                                             $query = $db->simple_select("forums", "fid", "CONCAT(',',parentlist,',') LIKE '%,$fid,%'");
lalal/modules/forum/management.php:                             $query = $db->simple_select("forums", "*", "CONCAT(',', parentlist, ',') LIKE '%,$fid,%'");
lalal/modules/forum/management.php:                             $db->delete_query("forums", "CONCAT(',',parentlist,',') LIKE '%,$fid,%'");
lalal/modules/user/users.php:                                   $additional_sql .= "OR CONCAT(',',additionalgroups,',') LIKE '%,{$usergroup},%'";
lalal/modules/user/admin_permissions.php:                               $secondary_group_list .= " OR CONCAT(',', u.additionalgroups,',') LIKE '%,{$gid},%'";
lalal/modules/user/groups.php:                          $query = $db->simple_select("users", "uid", "CONCAT(',',additionalgroups,',') LIKE '%,{$usergroup['gid']},%'");
lalal/modules/user/groups.php:                          LEFT JOIN ".TABLE_PREFIX."usergroups g ON (CONCAT(',', u.additionalgroups, ',') LIKE CONCAT('%,', g.gid, ',%'))
lalal/error.log:LINE 1: ...2)  OR ','||additionalgroups||',' LIKE '%,2,%' OR CONCAT(','...
lalal/error.log:Query: SELECT COUNT(uid) AS num FROM mybb_users u WHERE u.allownotices=1 AND (u.usergroup IN (2)  OR ','||additionalgroups||',' LIKE '%,2,%'OR CONCAT(',',additionalgroups,',') LIKE '%,2,%')</message>
managegroup.php:                        $query = $db->simple_select("users", "*", "CONCAT(',',additionalgroups,',') LIKE '%,".$mybb->input['gid'].",%' OR usergroup='".$mybb->input['gid']."'", array('order_by' => 'username'));
member.php:                                     $query = $db->simple_select("forums", "fid", "INSTR(CONCAT(',',parentlist,','),',".intval($mybb->input['fid']).",') > 0");
report.php:                                             LEFT JOIN ".TABLE_PREFIX."usergroups g ON (((CONCAT(','|| u.additionalgroups|| ',') LIKE CONCAT('%,'|| g.gid|| ',%')) OR u.usergroup = g.gid))
report.php:                                             LEFT JOIN ".TABLE_PREFIX."usergroups g ON (((CONCAT(',', u.additionalgroups, ',') LIKE CONCAT('%,', g.gid, ',%')) OR u.usergroup = g.gid))
search.php:                                     $query = $db->simple_select("modtools", "tid, name", "type='t' AND (CONCAT(',',forums,',') LIKE '%,-1,%' OR forums='')");
search.php:                                     $query = $db->simple_select("modtools", "tid, name, type", "type='p' AND (CONCAT(',',forums,',') LIKE '%,-1,%' OR forums='')");
showthread.php:                         $query = $db->simple_select("modtools", "tid, name, type", "CONCAT(',',forums,',') LIKE '%,$fid,%' OR CONCAT(',',forums,',') LIKE '%,-1,%' OR forums=''");
usercp.php:                             LEFT JOIN ".TABLE_PREFIX."users u ON(((CONCAT(',', u.additionalgroups, ',') LIKE CONCAT('%,', g.gid, ',%')) OR u.usergroup = g.gid))

If you looked most of those do have the proper switches. It's only in a few rare cases where there may be a programming mistake or the like

Ok, I understand.

My bug comes from inc/functions_massmail.php:

switch($db->type)
{
  case "pgsql":
  case "sqlite3":
  case "sqlite2":
    $additional_sql .= " OR ','||additionalgroups||',' LIKE '%,{$usergroup},%'";
  default:
    $additional_sql .= " OR CONCAT(',',additionalgroups,',') LIKE '%,{$usergroup},%'";
}

I suppose there should be:

switch($db->type)
{
  case "pgsql":
  case "sqlite3":
  case "sqlite2":
    $additional_sql .= " OR ','||additionalgroups||',' LIKE '%,{$usergroup},%'";
    break;
  default:
    $additional_sql .= " OR CONCAT(',',additionalgroups,',') LIKE '%,{$usergroup},%'";
}

It's common to forget a break in switch
No other problems with concat.

Thank you for your bug report.

This bug has been fixed in our internal code repository. Please note that the problem will not be fixed here until these forums are updated.

With regards,
MyBB Group