MyBB Community Forums
[Security] Mysql stores the IP address as varbinary - Printable Version

+- MyBB Community Forums (https://community.mybb.com)
+-- Forum: 1.8 Support (https://community.mybb.com/forum-175.html)
+--- Forum: General Support (https://community.mybb.com/forum-176.html)
+--- Thread: [Security] Mysql stores the IP address as varbinary (/thread-227948.html)



Mysql stores the IP address as varbinary - serhatakgl - 2020-05-23

(In phpmyadmin) I am looking at the IP address of the commenters in the forum all IP addresses are stored in this style: 0x4333D26E
They gave a solution here but I couldn't understand:
https://community.mybb.com/thread-226350.html


I wanna see normal like a 93.XX.XXX.XX
What should I do?


RE: Mysql stores the IP address as varbinary - .m. - 2020-05-24

whose IP address actually you want to see ?

if poster's IP address then each post has link to see the poster's IP
if you want to check registered users' IP address then you can create a users view at admin panel


RE: Mysql stores the IP address as varbinary - Crazycat - 2020-05-24

Registration IP is in user profile, and you can use the "Find users" tool in ACP to search by IP (registration, last known IP or message IP)


RE: Mysql stores the IP address as varbinary - serhatakgl - 2020-05-24

(2020-05-24, 02:30 AM).m. Wrote: whose IP address actually you want to see ?

if poster's IP address then each post has link to see the poster's IP
if you want to check registered users' IP address then you can create a users view at admin panel

in our country we must have members' log information on the Sql. Therefore I wanna see members' ip adress. (for example i use mybb Symp. pm plugin and the ip adress as varbinary only on the phpmyadmin panel. If someone commits a crime through the pm panel, i have to prove it.  If I cannot prove, I will be the culprit.


RE: Mysql stores the IP address as varbinary - noyle - 2020-05-24

(2020-05-24, 12:09 PM)serhatakgl Wrote: in our country we must have members' log information on the Sql. Therefore I wanna see members' ip adress. (for example i use mybb Symp. pm plugin and the ip adress as varbinary only on the phpmyadmin panel. If someone commits a crime through the pm panel, i have to prove it.  If I cannot prove, I will be the culprit.

Do you mean PrivateMessages by PM? There's no direct way for forum administrators to view all private messages stored in the database, and of course the logged IPs of them. If you need to check some binary packed IP addresses, probably you need to write a small PHP script for that. I don't know if there's any plugin could help.

Nonetheless, there's a quicker way of using SQL and 3rd party tools. Take the privatemessages table for example:
  1. Run following SQL to get hexadecimal output of a packed IP address (XXX is the ID of a private message):
    SELECT HEX( ipaddress ) as ip FROM mybb_privatemessages  WHERE pmid = XXX;
  2. If the length of the string in the result (in the ip column) is 8, then it's an IPv4 address. Use tools like this or that to convert it to human readable format.
  3. Or if the length of the string in the result (in the ip column) is 32, then it's a readable IPv6 address representation that probably doesn't need to be converted anymore. You could simply add colon : after every 4 alphabets. For example, if you get a representation like 20010DN885A3000000008A2E03707334, the IPv6 address is 2001:0db8:85a3:0000:0000:8a2e:0370:7334.



RE: Mysql stores the IP address as varbinary - serhatakgl - 2020-05-24

(2020-05-24, 12:58 PM)noyle Wrote: Do you mean PrivateMessages by PM? There's no direct way for forum administrators to view all private messages stored in the database, and of course the logged IPs of them. If you need to check some binary packed IP addresses, probably you need to write a small PHP script for that. I don't know if there's any plugin could help.

Nonetheless, there's a quicker way of using SQL and 3rd party tools. Take the privatemessages table for example:
  1. Run following SQL to get hexadecimal output of a packed IP address (XXX is the ID of a private message):
    SELECT HEX( ipaddress ) as ip FROM mybb_privatemessages  WHERE pmid = XXX;
  2. If the length of the string in the result (in the ip column) is 8, then it's an IPv4 address. Use tools like this or that to convert it to human readable format.
  3. Or if the length of the string in the result (in the ip column) is 32, then it's an IPv6 address that doesn't need to be converted anymore.

I run this code on the Sql panel. And result length of the string is 8. Thank you bro It works! Finally, can we see when the post was send?  (time stamp) Because there are dateline: 1590154890  Status time: 1590154922  and Read time: 1590260618  on the phpmyadmin > db > mybb_privatemessages 

edit: thx I found it. This was Unix time and I converted it 


RE: Mysql stores the IP address as varbinary - noyle - 2020-05-25

(2020-05-24, 06:48 PM)serhatakgl Wrote: I run this code on the Sql panel. And result length of the string is 8. Thank you bro It works! Finally, can we see when the post was send?  (time stamp) Because there are dateline: 1590154890  Status time: 1590154922  and Read time: 1590260618  on the phpmyadmin > db > mybb_privatemessages 

edit: thx I found it. This was Unix time and I converted it 

Sorry I didn't make my post clear. If you get a string of length 32, then it's a readable representation of an IPv6 address. You could simply add colon : after every 4 alphabets. For example, if you get a representation like 20010DN885A3000000008A2E03707334, the IPv6 address is 2001:0db8:85a3:0000:0000:8a2e:0370:7334.

Yes, it's Unix timestamp.