- Posts: 4
- Thank you received: 0
- K 3.0 General Questions
- School Website: Adding topics (w/ attachments) via MySQL table merge.
Kunena 5.1.8 Released - Security Release (30 Dec 2018)
The Kunena team has announce the arrival of Kunena 5.1.8 [K 5.1.8] which is now available for download as a native Joomla extension for J! 3.9.x. This version addresses most of the issues that were discovered in K 5.1 and issues discovered during the development stages of K 5.1. This is a Security release. Fixes 3 XSS security issues.
Question School Website: Adding topics (w/ attachments) via MySQL table merge.
I’ve been tasked with managing the student website at my school which has been active for 3 years. Kunena is used primarily to share attachments (outlines, etc). I’m class of 2017, and there are two classes before me. The first two years (2015, 2016) were active participants on the forum, thus there are a few thousand attachments. Sometime before my class commenced the website/server pair became incompatible, I’m told, due to a php upgrade on the server. As a result my class used a Facebook group to share outlines.
Disclaimer: I'm a complete novice, so what I've done has been learned within the last few days.
# First Steps:
I began from scratch, and successfully upgraded J!2.5 to J!3.3 with subsequent migration of Kunena (thanks to these Forums). I was now left with all the documents on the Facebook group. I created a Dev account at Facebook, and using their Graph API exported a list of all the files. The list contained a URL for the file, username, message, date and time. Using grep I massaged the list into html and then used a Firefox extension to download all files.
Using the tables jos_kunena_topics, jos_kunena_messages, jos_kunena_messages_text and jos_kunena_attachemnts as templates I created an corresponding new excel document.
For “id” I choose an arbitrary number above the greatest number in the original and matched this with “thread” in kunena_messages.
For “category_id” I created a new category in Kunena to which all of these posts will belong.
“subject” = attachment name w/out extension
“icon_id”, “locked”, “hold”, “ordering” = 0
“posts”, “hits”, “attachments” = 1
“poll_id” = 0
“moved_id” = 1
“first_post_id” = “id” in kunena_messages (again, arbitrary yet above greatest)
“first_post_time” = unix time stamp (converted from date/time in Group Files)
“first_post_userid” = Joomla User ID
“first_post_message” = Facebook group message (a period used for posts with no messages)
“first_post_guest_name” = Joomla Username matched with Joomla User ID
“last_post_…” = same as “first_post_…”
“params” = nothing
“id” = matched with kunena_topics “first_post_id”
“parent” = 0
“thread” = matched with kunena_topics “id”
“catid” = same as kunena_topics “category_id”
“name” & “userid” = same as in kunena_topics
“email” = blank
“subject” & “time” = same as in kunena_topics
“ip” = used random ip, but the same for all entries
“topic_emoticon”, “locked”, “hold”, “ordering”, “hits”, “moved” = 0
“modified_by”, “modified_time”, “modified_reason” = NULL
“mesid” = kunena_messages “id” / kunena_topics “first_post_id”
“message” = kunena_topics “first_post_message”
“id” = arbitrary number above greatest
“mesid” = kunena_messages “id”
“userid” = kunena_messages “userid”
“hash” = not sure how to generate this
“size” = left blank
“folder” = created new folder in media/kunena/attachments
“filename” = removed spaces from filenames and entered this name here
I then exported the excel file to csv, sanitized the the csv, and imported this into a new MySQL table. Checked the structure and merged this with the corresponding, active, table (created a backup and took the site offline).
Afterwards I ran “Recount Statistics”.
The attachments show up in the backend, but I’m assuming this is just reading the folder structure directly and is of no diagnostic value. Topics are not visible, either by search, or by viewing the category.
1. How can I generate a HASH for the attachment filenames?
2. Instead of assigning an arbitrary number, should I use AUTO INCREMENT, and if so how do I get kunena_topics “id” & “first_post_id” to match up with kunena_messages “thread” & “id” ?
I’m doing this on a test site before I attempt it on the real site. This is how I did the original migration, so the configuration below is for the test site.
Database collation check: z3ph6_kunena_attachments have wrong collation of type latin1_swedish_ci on field hash z3ph6_kunena_attachments have wrong collation of type latin1_swedish_ci on field folder z3ph6_kunena_attachments have wrong collation of type latin1_swedish_ci on field filetype z3ph6_kunena_attachments have wrong collation of type latin1_swedish_ci on field filename z3ph6_kunena_messages have wrong collation of type latin1_swedish_ci on field name z3ph6_kunena_messages have wrong collation of type latin1_swedish_ci on field email z3ph6_kunena_messages have wrong collation of type latin1_swedish_ci on field subject z3ph6_kunena_messages have wrong collation of type latin1_swedish_ci on field ip z3ph6_kunena_messages have wrong collation of type latin1_swedish_ci on field modified_reason z3ph6_kunena_messages_text have wrong collation of type latin1_swedish_ci on field message z3ph6_kunena_topics have wrong collation of type latin1_swedish_ci on field subject z3ph6_kunena_topics have wrong collation of type latin1_swedish_ci on field first_post_message z3ph6_kunena_topics have wrong collation of type latin1_swedish_ci on field first_post_guest_name z3ph6_kunena_topics have wrong collation of type latin1_swedish_ci on field last_post_message z3ph6_kunena_topics have wrong collation of type latin1_swedish_ci on field last_post_guest_name z3ph6_kunena_topics have wrong collation of type latin1_swedish_ci on field params
Joomla! SEF: Enabled | Joomla! SEF rewrite: Disabled | FTP layer: Disabled |
This message contains confidential informationhtaccess: Exists | PHP environment: Max execution time: 30 seconds | Max execution memory: 512M | Max file upload: 128M
Kunena menu details:Warning: Spoiler! [ Click to expand ] [ Click to hide ]
ID Name Menutype Link Path 738 Forum kunenamenu view=home&defaultmenu=740 forum 739 Index kunenamenu view=category&layout=list forum/index 740 Recent Topics kunenamenu view=topics&mode=replies forum/recent 741 New Topic kunenamenu view=topic&layout=create forum/newtopic 742 No Replies kunenamenu view=topics&mode=noreplies forum/noreplies 743 My Topics kunenamenu view=topics&layout=user&mode=default forum/mylatest 744 Profile kunenamenu view=user forum/profile 745 Help kunenamenu view=misc forum/help 746 Search kunenamenu view=search forum/search 747 Forum mainmenu Itemid=738 kunena-2014-08-08
Joomla default template details : Tx_Zenith_II | author: ThemeXpert.com | version: 1.1 | creationdate: Unknown
Kunena default template details : Blue Eagle | author: Kunena Team | version: 3.0.6 | creationdate: 2014-07-28
Kunena version detailed: Kunena 3.0.6 | 2014-07-28 [ Tala ]
| Kunena detailed configuration:| Kunena integration settings:Warning: Spoiler! [ Click to expand ] [ Click to hide ]
Kunena config settings: board_offline 0 enablerss 1 threads_per_page 25 messages_per_page 50 messages_per_page_search 25 showhistory 1 historylimit 25 shownew 1 disemoticons 0 template blue_eagle showannouncement 1 avataroncat 0 catimagepath category_images/ showchildcaticon 1 rtewidth 450 rteheight 300 enableforumjump 1 reportmsg 1 username 0 askemail 0 showemail 1 showuserstats 1 showkarma 0 useredit 1 useredittime 0 useredittimegrace 600 editmarkup 1 allowsubscriptions 1 subscriptionschecked 1 allowfavorites 1 maxsubject 100 maxsig 300 regonly 1 pubwrite 0 floodprotection 0 mailmod 0 mailadmin 0 captcha 0 mailfull 1 allowavatarupload 1 allowavatargallery 0 avatarquality 65 avatarsize 10000 imageheight 800 imagewidth 800 imagesize 500000 filetypes doc,docx,xls,xlsx,ppt,pptx,txt,rtf,pdf,zip,tar.gz,tgz,tar.bz2,ics,chm filesize 5000000 showranking 0 rankimages 0 userlist_rows 30 userlist_online 1 userlist_avatar 0 userlist_name 1 userlist_posts 0 userlist_karma 0 userlist_email 1 userlist_joindate 0 userlist_lastvisitdate 1 userlist_userhits 0 latestcategory showstats 0 showwhoisonline 1 showgenstats 0 showpopuserstats 0 popusercount 0 showpopsubjectstats 0 popsubjectcount 0 usernamechange 0 showspoilertag 0 showvideotag 1 showebaytag 0 trimlongurls 0 trimlongurlsfront 40 trimlongurlsback 20 autoembedyoutube 1 autoembedebay 0 ebaylanguagecode en-us sessiontimeout 1800 highlightcode 0 rss_type topic rss_timelimit month rss_limit 100 rss_included_categories rss_excluded_categories rss_specification rss2.0 rss_allow_html 1 rss_author_format name rss_author_in_title 1 rss_word_count 0 rss_old_titles 1 rss_cache 900 defaultpage recent default_sort asc sef 1 showimgforguest 0 showfileforguest 0 pollnboptions 20 pollallowvoteone 1 pollenabled 1 poppollscount 0 showpoppollstats 0 polltimebtvotes 00:15:00 pollnbvotesbyuser 100 pollresultsuserslist 0 maxpersotext 50 ordering_system mesid post_dateformat datetime post_dateformat_hover datetime hide_ip 0 imagetypes jpg,jpeg,gif,png,doc,docx,ppt,pptx,xls,xlsx checkmimetypes 1 imagemimetypes image/jpeg,image/jpg,image/gif,image/png imagequality 50 thumbheight 32 thumbwidth 32 hideuserprofileinfo put_empty boxghostmessage 0 userdeletetmessage 0 latestcategory_in 1 topicicons 0 debug 0 catsautosubscribed 0 showbannedreason 1 version_check 1 showthankyou 0 showpopthankyoustats 0 popthankscount 0 mod_see_deleted 0 bbcode_img_secure text listcat_show_moderators 1 lightbox 1 show_list_time 720 show_session_type 0 show_session_starttime 0 userlist_allowed 1 userlist_count_users 1 enable_threaded_layouts 0 category_subscriptions post topic_subscriptions every pubprofile 0 thankyou_max 10 email_recipient_count 0 email_recipient_privacy bcc captcha_post_limit 0 keywords 0 userkeywords 0 image_upload everybody file_upload registered topic_layout flat time_to_create_page 1 show_imgfiles_manage_profile 1 hold_newusers_posts 0 hold_guest_posts 0 attachment_limit 8 pickup_category 0 article_display intro send_emails 1 fallback_english 1 cache 1 cache_time 60 iptracking 1 rss_feedburner_url autolink 1 access_component 1 statslink_allowed 1 superadmin_userlist 0 userlist_usertype 1 sefutf8 0 changename 0 enablepdf 1 jmambot 0 annmodid userlist_username 0 rules_infb 1 help_infb 1 onlineusers 1| Joomla! detailed language files installed:Warning: Spoiler! [ Click to expand ] [ Click to hide ]Kunena - AlphaUserPoints Disabled
Kunena - Community Builder Disabled
Kunena - Gravatar Disabled
Kunena - JomSocial Disabled
Kunena - Joomla Enabled: access=1 login=1
Kunena - Kunena Enabled: avatar=1 profile=1
Kunena - UddeIM Disabled
Warning: Spoiler! [ Click to expand ] [ Click to hide ]
Joomla! languages installed: hu-HU Hungarian en-GB English (United Kingdom)
Third-party components: None
Third-party SEF components: None
Maybe SQL Query To Populate User_topics Table? would be a better fit. Apologies.
kunena_topic: most fields are generated from messages on recount. Looks ok.
kunena_attachments.hash is just md5 from the file contents.. Not really needed but useful.
kunena_attachments.size is in bytes
I would just use autoincrement for all ids, but if its hard, you can use whatever you want to as long as foreign keys are correct.
Should work in most parts, not sure what goes wrong there. Kunena 3.0 has hidden diagnostics tool in the backend to figure out some of the issues: administrator?option=com_kunena&view=diagnostics
- Not Allowed: to create new topic.
- Not Allowed: to reply.
- Not Allowed: to edit your message.
- K 3.0 General Questions
- School Website: Adding topics (w/ attachments) via MySQL table merge.