Welcome to all phpBB2 Refugees! 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. |
|
| Author |
Message |
Quiff Boy
Board Member
Joined: 27 Jun 2020
Posts: 4
|
 Posted: 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 
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" 
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 
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 
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: 152
Location: Germany
|
Posted: 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
|
Posted: 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: 611
Location: North West UK
|
Posted: 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
|
Posted: 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 |
|
|
|
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0265 seconds using 16 queries. (SQL 0.0019 Parse 0.0008 Other 0.0238) |
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com 
|
|
