[F] [pgsql] Report posts [C-StefanT]

[Michael S.]

If the board is set to notify the moderators via PM about reported posts you get two SQL errors using PostgreSQL.

Quote:SQL Error:
22P02 - ERROR: invalid input syntax for integer: ""
Query:
SELECT pmid FROM mybb_privatemessages WHERE folder='3' AND uid='' AND pmid='0'

You can find the query in inc/datahandlers/pm.php in line 511.

Quote:SQL Error:
22P02 - ERROR: invalid input syntax for integer: ""
Query:
INSERT INTO mybb_privatemessages (fromid,folder,subject,icon,message,dateline,status,includesig,smilieoff,receipt​,readtime,recipients,uid,toid) VALUES ('','1','Reported post at Forums','0','tester from Forums has reported this post: Test http://michael-laptop/~michael/mybb/showthread.php?tid=4&pid=5#pid5 The reason this user gave for reporting this post: Test This message has been sent to all moderators of this forum, or all administrators and super moderators if there are no moderators. Please check this post out as soon as possible.','1241369915','0','1','0','0','0','a:1:{s:2:"to";a:1:{i:0;s:1:"2";}}','2','2')

You can find the query in inc/datahandlers/pm.php in line 584.

[Ryan Gordon]

I think this is the same: http://community.mybboard.net/thread-49258.html

They're just showing two different results for the same issue depending the SQL software.

[Tomm M]

Not sure if this is the best way or not - I'm not too polished on pgSQL...

In ./inc/datahandlers/pm.php, around line 499, find:

PHP Code:

$this->pm_insert_data = array( 


Above it, add:

Code:

        if(!$pm['sender']['uid'])
        {
            $pm['sender']['uid'] = $mybb->user['uid'];
        }

That obviously sets it to the uid of the user reporting the post - setting it to 0 will set it to "MyBB Engine".

[Ryan Gordon]

No, that's a bad fix. It just needs to be intval'ed so that it shows up as 0 instead of a null value.

[Tomm M]

(05-13-2009 09:40 PM)Ryan Gordon Wrote:  No, that's a bad fix. It just needs to be intval'ed so that it shows up as 0 instead of a null value.

I tried that, and I just got a new SQL error (as said, not too polished on pgSQL errors!). I think there's a different bug that's on top of this one too which I think is the cause though. I'll revisit in the morning...

[Ryan Gordon]

Well then what is the new SQL error? I can simply tell you what to change if you tell me the new SQL error

[Tomm M]

Changes to ./inc/datahandlers/pm.php; around line 494...

PHP Code:

        $this->pm_insert_data = array(
            'fromid' => $pm['sender']['uid'],
            'folder' => $pm['folder'], 


Replace with:

PHP Code:

        $this->pm_insert_data = array(
            'fromid' => intval($pm['sender']['uid']),
            'folder' => $pm['folder'], 


Find (around line 510):

PHP Code:

$query = $db->simple_select("privatemessages", "pmid", "folder='3' AND uid='{$pm['sender']['uid']}' AND pmid='{$pm['pmid']}'"); 


Replace with:

PHP Code:

$query = $db->simple_select("privatemessages", "pmid", "folder='3' AND uid='".intval($pm['sender']['uid'])."' AND pmid='{$pm['pmid']}'"); 


Find (around line 630):

PHP Code:

            $this->pm_insert_data['uid'] = $pm['sender']['uid'];
            $this->pm_insert_data['folder'] = 2;
            $this->pm_insert_data['status'] = 1;
            $this->pm_insert_data['receipt'] = 0; 


Replace with:

PHP Code:

            $this->pm_insert_data['uid'] = intval($pm['sender']['uid']);
            $this->pm_insert_data['folder'] = 2;
            $this->pm_insert_data['status'] = 1;
            $this->pm_insert_data['receipt'] = 0; 


That will fix the 3 SQL errors that will appear when reporting a post.

Not sure if you want me to make a new bug report for this, at it is a *slightly* different bug.

If the setting is for PM or Email, and there is no set moderator for the forum, you receive this error:

Code:

SQL Error:
42883 - ERROR: function concat(text) does not exist LINE 4: LEFT JOIN mybb_usergroups g ON (((CONCAT(','|| u.addit... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Query is found in report.php:

Code:

                    $query = $db->query("
                        SELECT u.username, u.email, u.receivepms, u.uid
                        FROM ".TABLE_PREFIX."users u
                        LEFT JOIN ".TABLE_PREFIX."usergroups g ON (((CONCAT(','|| u.additionalgroups|| ',') LIKE CONCAT('%,'|| g.gid|| ',%')) OR u.usergroup = g.gid))
                        WHERE (g.cancp=1 OR g.issupermod=1)
                    ");

[Ryan Gordon]

(05-14-2009 08:04 AM)Tomm M Wrote:  Not sure if you want me to make a new bug report for this, at it is a *slightly* different bug.

If the setting is for PM or Email, and there is no set moderator for the forum, you receive this error:

Code:

SQL Error:
42883 - ERROR: function concat(text) does not exist LINE 4: LEFT JOIN mybb_usergroups g ON (((CONCAT(','|| u.addit... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Query is found in report.php:

Code:

                    $query = $db->query("
                        SELECT u.username, u.email, u.receivepms, u.uid
                        FROM ".TABLE_PREFIX."users u
                        LEFT JOIN ".TABLE_PREFIX."usergroups g ON (((CONCAT(','|| u.additionalgroups|| ',') LIKE CONCAT('%,'|| g.gid|| ',%')) OR u.usergroup = g.gid))
                        WHERE (g.cancp=1 OR g.issupermod=1)
                    ");

Yes, make a separate bug report. To fix that you need to use a switch statement and the proper form of the query for postgresql. I can fix it if you want.

[Tomm M]

The switch statement is there, but pgsql uses the same as sqlite so it won't work.

Made a new report and posted a fix - it should work, but if it's wrong then please feel free to correct it!

http://community.mybboard.net/thread-49838.html

[Tomm M]

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