#!/usr/bin/perl
-
-# flat2sql.pl v1.0
-#
-# Copyright (c) 2002, Michael Blandford and Tal Yardeni
-
-##### Conf Section #####
-
-$data_dir = "/home/www/mail.topolis.inet/www/squirrelmail-devel-20050911/data";
+#
+# Converts file based preferences into SQL statements.
+#
+# WARNING: this script is experimental. Don't use it as
+# privileged user or backup your data directory before using it.
+#
+# Copyright (c) 2002, Michael Blandford and Tal Yardeni
+# Copyright (c) 2005-2006 The SquirrelMail Project Team
+#
+# This script is licensed under GPL.
+# $Id$
+#
+
+##### Default values #####
$db = "squirrelmail";
$abook_table = "address";
$pref_table = "userprefs";
-
+$dbtype = 'mysql';
##### ##### #####
use Getopt::Long;
-&GetOptions( \%opts, qw( abook data_dir:s delete h help pref sig user:s ) );
+&GetOptions( \%opts, qw( abook data_dir:s delete h help pref sig user:s db:s pref_table:s abook_table:s) );
&Usage if ( defined $opts{h} or defined $opts{help} );
-unless ( defined $opts{abook} or defined $opts{pref} or
- defined $opts{sig}) {
- $opts{abook}=TRUE;
- $opts{pref}=TRUE;
- $opts{sig}=TRUE;
+unless ( defined $opts{abook} or defined $opts{pref} or defined $opts{sig}) {
+ $opts{abook}=TRUE;
+ $opts{pref}=TRUE;
+ $opts{sig}=TRUE;
}
-# Override the data directory if passed as an argument
-$data_dir = $opts{data_dir} if ( defined $opts{data_dir} );
+
+if ( defined $opts{db} and $opts{db} ) {
+ $db = $opts{db};
+}
+if ( defined $opts{pref_table} and $opts{pref_table} ) {
+ $pref_table = $opts{pref_table};
+}
+if ( defined $opts{abook_table} and $opts{abook_table}) {
+ $abook_table = $opts{abook_table};
+}
+
+# Get data directory option and display help if it is not defined
+if ( defined $opts{data_dir} and $opts{data_dir} ) {
+ $data_dir = $opts{data_dir};
+} else {
+ &Usage;
+}
# Are we looking for specific users or all users?
# There has to be a better way to do this - Below
# Process a user address file
sub abook {
- print "DELETE FROM $db.$abook_table WHERE owner = '$username;\n"
+ print "DELETE FROM $db.$abook_table WHERE owner = '".escape_sql_string($username,true)."';\n"
if ( defined $opts{delete} );
- open(ABOOK, ">$data_dir/$filename") or
+ open(ABOOK, "<$data_dir/$filename") or
die "FILE READ ERROR: Could not open $filename!!\n";
while (my $line = <ABOOK> ) {
print "INSERT INTO $db.$abook_table "
. "(owner,nickname,firstname,lastname,email,label) "
- . "VALUES ('$username','$nickname','$firstname','$lastname',"
- . "'$email','$label');\n";
+ . "VALUES ('"
+ .escape_sql_string($username)."','"
+ .escape_sql_string($nickname)."','"
+ .escape_sql_string($firstname)."','"
+ .escape_sql_string($lastname)."','"
+ .escape_sql_string($email)."','"
+ .escape_sql_string($label)."');\n";
}
close(ABOOK);
}
-# Process a user prefernce file
-
+# Process a user preference file
sub pref {
print "DELETE FROM $db.$pref_table "
- . "WHERE user = '$username' and prefkey not like '___sig\%___';\n"
+ . "WHERE user = '".escape_sql_string($username,true)."' and prefkey not like '___sig\%___';\n"
if ( defined $opts{delete} );
open(PREFS, "<$data_dir/$filename") or
print "INSERT INTO $db.$pref_table "
. "(user,prefkey,prefval) "
- . "VALUES ('$username','$prefkey','$prefval');\n";
+ . "VALUES ('"
+ .escape_sql_string($username)."','"
+ .escape_sql_string($prefkey)."','"
+ .escape_sql_string($prefval)."');\n";
}
close(PREFS);
}
-# Process a user sig file
-
+# Process a user signature file
sub sig {
$del_ext = $1;
$del_ext = "nature" if ( $del_ext eq "g" );
print "DELETE FROM $db.$pref_table "
- . "WHERE user = '$username' and prefkey like '___sig" . $del_ext . "___';\n"
+ . "WHERE user = '".escape_sql_string($username,true)."' and prefkey like '___sig" . escape_sql_string($del_ext,true) . "___';\n"
if ( defined $opts{delete} );
open(SIG, "<$data_dir/$filename") or
$prefkey .= "$1___";
}
- print "INSERT INTO $db.$sig_table (user,prefkey,prefval) "
- . "VALUES ('$username','$prefkey','".join("", @lines)."');\n";
+ print "INSERT INTO $db.$pref_table (user,prefkey,prefval) "
+ . "VALUES ('".escape_sql_string($username)."','"
+ .escape_sql_string($prefkey)."','"
+ .escape_sql_string(join("", @lines))."');\n";
}
-# Print out the usage screen
+# Escapes sql strings
+# MySQL escaping:
+# http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html
+# full - \x00 (null), \n, \r, \, ', " and \x1a (Control-Z)
+# add % and _ in pattern matching expressions.
+# short - only character used for quoting and backslash should be escaped
+# PostgreSQL
+# Oracle
+# Sybase - different quoting of '
+sub escape_sql_string() {
+ my ($str,$isPattern) = @_;
+
+ if ($dbtype eq 'mysql'){
+ # escape \, ' and "
+ $str =~ s/(['"\\])/\\$1/g;
+ # escape \x1a
+ $str =~ s/([\x1a])/\\Z/g;
+ # escape ascii null
+ $str =~ s/([\x0])/\\0/g;
+ # escape line feed
+ $str =~ s/([\n])/\\n/g;
+ # escape cr
+ $str =~ s/([\r])/\\r/g;
+ if ($isPattern) {
+ $str =~ s/([%_])/\\$1/g;
+ }
+ } else {
+ die "ERROR: Unsupported database type";
+ }
+ return $str;
+}
+
+# Print out the usage screen
sub Usage {
$0 =~ /.*\/(.*)/;
$prog = $1;
print <<EOL;
-This program generates SQL statements to aid importing squirrelmail
+
+This program generates SQL statements to aid importing SquirrelMail
user config into a database.
-Usage: $prog [--delete] [--abook] [--pref] [--sig] [--data_dir=<>] [--user=<username0[,username1[,username2]...]]
+WARNING: this script is experimental. Don't use it as
+privileged user or backup your data directory before using it.
+
+Usage: $prog --data_dir=<data_dir> [--delete] [--abook] [--pref] [--sig]
+ [--user=<username0[,username1[,username2]...]]
+ [--db=<database>] [--pref_table=<userprefs>] [--abook_table=<address>]
+
+--data_dir option must define path to SquirrelMail data directory. If
+option is not defined, script displays this help message.
+
+--abook option is used to generate SQL with address books.
+--pref option is used to generate SQL with user preferences.
+--sig option is used to generate SQL with signatures.
+
+--db option can be used to set database name. Script defaults to
+'squirrelmail'.
+
+--pref_table option can be used to set preference table name. Script
+defaults to 'userprefs'.
+
+--abook_table option can be used to set address book table name. Script
+defaults to 'address'.
Prefs --abook, --pref, and --sig are assumed if none of them as passed
the database. This is useful to reimport users.
It respects --abook, --pref, and --sig.
-If --user is not specified, it will try to do all users.
+If --user is not specified, script extracts all user data.
EOL
exit 1;
projects / squirrelmail.git / blobdiff