Friday, January 02, 2009

PHPBB Convert poster_ip to real IP address in phpbb_posts table

Let's say you see an offending user on your website and you have their IP and you'd like to see if they're a registered poster on your PHPBB forum. The problem is that the poster_ip field in the phpbb_posts table is encoded. Here's an example to convert it back and search for an offending IP, let's say in this case it's

SELECT * from nuke_phpbb_posts where inet_ntoa(conv(poster_ip, 16, 10)) = '';

SELECT u.username from nuke_phpbb_posts p, nuke_phpbb_users u where inet_ntoa(conv(p.poster_ip, 16, 10)) = '' and p.poster_id = u.user_id limit 1;