Blame
Date:
Sun Oct 8 22:28:02 2023 UTC
Message:
Now prevents creating accounts that exist in database with a password
01
2023-04-14
jrmu
#!/usr/bin/perl
02
2023-04-14
jrmu
#
03
2023-04-14
jrmu
# Written by IanJ@nastycode.com 13/04/2023
04
2023-04-14
jrmu
#
05
2023-04-14
jrmu
use strict;
06
2023-04-14
jrmu
use DBI;
07
2023-04-14
jrmu
08
2023-04-14
jrmu
my $DBFILE = 'botnow.db';
09
2023-04-14
jrmu
my $OLDDBFILE = 'botnow.old.db';
10
2023-04-14
jrmu
11
2023-04-14
jrmu
my $dbh = DBI->connect("dbi:SQLite:dbname=$DBFILE","");
12
2023-04-14
jrmu
13
2023-04-14
jrmu
foreach my $table ("mail", "shell", "bnc")
14
2023-04-14
jrmu
{
15
2023-04-14
jrmu
# Delete unconfirmed captcha entries
16
2023-04-14
jrmu
print "Deleting unconfirmed captcha entries.\n";
17
2023-04-14
jrmu
$dbh->do("delete from $table where password is null");
18
2023-04-14
jrmu
19
2023-04-14
jrmu
# Create table with new schema (seems you can't add/modify autoindex/timestamp fields)
20
2023-04-14
jrmu
print "Create table '".$table."_new' with new schema for auto timestamp.\n";
21
2023-04-14
jrmu
$dbh->do("CREATE table ".$table."_new (
22
2023-04-14
jrmu
id INTEGER PRIMARY KEY,
23
2023-04-14
jrmu
hashid VARCHAR(100),
24
2023-04-14
jrmu
ircid INTEGER,
25
2023-04-14
jrmu
wwwid INTEGER,
26
2023-04-14
jrmu
smtpid INTEGER,
27
2023-04-14
jrmu
username VARCHAR(32),
28
2023-04-14
jrmu
email VARCHAR(100),
29
2023-04-14
jrmu
password VARCHAR(100),
30
2023-04-14
jrmu
localtime default current_timestamp,
31
2023-04-14
jrmu
captcha INTEGER);");
32
2023-04-14
jrmu
33
2023-04-14
jrmu
# Copy data to new table
34
2023-04-14
jrmu
print "Copying data from '$table' to '".$table."_new' table.\n";
35
2023-04-14
jrmu
$dbh->do("insert into ".$table."_new select * from $table");
36
2023-04-14
jrmu
37
2023-04-14
jrmu
# Copy data from old db into new table
38
2023-04-14
jrmu
print "Copying data from old database '$table' to '".$table."_new'.\n";
39
2023-04-14
jrmu
$dbh->do("attach database '$OLDDBFILE' as olddb");
40
2023-04-14
jrmu
# Causes column mismatch without adding hashid for shell
41
2023-04-14
jrmu
$dbh->do("alter table olddb.shell add hashid varchar(100)") if ($table eq "shell");
42
2023-04-14
jrmu
$dbh->do("insert into ".$table."_new (hashid, ircid, wwwid, smtpid, username, email, password, captcha) \
43
2023-04-14
jrmu
select hashid, ircid, wwwid, smtpid, username, email, password, captcha from olddb.".$table." \
44
2023-04-14
jrmu
where password is not null");
45
2023-04-14
jrmu
$dbh->do("detach olddb");
46
2023-04-14
jrmu
47
2023-04-14
jrmu
# Set timestamp to null as we don't know the true date of entry
48
2023-04-14
jrmu
print "Set timestamps to null as we don't know the true date of entries.\n";
49
2023-04-14
jrmu
$dbh->do("update ".$table."_new set localtime = null");
50
2023-04-14
jrmu
51
2023-04-14
jrmu
# Rename original table (don't delete it yet)
52
2023-04-14
jrmu
print "Rename '$table' to '".$table."_backup', rename '".$table."_new' to '$table'.\n";
53
2023-04-14
jrmu
$dbh->do("alter table $table rename to ".$table."_backup");
54
2023-04-14
jrmu
$dbh->do("alter table ".$table."_new rename to $table");
55
2023-04-14
jrmu
}
56
2023-04-14
jrmu
57
2023-04-14
jrmu
print "Finished - hopefully with no errors!\n";
IRCNow