Dragonfly CMS v9 ⇒ CPG-BB (forum) :: Archives ⇒ sql error and problem with poster_ip in _bbposts. :: Archived ⇒ Community Forums ⇒ CPG Dragonfly™ CMS
Forum Index Modules & Blocks CPG-BB (forum) ⇒ Archives

Archived ⇒ sql error and problem with poster_ip in _bbposts.


On /index.php?name=Forums&file=modcp&mode=ip&p=42963&t=5478
While executing query "SELECT u.user_id, u.username, COUNT(*) as postings
FROM nuke_users u, nuke_bbposts p
WHERE p.poster_id = u.user_id
AND p.poster_ip = 'G'
/'
GROUP BY u.user_id, u.username
ORDER BY postings DESC"

the following error occured: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/'
GROUP BY u.user_id, u.username
ORDER BY postings DESC' at line 5

In: (edited)/modules/Forums/modcp.php on line: 664

Error didn't show up in the first post

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Linux/2.2.9/5.0.45/5.2.6/9.2.1


The other part of the problem is that the poster_ips in the _bbposts table are garbled. Some of them have boxes with letters and others look like this J!rO/,BD|, Ѩ

This is an upgrade from php-nuke 7.6.

Is this a collation problem?

Edit: After looking through some of the databases that store the users IP address most of them are this way. anyone have any idea what the problem is?

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Linux/2.2.9/5.0.45/5.2.6/9.2.1

Last edited by Dreadan on Sun Jun 10, 2007 12:35 am; edited 1 time in total


I found part of the problem. The way the Ip got stored it had an apostrophe in it. I deleted the apostrophe and then it I could look up the users IP from the forum post. Are the examples above how the ip should look in the _bbposts table?

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Linux/2.2.9/5.0.45/5.2.6/9.2.1


all theses views and no one has any input?

Confused Sad

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Linux/2.2.9/5.0.45/5.2.6/9.2.1


this is happening when you try to see the ip address from old posts (posts made before you switched to dragon fly)? or with all posts?

www.greenday2k.net

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):


Sorry this response took so long. This is only happening with new posts. The only posts that are giving an error so far are from one poster who's ip address through the database is stored with an apostrophe. Are the ip address encoded in some of the databases? They just look really weird.

I'll include a picture of part of the database. Maybe that will help.

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Linux/2.2.9/5.0.45/5.2.6/9.2.1


The screenshot is about what they should look like. You can see the functions where they're converted in includes/functions/inet.php

Diagon Alley - Top Design

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Linux/1.3.37/4.1.21-standard/4.4.4/9.1.1


Why do I get an error with the one with the apostrophe though? If i go in and edit it out then it works fine.

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Linux/2.2.9/5.0.45/5.2.6/9.2.1


I was looking at that, I think it's a bug. I have some with apostrophes too and can reproduce the error.

The reason is because MySQL parses the apostrophe as the end of the string, so if you're trying to select the record where poster_ip = 'R'Ύ/' then it hits the second apostrophe and thinks you just want poster_ip = 'R'. Then it gets to Ύ/' and says OOPS GARBAGE. So if this string is going to have apostrophes they need to be escaped or something.

Diagon Alley - Top Design

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Linux/1.3.37/4.1.21-standard/4.4.4/9.1.1


That makes sense. I was worried that something was messed up and I would end up having problems later.

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Linux/2.2.9/5.0.45/5.2.6/9.2.1


Out of curiosity if you run this select statement in phpMyAdmin:

SELECT * FROM nuke_bbposts WHERE poster_id LIKE "%'%";

How many records do you have with apostrophes total?

I had 122 out of 125,000 and change, or less than 0.1%.

Diagon Alley - Top Design

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Linux/1.3.37/4.1.21-standard/4.4.4/9.1.1


I'm not sure why but that query doesn't return anything. I believe I only have the one user who has an apostrophe there. This is a convert from phpnuke that I recently did so theres only a couple hundred posts since I changed it. I tried just searching and I only have 3 results all from the same user.

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Linux/2.2.9/5.0.45/5.2.6/9.2.1


We have the same error in our forums - just from one poster (sadly someone who tries to fool around with us). I think it comes from the convert function and a special "constellation" of the ip address.
Is there a fix/workaround out? I dont think that deleting the apostrophe will solve the problem. Maybe you can escape the field better?

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Linux/Apache 2/MySQL 4.1.21-standard/PHP 4.4.2/CMS 9.1.2.1


Your forums are ForumsPro - you should seek assistance there.

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Ubuntu/Apache 2.2.22/MySQL 5.6.34/PHP 7.1.22/DragonFly 10.0.48.9418


I dont think that the issue is related to forumspro - i thought this first and opened a thread in their forums.
But if i get it correct, the ip address is encoded and decoded by the dragonfly cms, not by the forum.
As mentioned before by sarah
includes/functions/inet.php seems to be the library that causes the "problem". And this is part of dragonfly, not forumspro.

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Linux/Apache 2/MySQL 4.1.21-standard/PHP 4.4.2/CMS 9.1.2.1

All times are UTC