[How to] How to remove spam messages from modules Blog/Guestbook on site manually?

Refers to:

  • Plesk Sitebuilder 4.5 for Linux

Created:

2016-11-16 13:01:03 UTC

Modified:

2016-12-21 19:45:52 UTC

0

Was this article helpful?


Have more questions?

Submit a request

[How to] How to remove spam messages from modules Blog/Guestbook on site manually?

Symptoms

Module Blog or Guestbook on Parallels Sitebuilder site is overloaded with spam messages:

http://DOMAIN.TLD/page8.php?post=16

These messages cannot be removed through Sitebuilder Wizard due to the error:

Error! Operation has not been completed.

How to remove them manually?

Resolution

Messages of Sitebuilder module Blog are stored in SQLite database which is located on the publishing host. Using the steps below you may remove comments from the database manually.

  1. Login details for Sitebuilder database are stored in the configuration file:

    /usr/local/sitebuilder/config (/opt/sitebuilder/config for Debian/Ubuntu)
    ---8<---
    [database]
    adapter = PDO_MYSQL
    dbname = sitebuilder3
    emulate_prepares = 0
    host = localhost
    password = fbaaee3a9e9c38565de26bfca949816b
    port = 3306
    username = sitebuilder_db
    --->8---
  2. Login to the database and get alias and publishing settings of site that is published to domain DOMAIN.TLD:

    sitebuilder:~# mysql -usitebuilder_db -pfbaaee3a9e9c38565de26bfca949816b sitebuilder3
    ...
    mysql> SELECT s.alias, host.name AS host, sps.user, sps.password, sps.path, sps.url FROM site s, site_publish_settings sps, host WHERE s.site_publish_settings_id=sps.id AND sps.host_id=host.id AND sps.id IN (SELECT DISTINCT site_publish_settings_id FROM site WHERE site_publish_settings_id in (select id from site_publish_settings where url like '%DOMAIN.TLD%'));
    +------------+----------+----------+--------------+--------------------+-------------------+
    | alias | host | user | password | path | url |
    +------------+----------+----------+--------------+--------------------+-------------------+
    | SITE_ALIAS | FTP_HOST | FTP_USER | FTP_PASSWORD | /WORKING_DIRECTORY | http://DOMAIN.TLD |
    +------------+----------+----------+--------------+--------------------+-------------------+
    1 row in set (0.00 sec)

    mysql>
  3. Using the information above login to publishing host via FTP and download SQLite database file:

    sitebuilder:~# ftp FTP_HOST
    ...
    ftp> cd /WORKING_DIRECTORY
    250 CWD command successful
    ftp> cd data/storage
    250 CWD command successful
    ftp> ls
    200 PORT command successful
    150 Opening ASCII mode data connection for file list
    drwxrwxrwx 2 FTP_USER GROUP 4096 Feb 18 2010 attachments
    -rwxrwxrwx 1 FTP_USER GROUP 44327936 Sep 21 09:40 sb_modules.php
    226 Transfer complete
    ftp>
    ftp> get sb_modules.php
    local: sb_modules.php remote: sb_modules.php
    200 PORT command successful
    150 Opening BINARY mode data connection for sb_modules.php (44327936 bytes)
    226 Transfer complete
    44327936 bytes received in 1.45 secs (29791.1 kB/s)
    ftp>
  4. Upload the file to workstation with sqlite2 installed, open the database file:

    [root@sqlite2 tmp]# gunzip sb_modules.php.gz
    [root@sqlite2 tmp]# sqlite sb_modules.php
    SQLite version 2.8.17
    Enter ".help" for instructions
    sqlite> .tables
    modules_6dziemf3hhv_category modules_hjaivplgkkh_message
    modules_6dziemf3hhv_category_post modules_hjaivplgkkh_post
    modules_6dziemf3hhv_message modules_hspfnfjc8gi_category
    modules_6dziemf3hhv_post modules_hspfnfjc8gi_category_post
    modules_d1r1n0g0zhn_answers modules_hspfnfjc8gi_message
    modules_d1r1n0g0zhn_question modules_hspfnfjc8gi_post
    modules_fih5f0f33u4_answers modules_rfx8bqh9ql5_answers
    modules_fih5f0f33u4_question modules_rfx8bqh9ql5_question
    modules_hgundcuvolg_answers modules_swvxgsxipl7_answers
    modules_hgundcuvolg_question modules_swvxgsxipl7_question
    modules_hjaivplgkkh_category pr
    modules_hjaivplgkkh_category_post
    sqlite>

If you do not have utiltiy sqlite of version 2, you may install rpm attached . Install the package on your system (CentOS5 on 32 and 64-bit) with " rpm -Uvh ".

  1. Get identity of page page8.php (with module Blog) from database of Parallels Plesk Sitebuilder 4.5:

    mysql> select site_page_id from site where alias='SITE_ALIAS';
    +--------------+
    | site_page_id |
    +--------------+
    | 5843 |
    +--------------+
    1 row in set (0.00 sec)

    mysql> select id, title, module_id, file_name, identity from site_page where parent_id in (select id from site_page where parent_id = 5843) or parent_id = 5843 order by file_name;
    +-------+------------------------+-----------+-----------+-------------+
    | id | title | module_id | file_name | identity |
    +-------+------------------------+-----------+-----------+-------------+
    | 5844 | Home | NULL | page1 | 4he3vgzyhwv |
    | 6237 | Editor's Blog | 1 | page10 | 6dziemf3hhv |
    | 6101 | Contact Us | NULL | page11 | pc68m1mtr7f |
    | 6103 | Knowledge Services | NULL | page3 | rlke84u771s |
    | 6104 | Knowledge Update | NULL | page4 | cdbiq235n9h |
    | 6105 | Members' articles | NULL | page5 | iyiws68lcy8 |
    | 5849 | Favourite Links | NULL | page6 | 8pdmz61dest |
    | 19655 | Members' presentations | NULL | page7 | 4aa1bwf3qbe |
    | 6119 | Discussion Boards | 1 | page8 | hspfnfjc8gi | <== !!!
    | 6099 | News and Events | 1 | page9 | hjaivplgkkh |
    +-------+------------------------+-----------+-----------+-------------+
    10 rows in set (0.00 sec)

    mysql>

    In SQLite database file you should edit tables modules_hspfnfjc8gi_*.
  2. Dump SQLite database file to get it readable form:

    [root@sqlite2 tmp]# sqlite sb_modules.php .dump > dump.sql
  3. Open the dump file and find identity of several spam comments from the port:

    [root@sqlite2 tmp]# less dump.sql
    --->8---
    INSERT INTO modules_hspfnfjc8gi_message VALUES(3311,16,'','Neubscoorce','ralph lauren',NULL,NULL,1343050997,'viFTgqt &lt;a href=&quot;http://magasindesralphlauren.com&quot;&gt;ralph lauren moins cher&lt;/a&gt; yLTlkc <br />^M
    <br />^M
    cQTpwp &lt;a href=http://magasindesralphlauren.com&gt;polo by ralph lauren&lt;/a&gt; GThfrIQxpj <br />^M
    <br />^M
    http://magasindesralphlauren.com','a:0:{}',2);
    INSERT INTO modules_hspfnfjc8gi_message VALUES(3298,16,'','Affifysep','beats by dre ',NULL,NULL,1343022080,'tMnataRyrh &lt;a href=&quot;http://isabelmarantsneakers.webeden.net/&quot;&gt;isabel marant&lt;/a&gt; kNzirkAkqo http://isabelmarantsneakers.webeden.net/ pInigxJjpm &lt;a href=&quot;http://fitflop-pascher.com/&quot;&gt;fitflop&lt;/a&gt; jAknygTpbi http://fitflop-pascher.com/ dXbeufLehp &lt;a href=&quot;http://www.melovinja.com/&quot;&gt;beats by dr dre &lt;/a&gt; oHfvjjXesy http://www.melovinja.com/<br />^M
    ','a:0:{}',2);
    ---8<---

They are related to post #16 (parent_id=16). Remove the records related to port 16 from the database:

    sqlite> .schema modules_hspfnfjc8gi_message
CREATE TABLE modules_hspfnfjc8gi_message (id INTEGER, parent_id INTEGER, user_id INTEGER, author VARCHAR(50), subject VARCHAR(255), url VARCHAR(255), email VARCHAR(50), created INTEGER, comment TEXT, comment_images TEXT, storage_state INTEGER, PRIMARY KEY (id,storage_state));
sqlite>
sqlite> select count(*) from modules_hspfnfjc8gi_message where parent_id=16;
280
sqlite> delete from modules_hspfnfjc8gi_message where parent_id=16;
sqlite>
  1. Upload fixed database file to the publishing location into directory /data/storage. Remember to fix permissions for uploaded file:

    ftp> cd /data/storage
    250 CWD command successful
    ftp> ls
    200 PORT command successful
    150 Opening ASCII mode data connection for file list
    drwxrwxrwx 2 FTP_USER GROUP 4096 Feb 18 2010 attachments
    -rwxrwxrwx 1 FTP_USER GROUP 44327936 Sep 21 09:40 sb_modules.php
    226 Transfer complete
    ftp>
    ftp> del sb_modules.php
    250 DELE command successful
    ftp> put sb_modules.php
    local: sb_modules.php remote: sb_modules.php
    200 PORT command successful
    150 Opening BINARY mode data connection for sb_modules.php
    226 Transfer complete
    44327936 bytes sent in 0.61 secs (70874.4 kB/s)
    ftp> chmod 777 sb_modules.php
    200 SITE CHMOD command successful
    ftp>
  2. Verify the posts are missing on the publishing host now:

    http://DOMAIN.TLD/page8.php?post=16

[How to] Sitebuilder sites are overloaded with spam messages on Blog/Guestbook pages.

Have more questions? Submit a request
Please sign in to leave a comment.