• From: Provence, France
  • Registered: 2019-11-16
  • Posts: 10

Topic: Spam registrations

Hello !
I am trying to fight robot registrations.
I cannot install any anti-spam extension (Fancy stop spam 1.3.52.2, Pan antispam 0.3.4, Pan Moderator 0.6.4) : I get error on line 552 of include/dblayer/sqlite3.php.
My PunBB is 1.4.4 and uses sqlite3. Php version is 7.3 and cannot be modified.

What can I do, please ?

If I try to reinstall a brand new forum, how can I recover the old messages ?  I do not know how to save and restore a sqlite3 data base. I have never done this.

Note that I was not able to login here with my old credentials for MaraKat and I had to register anew. So I am not new to this forum, since Katryne = Marakat..

Katryne's Website

  • From: Siberia
  • Registered: 2013-08-05
  • Posts: 408

Re: Spam registrations

I get error on line 552 of include/dblayer/sqlite3.php.

line 552:

        // Drop old table

?

I installed the new punbb 1.4.4 (_https://github.com/punbb/punbb/archive/master.zip) on php 7.3.5 + sqlite3 3.24.0 and took Fancy Stop SPAM 1.3.53 (_https://github.com/dimkalinux/Fancy-Extensions/archive/master.zip). There were no errors when installing it.


https://stackoverflow.com/questions/256 … e-database
https://stackoverflow.com/questions/186 … 3-to-mysql

ForkBB
I speak only Russian  :P

Visman's Website

3 Reply by Katryne (edited by Katryne 2019-11-18 06:36)

  • From: Provence, France
  • Registered: 2019-11-16
  • Posts: 10

Re: Spam registrations

Thanks for answer and links.
My punbb data base (sqlite3) seems to have some problems. I will try to reinstall everything.

Edit : my line 552 in sqlite3.php is :

        // Create new table
        $this->query($new_table) or error(__FILE__, __LINE__);

From what I understand, some extensions cannot be installed  because new table cannot be created : this table already exists.

Katryne's Website

  • From: Siberia
  • Registered: 2013-08-05
  • Posts: 408

Re: Spam registrations

1. in config.php

// Enable DEBUG mode by removing // from the following line
//define('FORUM_DEBUG', 1);

// Enable show DB Queries mode by removing // from the following line
//define('FORUM_SHOW_QUERIES', 1);

replace to

// Enable DEBUG mode by removing // from the following line
define('FORUM_DEBUG', 1);

// Enable show DB Queries mode by removing // from the following line
define('FORUM_SHOW_QUERIES', 1);

2. use standart sqlite3.php file https://github.com/punbb/punbb/blob/mas … qlite3.php

3. Show the full text of the error that will be displayed in the message.

P.S. You can also look in the errors log of your server.

ForkBB
I speak only Russian  :P

Visman's Website

5 Reply by Katryne (edited by Visman 2019-11-18 11:04)

  • From: Provence, France
  • Registered: 2019-11-16
  • Posts: 10

Re: Spam registrations

Thanks for helping.
With the sqlite3.php version that I found at Github, as you advised, here is the full error message when I try to install PanAntispam (the error is about Create new table):

Désolé ! La page ne peut être chargée.

Cette erreur peut être temporaire. Actualisez la page. Si le problème persiste, essayez à nouveau dans 5-10 minutes.

Le serveur BDD a répondu: duplicate column name: errors (Errno: 1).

Impossible d'exécuter la requête : CREATE TABLE dbtonline ( errors INTEGER NOT NULL DEFAULT 0, user_id INTEGER NOT NULL DEFAULT 1, ident VARCHAR(200) NOT NULL DEFAULT '', logged INTEGER NOT NULL DEFAULT 0, idle INTEGER NOT NULL DEFAULT 0, csrf_token VARCHAR(40) NOT NULL DEFAULT '', prev_url VARCHAR(255), last_post INTEGER, last_search INTEGER, errors INTEGER NOT NULL DEFAULT 0, UNIQUE (user_id,ident) );

Une erreur est apparue à la ligne 423 dans le fichier /var/www/legtux.org/users/revest/www/include/dblayer/sqlite3.php

Before I clicked on the install link, here is the query that was displayed :

delete

Sorry, I had never done this and do not know what to do with it.

Katryne's Website

  • From: Siberia
  • Registered: 2013-08-05
  • Posts: 408

Re: Spam registrations

You still have the wrong file.
Look at line 423 https://github.com/punbb/punbb/blob/mas … 3.php#L423

1. Download archive https://github.com/punbb/punbb/archive/master.zip
2. Take one file from it: include/dblayer/sqlite3.php
3. Copy it to your server with a replacement file.
4. Show the error again if it appears.

ForkBB
I speak only Russian  :P

Visman's Website

7 Reply by Katryne (edited by Visman 2019-11-18 12:15)

  • From: Provence, France
  • Registered: 2019-11-16
  • Posts: 10

Re: Spam registrations

Thanks for the direct link, I am not comfortable with the new Github.

It looks that the error is still about Create new table (line 556 of sqlite3.php)

Error when cliking on install button :


Le serveur BDD a répondu: duplicate column name: errors (Errno: 1).

Impossible d'exécuter la requête : CREATE TABLE dbtonline ( errors INTEGER NOT NULL DEFAULT 0, user_id INTEGER NOT NULL DEFAULT 1, ident VARCHAR(200) NOT NULL DEFAULT '', logged INTEGER NOT NULL DEFAULT 0, idle INTEGER NOT NULL DEFAULT 0, csrf_token VARCHAR(40) NOT NULL DEFAULT '', prev_url VARCHAR(255), last_post INTEGER, last_search INTEGER, errors INTEGER NOT NULL DEFAULT 0, UNIQUE (user_id,ident) );

Une erreur est apparue à la ligne 556 dans le fichier /var/www/legtux.org/users/revest/www/include/dblayer/sqlite3.php

Query at bottom of extension page :


del

Katryne's Website

8 Reply by Visman (edited by Visman 2019-11-18 12:43)

  • From: Siberia
  • Registered: 2013-08-05
  • Posts: 408

Re: Spam registrations

I can’t understand where the duplication comes from.

1. Administration → Settings → Setup → Redirect wait = 60 → Save changes
2. open /include/dblayer/sqlite3.php
before

        return (preg_match('/[\r\n]'.preg_quote($field_name).' /', $sql) === 1);

add

echo "<pre>";
var_dump($field_name, $sql);
echo "</pre>";

----------------------
after

    function add_field($table_name, $field_name, $field_type, $allow_null, $default_value = null, $after_field = 0, $no_prefix = false)
    {
        if ($this->field_exists($table_name, $field_name, $no_prefix))
            return;

        $table = $this->get_table_info($table_name, $no_prefix);

add

echo "<pre>";
var_dump($table['columns']);
echo "</pre>";

-------------------------------

after

        $old_columns = array_keys($table['columns']);
        array_insert($table['columns'], $after_field, $query.',', $field_name);

add

echo "<pre>";
var_dump($after_field, $query.',', $field_name, $table['columns']);
echo "</pre>";

-------------------------

(below, only for add_field() function)
replace

        $this->query($new_table) or error(__FILE__, __LINE__);

to

        $this->query($new_table) or exit;

save file

3. Run the installation of the extension again and copy the text here from the browser that the var_dump () commands will output.

ForkBB
I speak only Russian  :P

Visman's Website

9 Reply by Katryne (edited by Katryne 2019-11-28 12:32)

  • From: Provence, France
  • Registered: 2019-11-16
  • Posts: 10

Re: Spam registrations

Hello Visman ! Sorry for being so long to answer. And many thanks for your help.
I did as you told me.
But for the last modification I found 2 possible locations and did not know which to modify. So I modify one at randow. And then launched extension install once more.. Below is what is displayed when I was not able to complete the install.

string(6) "errors"
string(335) "CREATE TABLE dbtonline (
user_id INTEGER NOT NULL DEFAULT 1,
ident VARCHAR(200) NOT NULL DEFAULT '',
logged INTEGER NOT NULL DEFAULT 0,
idle INTEGER NOT NULL DEFAULT 0,
csrf_token VARCHAR(40) NOT NULL DEFAULT '',
prev_url VARCHAR(255),
last_post INTEGER,
last_search INTEGER, errors INTEGER NOT NULL DEFAULT 0,
UNIQUE (user_id,ident)
)"

string(6) "errors"
string(335) "CREATE TABLE dbtonline (
user_id INTEGER NOT NULL DEFAULT 1,
ident VARCHAR(200) NOT NULL DEFAULT '',
logged INTEGER NOT NULL DEFAULT 0,
idle INTEGER NOT NULL DEFAULT 0,
csrf_token VARCHAR(40) NOT NULL DEFAULT '',
prev_url VARCHAR(255),
last_post INTEGER,
last_search INTEGER, errors INTEGER NOT NULL DEFAULT 0,
UNIQUE (user_id,ident)
)"

array(8) {
  ["user_id"]=>
  string(27) "INTEGER NOT NULL DEFAULT 1,"
  ["ident"]=>
  string(33) "VARCHAR(200) NOT NULL DEFAULT '',"
  ["logged"]=>
  string(27) "INTEGER NOT NULL DEFAULT 0,"
  ["idle"]=>
  string(27) "INTEGER NOT NULL DEFAULT 0,"
  ["csrf_token"]=>
  string(32) "VARCHAR(40) NOT NULL DEFAULT '',"
  ["prev_url"]=>
  string(13) "VARCHAR(255),"
  ["last_post"]=>
  string(8) "INTEGER,"
  ["last_search"]=>
  string(43) "INTEGER, errors INTEGER NOT NULL DEFAULT 0,"
}

int(0)
string(27) "INTEGER NOT NULL DEFAULT 0,"
string(6) "errors"
array(9) {
  ["errors"]=>
  string(27) "INTEGER NOT NULL DEFAULT 0,"
  ["user_id"]=>
  string(27) "INTEGER NOT NULL DEFAULT 1,"
  ["ident"]=>
  string(33) "VARCHAR(200) NOT NULL DEFAULT '',"
  ["logged"]=>
  string(27) "INTEGER NOT NULL DEFAULT 0,"
  ["idle"]=>
  string(27) "INTEGER NOT NULL DEFAULT 0,"
  ["csrf_token"]=>
  string(32) "VARCHAR(40) NOT NULL DEFAULT '',"
  ["prev_url"]=>
  string(13) "VARCHAR(255),"
  ["last_post"]=>
  string(8) "INTEGER,"
  ["last_search"]=>
  string(43) "INTEGER, errors INTEGER NOT NULL DEFAULT 0,"
}


Warning: SQLite3::query(): Unable to prepare statement: 1, duplicate column name: errors in /var/www/???/users/blackland/www/aforum/include/dblayer/sqlite3.php on line 100

I have installed a copy of my forum on a test site, so that I do not disturb the living site. I was also able to install there phplite admin, in case I could mend the data base and put it back afterwards on the original hosting. But If I can in theory manage the data base now, I do not know what to do with it.

Katryne's Website

10 Reply by Visman

  • From: Siberia
  • Registered: 2013-08-05
  • Posts: 408

Re: Spam registrations

Replace field_exists() function in include/dblayer/sqlite3.php to

    function field_exists($table_name, $field_name, $no_prefix = false)
    {
#        $result = $this->query('SELECT sql FROM sqlite_master WHERE name = \''.($no_prefix ? '' : $this->prefix).$this->escape($table_name).'\' AND type=\'table\'');
#        $sql = $this->result($result);
#
#        if (is_null($sql) || $sql === false)
#            return false;
#
#        return (preg_match('/[\r\n]'.preg_quote($field_name).' /', $sql) === 1);

        $result = $this->query('PRAGMA table_info(\'' . ($no_prefix ? '' : $this->prefix) . $this->escape($table_name) . '\');');
        while ($row = $this->fetch_assoc($result))
        {
            if ($row['name'] == $field_name)
            {
                $this->free_result($result);
                return true;
            }
        }
        return false;
    }
ForkBB
I speak only Russian  :P

Visman's Website

11 Reply by Katryne (edited by Katryne 2019-11-28 14:13)

  • From: Provence, France
  • Registered: 2019-11-16
  • Posts: 10

Re: Spam registrations

Welll thank you... I was able to install Pan antispam. And to register as a new member.
What should I do now ?
Do you have an explanation ?

Katryne's Website

12 Reply by Visman (edited by Visman 2019-11-28 14:25)

  • From: Siberia
  • Registered: 2013-08-05
  • Posts: 408

Re: Spam registrations

>What should I do now ?
If you still have changes from this http://punbb.informer.com/forums/post/158388/#p158388 post, you need to roll them back.
Next, use the new function from this http://punbb.informer.com/forums/post/158396/#p158396 post.

>Do you have an explanation?
The old field_exists() function used a too simple regular expression to find the field name in the query text.
As a result, she did not find anything and gave the result "this field is not in the table".

P.S. The new function uses the database command to get the full table structure.

ForkBB
I speak only Russian  :P

Visman's Website

  • From: Provence, France
  • Registered: 2019-11-16
  • Posts: 10

Re: Spam registrations

Many many Thanks.
I will now try this corrected file on my main (living) forum.

Katryne's Website

14 Reply by Visman

  • From: Siberia
  • Registered: 2013-08-05
  • Posts: 408

Re: Spam registrations

Please test 1.3.54.3 http://punbb.informer.com/forums/post/158414/#p158414

ForkBB
I speak only Russian  :P

Visman's Website