phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area.

Could not insert data into users table


 
Search this topic... | Search phpBB2 Discussion... | Search Box
Register or Login to Post    Index » phpBB2 Discussion  Previous TopicPrint TopicNext Topic
Author Message
Quiff Boy
Board Member



Joined: 27 Jun 2020

Posts: 4



PostPosted: Sat Jun 27, 2020 3:34 pm 
Post subject: Could not insert data into users table

Hi

I have a phpbb2 forum that's been running since 2001 and is pretty heavily modded, and has been running just fine for years, hence me not wanting to touch it.

However, I think I've broken it icon_redface.gif

Like all forums we get spam registrations, but my mod team are pretty on the ball, and we have settled on a system where they mark a user account as a spammer, and i go in and delete the account once I've added an IP addresses or suspect email addresses to the ban list.

They deactivate the account and set the occupation field to "spammer" icon_lol.gif

It's very manual, but we've got it down to a fine art...

However, I recently had the bright idea of creating a shell script to delete all users with the occupation of spammer

Code:
DELETE FROM `phpbb_users` WHERE `user_occ` = 'spammer';


and a cron job to run that every day at 4am

I ran it on a local copy of the site and it all seemed fine, so set it up on live...

That was a few days ago, and now registration is broken icon_sad.gif

All new registration gets this error:

Quote:
Could not insert data into users table


The rest of the forum works just fine, but I'm guessing deleting users from the db isn't as simple as I thought and I've messed the db up icon_surprised.gif

It's been like that for a few days now so I can't really roll back to an older copy of the forum.

Any ideas?

--
EDIT: existing users can edit their profile, it just wont accept new registrations.

EXTRA EDIT: I remembered to turn DEBUG on and have a look, and it seems to be related to one of the mods I installed years ago for birthdays...

Code:
DEBUG MODE

SQL Error : 1366 Incorrect integer value: '' for column 'user_next_birthday_greeting' at row 1

INSERT INTO phpbb_users (user_id, username, user_regdate, user_password, user_email, user_icq, user_website, user_occ, user_from, user_interests, user_sig, user_sig_bbcode_uid, user_avatar, user_avatar_type, user_viewemail, user_aim, user_yim, user_msnm, user_attachsig, user_allowsmile, user_allowhtml, user_allowbbcode, user_allow_viewonline, user_notify, user_notify_pm, user_popup_pm, user_timezone, user_dateformat, user_lang, user_style, user_level, user_allow_pm, user_birthday, user_next_birthday_greeting, user_active, user_actkey) VALUES (7513, 'Just be testing', 1593272401, '22ca24e48ae0ba3b7b1f0862c9a21ac9', 'me@myemail.com', '', '', '', '', '', '', '', '', 0, 0, '', '', '', 0, 1, 0, 1, 1, 0, 1, 1, 1, 'D M d, Y g:i a', 'english', 1, 0, 1, '999999', '', 0, '4133aa34a')


What's weird is that I haven't touched anything to do with that.

I've checked and user_id column has

Code:
null = no
default = none
extra = AUTO_INCREMENT


which looks right, I think?

I tried pasting the query into phpmyadmin and it generated the same error.

user_next_birthday_greeting seems to be defined as

Code:
null = no
default = 0


It used to work but I have upgraded mysql recently, so wondering if that has created the issue?

Do I need to alter the user_next_birthday_greeting column to handle the newer version of mysql or something?
Back to top
StarWolf3000
Board Member



Joined: 10 Jun 2010

Posts: 151
Location: Germany


flag
PostPosted: Sun Jun 28, 2020 8:53 am 
Post subject: Re: Could not insert data into users table

The "issue" here is the stricter (but correct) handling of datatypes in the database, which definitely was caused by the upgrade of MySQL. You cannot have an empty string, if the field is defined as an integer type.

It's been a few years, since I've actually modded a phpBB 2.x, but I think this INSERT statement is found in includes/usercp_register.php. Inside this INSERT, replace the third-last parameter inside VALUES (which should be a '') with a 0. You can also attach a copy as .txt to your post and we will sort out the critical statement, if you like.
Back to top
Quiff Boy
Board Member



Joined: 27 Jun 2020

Posts: 4



PostPosted: Sun Jun 28, 2020 11:01 am 
Post subject: Re: Could not insert data into users table

Ah, gotcha! Thanks.

So this is the chunk of code that builds the INSERT:

Code:
         $sql = "INSERT INTO " . USERS_TABLE . "   (user_id, username, user_regdate, user_password, user_email, user_icq, user_website, user_occ, user_from, user_interests, user_sig, user_sig_bbcode_uid, user_avatar, user_avatar_type, user_viewemail, user_aim, user_yim, user_msnm, user_attachsig, user_allowsmile, user_allowhtml, user_allowbbcode, user_allow_viewonline, user_notify, user_notify_pm, user_popup_pm, user_timezone, user_dateformat, user_lang, user_style, user_level, user_allow_pm, user_birthday, user_next_birthday_greeting, user_active, user_actkey)
            VALUES ($user_id, '" . str_replace("\'", "''", $username) . "', " . time() . ", '" . str_replace("\'", "''", $new_password) . "', '" . str_replace("\'", "''", $email) . "', '" . str_replace("\'", "''", $icq) . "', '" . str_replace("\'", "''", $website) . "', '" . str_replace("\'", "''", $occupation) . "', '" . str_replace("\'", "''", $location) . "', '" . str_replace("\'", "''", $interests) . "', '" . str_replace("\'", "''", $signature) . "', '$signature_bbcode_uid', $avatar_sql, $viewemail, '" . str_replace("\'", "''", str_replace(' ', '+', $aim)) . "', '" . str_replace("\'", "''", $yim) . "', '" . str_replace("\'", "''", $msn) . "', $attachsig, $allowsmilies, $allowhtml, $allowbbcode, $allowviewonline, $notifyreply, $notifypm, $popup_pm, $user_timezone, '" . str_replace("\'", "''", $user_dateformat) . "', '" . str_replace("\'", "''", $user_lang) . "', $user_style, 0, 1, '$birthday', '$next_birthday_greeting', ";
         if ( $board_config['require_activation'] == USER_ACTIVATION_SELF || $board_config['require_activation'] == USER_ACTIVATION_ADMIN || $coppa )
         {
            $user_actkey = gen_rand_string(true);
            $key_len = 54 - (strlen($server_url));
            $key_len = ( $key_len > 6 ) ? $key_len : 6;
            $user_actkey = substr($user_actkey, 0, $key_len);
            $sql .= "0, '" . str_replace("\'", "''", $user_actkey) . "')";
         }
         else
         {
            $sql .= "1, '')";
         }


I guess I need to replace $next_birthday_greeting in this

Code:
            VALUES ($user_id, '" . str_replace("\'", "''", $username) . "', " . time() . ", '" . str_replace("\'", "''", $new_password) . "', '" . str_replace("\'", "''", $email) . "', '" . str_replace("\'", "''", $icq) . "', '" . str_replace("\'", "''", $website) . "', '" . str_replace("\'", "''", $occupation) . "', '" . str_replace("\'", "''", $location) . "', '" . str_replace("\'", "''", $interests) . "', '" . str_replace("\'", "''", $signature) . "', '$signature_bbcode_uid', $avatar_sql, $viewemail, '" . str_replace("\'", "''", str_replace(' ', '+', $aim)) . "', '" . str_replace("\'", "''", $yim) . "', '" . str_replace("\'", "''", $msn) . "', $attachsig, $allowsmilies, $allowhtml, $allowbbcode, $allowviewonline, $notifyreply, $notifypm, $popup_pm, $user_timezone, '" . str_replace("\'", "''", $user_dateformat) . "', '" . str_replace("\'", "''", $user_lang) . "', $user_style, 0, 1, '$birthday', '$next_birthday_greeting', ";


with a 0, eg:

Code:
            VALUES ($user_id, '" . str_replace("\'", "''", $username) . "', " . time() . ", '" . str_replace("\'", "''", $new_password) . "', '" . str_replace("\'", "''", $email) . "', '" . str_replace("\'", "''", $icq) . "', '" . str_replace("\'", "''", $website) . "', '" . str_replace("\'", "''", $occupation) . "', '" . str_replace("\'", "''", $location) . "', '" . str_replace("\'", "''", $interests) . "', '" . str_replace("\'", "''", $signature) . "', '$signature_bbcode_uid', $avatar_sql, $viewemail, '" . str_replace("\'", "''", str_replace(' ', '+', $aim)) . "', '" . str_replace("\'", "''", $yim) . "', '" . str_replace("\'", "''", $msn) . "', $attachsig, $allowsmilies, $allowhtml, $allowbbcode, $allowviewonline, $notifyreply, $notifypm, $popup_pm, $user_timezone, '" . str_replace("\'", "''", $user_dateformat) . "', '" . str_replace("\'", "''", $user_lang) . "', $user_style, 0, 1, '$birthday', '0', ";


as it doesn't seem to be setting $next_birthday_greeting correctly
Back to top
Jim_UK
Board Member



Joined: 19 Nov 2008

Posts: 606
Location: North West UK


flag
PostPosted: Sun Jun 28, 2020 11:57 am 
Post subject: Re: Could not insert data into users table

I know sod all about coding but did he not mean that you could not have a nil entry so it should be
'$birthday', '$next_birthday_greeting', '0';

Jim

_________________
The truth is out there.
Unfortunately they will not let you anywhere near it!
Back to top
Quiff Boy
Board Member



Joined: 27 Jun 2020

Posts: 4



PostPosted: Sun Jun 28, 2020 2:04 pm 
Post subject: Re: Could not insert data into users table

Oh, you might be right.

All those single quotes got me confused 😂
Back to top
Display posts from previous:   
Register or Login to Post    Index » phpBB2 Discussion  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0268 seconds using 16 queries. (SQL 0.0020 Parse 0.0008 Other 0.0240)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo