FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » FUDforum » FUDforum Installation Issues » FUDForum 3.0 SQL errors with SQLite
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
FUDForum 3.0 SQL errors with SQLite [message #160707] Thu, 15 October 2009 08:57 Go to next message
dewaard is currently offline  dewaard   Netherlands
Messages: 6
Registered: November 2004
Location: The Netherlands
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user

After considering different alternatives, I selected FUDForum 3.0 for my site because you support SQLite. Because this approach is memory friendly for hosting this small forum on a VPS and doesn't force me to run a standalone process for the database.

However, I am getting SQL errors when users log in or register (and possibly elsewhere too, but I haven't tested that yet). Here is the error message:

Quote:
Server Version: 3.6.10<br />
[Referring Page] [removed]/index.php?t=register&amp;rid=0&amp;S=[removed]&amp;re g_coppa=0 <br />

?3002 ?1255535142?(/srv/www/[removed]/html/index.php:106<br />
/srv/www/[removed]/html/index.php:175<br />
/srv/www/[removed]/html/theme/default/register.php:263<br />
/srv/www/[removed]/html/theme/default/register.php:2190<br />
/srv/www/[removed]/html/index.php:1223<br />
) near ",": syntax error: HY000<br />
Query: INSERT INTO
fud30_users (
login,
alias,
passwd,
name,
email,
icq,
aim,
yahoo,
msnm,
jabber,
affero,
google,
skype,
twitter,
posts_ppg,
time_zone,
bday,
last_visit,
conf_key,
user_image,
join_date,
location,
theme,
occupation,
interests,
referer_id,
last_read,
sig,
home_page,
bio,
users_opt,
reg_ip,
topics_per_page,
flag_cc,
flag_country
) VALUES (
'[removed]',
'[removed]',
'[removed]',
'[removed]',
'[removed]',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0,
'Europe/Paris',
00000000,
1255535142,
'[removed]',
NULL,
1255535142,
'[removed]',
1,
'Self Employed',
NULL,
0,
1255535142,
NULL,
NULL,
NULL,
4226550,
,
40,
NULL,
NULL
)
<br />


It seems to me that comma with the empty value in "[...] 4226550, , 40 [...]" is to blame. The value that is supposed to be there is:
Quote:
".ip2long($reg_ip).",


but seems the result of the ip2long() function seems to be empty. I think, but haven't verified, that MySQL and PostgreSQL are probably more permissive with empty values than SQLite which is why this error hasn't popped up because I'm not sure if many people are using SQLite with FUDForum yet.

I have tried to fix this by adding quotes around the output of that function call, but this fix is only temporary as the templates keep getting overwritten somehow. I would be most obliged if someone could point me to the location of the files where I can permanently modify these templates (as the same problem is cropping up in some other places too) so I can fix this and supply a patch.
Re: FUDForum 3.0 SQL errors with SQLite [message #160708 is a reply to message #160707] Thu, 15 October 2009 16:12 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3634
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Are you using an Apache webserver? Is the system configured to use IPv4 or IPv6?

ip2long() will only return nothing if an invalid or v6 IP address was passed to it. Can you please check what the get_ip() function in core.inc returns?
Re: FUDForum 3.0 SQL errors with SQLite [message #160709 is a reply to message #160707] Thu, 15 October 2009 19:48 Go to previous messageGo to next message
dewaard is currently offline  dewaard   Netherlands
Messages: 6
Registered: November 2004
Location: The Netherlands
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user

Thanks a lot for your reply. I'm running php5 using FastCGI with a Lighttpd webserver (again, to conserve memory on my VPS). My system is using IPv4, but supports and recognizes IPv6 (if I run ifconfig I actually see both an IPv4 and IPv6 address for eth0). After reading your post I wrote this code to test some values:

Quote:
<?php
include("/[removed]/FUDforum/include/core.inc");

print var_dump(get_ip());
print "<br />";
print var_dump($_SERVER['HTTP_X_FORWARDED_FOR']);
print "<br />";
print var_dump($_SERVER['REMOTE_ADDR']);
print "<br />";
print var_dump(substr($_SERVER['REMOTE_ADDR'], 7));
print "<br />";
print var_dump(ip2long(get_ip()));
?>


With this output (the IP was my actual address, but changed it to 127.0.0.1 it for privacy reasons):

Quote:
string(20) "::ffff:127.0.0.1"
NULL
string(20) "::ffff:127.0.0.1"
string(13) "127.0.0.1"
bool(false)


Apparently, the remote address is some strange hybrid between IPv4 and IPv6. I brushed off my rusty PHP skills (Python user here, sorry) and created a quick and dirty fix (that works for me, so far), by changing the return line of get_ip to:

Quote:
return (isset($_SERVER['REMOTE_ADDR']) ? substr($_SERVER['REMOTE_ADDR'], 7) : '0.0.0.0');


Obviously, a clean solution would run regular expressions to check if it concerns a IPv4, IPv6 or this weird hybrid address. I would suggest a solution where the current mechanics are followed if the IP matches IPv4, but 0.0.0.0 is returned if it isn't (without checking for IPv6/hybrid). That will at least suppress errors like I encountered, without excessive performance trade offs that would occur when checking the value against IPv6 or the weird hybrid on my system.

IPv6 users or people who have the same prefix to the address my system has should probably convert the value of $_SERVER['REMOTE_ADDR'] to valid IPv4 centrally (maybe using a configuration option set automatically during installation?) if they want a different value from 0.0.0.0.

If you want, I'll gladly volunteer to prepare a patch to your specifications to resolve the issue.

[Updated on: Thu, 15 October 2009 19:51]

Report message to a moderator

Re: FUDForum 3.0 SQL errors with SQLite [message #160710 is a reply to message #160709] Thu, 15 October 2009 23:19 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3634
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Something like this should fix it:
Index: core.inc
===================================================================
RCS file: /forum21/install/forum_data/include/core.inc,v
retrieving revision 1.204
diff -u -r1.204 core.inc
--- core.inc    7 Oct 2009 15:19:24 -0000       1.204
+++ core.inc    16 Oct 2009 03:12:39 -0000
@@ -89,6 +89,7 @@
        if (isset($_SERVER['HTTP_X_FORWARDED_FOR'])) {
                $ip = strtok($_SERVER['HTTP_X_FORWARDED_FOR'], ',');
                do {
+                       $ip = str_replace('::ffff:', '', $ip);
                        if (($lip = ip2long($ip)) === -1) {
                                continue;
                        }
@@ -104,7 +105,10 @@
                        return long2ip($lip);
                } while (($ip = strtok(',')));
        }
-       return (isset($_SERVER['REMOTE_ADDR']) ? $_SERVER['REMOTE_ADDR'] : '0.0.0.0');
+
+       $ip = isset($_SERVER['HTTP_CLIENT_IP']) ? $_SERVER['HTTP_CLIENT_IP'] : $_SERVER['REMOTE_ADDR'];
+       $ip = str_replace('::ffff:', '', $ip);
+       return (!empty($ip) ? $ip : '0.0.0.0');
 }

 function ssn($str)


I would appreciate if you can test the above patch before we commit it.
Re: FUDForum 3.0 SQL errors with SQLite [message #160730 is a reply to message #160710] Sat, 17 October 2009 07:43 Go to previous messageGo to next message
dewaard is currently offline  dewaard   Netherlands
Messages: 6
Registered: November 2004
Location: The Netherlands
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user

I have tested your patch and it works for me. Still, I'm not sure about the str_replace line you added. The first doubt I have is how it would behave with an IPv6 address that contains the token you are replacing. It might break that. The second doubt is if it isn't a general performance loss for the majority of users (that are using normal IPv4) to perform this function here on every single call to get_ip().

One thing I'm not sure about: does FUDForum support IPv6 at all? If it doesn't it should probably give a warning during installation or upgrade. In any case, it would probably be best to do any IP address conversion centrally and only once. And only for the exception cases (IPv6/my system hybrids), in order to avoid a performance loss for the majority.

I'm sorry to be nitpicking here and maybe I'm wrong, because I haven't done any PHP development in years. My own installation works now, so that is great. But if there is anything further I can do to help regarding this issue I'm happy to do so.

Cheers, Filip
Re: FUDForum 3.0 SQL errors with SQLite [message #160731 is a reply to message #160730] Sat, 17 October 2009 08:02 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3634
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Unfortunately FUDforum cannot handle IPv6 addresses at this stage.

The "::ffff:" indicates an IPv4 Mapped IPv6 Address. This is a special type of IPv6 address that holds an embedded IPv4 address.

The provided patch will allow FUDforum to map these IPv6 addresses back to IPv4 compatible addresses. However, further work is still required to support non-mapped IPv6 addresses.

You are welcome to suggest further improvements or to develop patched to enable full IPv6 support. If I can somehow help you to to get started, please let me know.
Re: FUDForum 3.0 SQL errors with SQLite [message #160733 is a reply to message #160731] Sat, 17 October 2009 11:02 Go to previous message
dewaard is currently offline  dewaard   Netherlands
Messages: 6
Registered: November 2004
Location: The Netherlands
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user

Thanks a lot for the clarification.

I have little knowledge of IPv6, beyond what is left in my long term memory from the times I have casually read about it over the years. While IPv4 blocks might be running out, we are not quite there yet by a long stretch and I don't know of anyone using IPv6-only in production for server websites.

When I started doing serious programming little over ten years ago, I used PHP as my main language for web scripting. Parallel to the development of PHP 5 in 2003/2004 I started looking for a way out and eventually went for Python. While I continued maintaining legacy PHP code until the end of 2004 (and played around with PHP 5) that code was rewritten with a clean slate in Python.

So this incident is the first time I have touched PHP in over four years. If there was a viable, production-ready web forum application written in Python (or even Ruby) I would've used that, because I'm a lot more familiar with these languages and simply don't enjoy working with PHP that much.

This is by no means intended to bash PHP or argue its inferiority in any way (although it would not be my *personal* language of choice, applications like FUDForum clearly prove it is possible to write clean code in PHP). That, however, doesn't take away the fact that I don't feel confident in making larger changes to existing code without investing a lot of time in understanding the internals of FUDForum and rehashing my knowledge of PHP.

So while I'm happy to make small contributions that scratch my own itch I don't feel comfortable with making larger improvements to FUDforum (especially as I got lost in the FUDforum internals while trying to fix this issue in the first place, until you pointed me to the source of the issue).
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: GLOBALS.php not created on installation
Next Topic: Step 2 installation error PLEASE HELP!!!
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Jan 23 05:08:44 EST 2018

Total time taken to generate the page: 0.00802 seconds