forumhome: PM Queries optimization!

[Xenon]

you have a real wierd board have ya?
it shows also 0 at my boards...

[NTLDR]

This is the code I have, with the origional variables in the templates and it works fine on 2.2.7 and 2.2.8 and displays 0 for me:

PHP Code:

$allpm=$DB_site->query_first("SELECT COUNT(*) AS messages,
                                 SUM(IF(dateline>$bbuserinfo[lastvisit] AND folderid=0,1,0)) AS newpm,
                                 SUM(IF(messageread=0 AND folderid=0,1,0)) AS unreadpm
                                 FROM privatemessage WHERE userid=$bbuserinfo[userid] $ignoreusers");
 
$newpm=$allpm['newpm'];
$unreadpm=$allpm['unreadpm']; 


[N9ne]

I have this:

$allpm=$DB_site->query_first("SELECT COUNT(*) AS messages,
SUM(IF(dateline>$bbuserinfo[lastvisit] AND folderid=0,1,0)) AS newpm,
SUM(IF(messageread=0 AND folderid=0,1,0)) AS unreadpm
FROM privatemessage WHERE userid=$bbuserinfo[userid] $ignoreusers");

$newpm['messages']=$allpm['newpm'];
$unreadpm['messages']=$allpm['unreadpm'];

Which looks the same! However it still doesn't show 0 for unread and new PMs! Why is it doing this? Is there anything in index.php that could cause this? [i have modified it a tiny bit, but not the PM bits :S]

[Xenon]

hmm, depends on what you've changed

try

PHP Code:

$newpm['messages']=intval($allpm['newpm']);
$unreadpm['messages']=intval($allpm['unreadpm']); 


instead of

PHP Code:

$newpm['messages']=$allpm['newpm'];
$unreadpm['messages']=$allpm['unreadpm']; 


[N9ne]

It works! Thankyou for putting up with me and my problems, keep up the good work

[Xenon]

you're welcome

i'm glad finally it works

btw. i think it's an php internal issue, can you tell me which php version you run?

[NTLDR]

Originally posted by Xenon

PHP Code:

$newpm['messages']=intval($allpm['newpm']);
$unreadpm['messages']=intval($allpm['unreadpm']); 


Xenon would changeing the code I have to what you have posted be a more secure way of doing it? I read a post somewhere from one of the dev's saying you should use intval for security reasons. Also there were lots of changes that use intval in 2.2.8.

[Xenon]

well this part isn't a security risk part, so you can leave your code or change it, no problem...

intval is more secure, because strings are always converted into an int value. that'll take effect on inputboxes whenever a user should enter an intval but enters a string this could produce security problems as i can think of.

hmm, is this understandable?

perhaps an example:

PHP Code:

$posts="5, password=MD5('hello')";
$DB-site->query("UPDATE user SET posts=$posts"); 


as you can see this would run this query:
UPDATE user SET posts=5,password=MD5('hello')

if you use intval this cannot happen:

PHP Code:

$posts="5, password=MD5('hello')";
$DB-site->query("UPDATE user SET posts=".intval($posts)); 


this would just run this query (as normally wanted):
UPDATE user SET posts=5

[NTLDR]

Thanks for the explaination Xenon I understand why and where intval should be used now

[Erwin]

Yes, I think that's why they did it - to avoid users entering in non-integer values and running the risk of corrupting the db or causing db errors.