Invalid initialization of To: header (#1772893).
[squirrelmail.git] / contrib / flat2sql.pl
CommitLineData
209b9127 1#!/usr/bin/perl
c47b981d 2#
208870cf 3# Converts file based preferences into SQL statements.
4#
5# WARNING: this script is experimental. Don't use it as
6# privileged user or backup your data directory before using it.
7#
c47b981d 8# Copyright (c) 2002, Michael Blandford and Tal Yardeni
47ccfad4 9# Copyright (c) 2005-2006 The SquirrelMail Project Team
0575fe8b 10#
11# This script is licensed under GPL.
c47b981d 12# $Id$
208870cf 13#
209b9127 14
208870cf 15##### Default values #####
209b9127 16$db = "squirrelmail";
17$abook_table = "address";
18$pref_table = "userprefs";
1c6b3a71 19$dbtype = 'mysql';
209b9127 20##### ##### #####
21
22use Getopt::Long;
23
208870cf 24&GetOptions( \%opts, qw( abook data_dir:s delete h help pref sig user:s db:s pref_table:s abook_table:s) );
209b9127 25
26&Usage if ( defined $opts{h} or defined $opts{help} );
27
c47b981d 28unless ( defined $opts{abook} or defined $opts{pref} or defined $opts{sig}) {
29 $opts{abook}=TRUE;
30 $opts{pref}=TRUE;
31 $opts{sig}=TRUE;
209b9127 32}
33
208870cf 34
35if ( defined $opts{db} and $opts{db} ) {
36 $db = $opts{db};
37}
38if ( defined $opts{pref_table} and $opts{pref_table} ) {
39 $pref_table = $opts{pref_table};
40}
41if ( defined $opts{abook_table} and $opts{abook_table}) {
42 $abook_table = $opts{abook_table};
43}
44
45# Get data directory option and display help if it is not defined
46if ( defined $opts{data_dir} and $opts{data_dir} ) {
47 $data_dir = $opts{data_dir};
48} else {
49 &Usage;
50}
209b9127 51
52# Are we looking for specific users or all users?
53# There has to be a better way to do this - Below
54 @user_list = split ( /,/, $opts{user} ) if defined $opts{user};
55
56# Here we go
57# If no arguments are passed, and we cant open the dir, we should
58# get a usage.
59opendir(DIR, $data_dir) or
60 die "DIRECTORY READ ERROR: Could not open $data_dir!!\n";
61
62while ( $filename = readdir DIR ) {
63 next if ( $filename eq "." or $filename eq ".." );
64 $filename =~ /(.*)\.(.*)/;
65 $username = $1;
66
67 # Deal with the people
68 # There has to be a better way to do this - Above
69 next if ( defined $opts{user} and grep(!/$username/, @user_list));
70
71 # Deal with the extension files
72 $ext = $2;
73 next unless $ext;
74 &abook if ( $ext eq "abook" and defined $opts{abook} );
75 &pref if ( $ext eq "pref" and defined $opts{pref} );
76 &sig if ( $ext =~ /si([g\d])$/ and defined $opts{sig});
77}
78closedir ( DIR );
79
80# All done. Below are functions
81
82# Process a user address file
83
84sub abook {
1c6b3a71 85 print "DELETE FROM $db.$abook_table WHERE owner = '".escape_sql_string($username,true)."';\n"
209b9127 86 if ( defined $opts{delete} );
87
c47b981d 88 open(ABOOK, "<$data_dir/$filename") or
209b9127 89 die "FILE READ ERROR: Could not open $filename!!\n";
90
91 while (my $line = <ABOOK> ) {
92
93 chomp $line;
94 my ( $nickname,$firstname,$lastname,$email,$label ) = split(/\|/, $line);
95
96 print "INSERT INTO $db.$abook_table "
97 . "(owner,nickname,firstname,lastname,email,label) "
1c6b3a71 98 . "VALUES ('"
99 .escape_sql_string($username)."','"
100 .escape_sql_string($nickname)."','"
101 .escape_sql_string($firstname)."','"
102 .escape_sql_string($lastname)."','"
103 .escape_sql_string($email)."','"
104 .escape_sql_string($label)."');\n";
209b9127 105 }
106
107 close(ABOOK);
108}
109
1c6b3a71 110# Process a user preference file
209b9127 111sub pref {
112 print "DELETE FROM $db.$pref_table "
1c6b3a71 113 . "WHERE user = '".escape_sql_string($username,true)."' and prefkey not like '___sig\%___';\n"
209b9127 114 if ( defined $opts{delete} );
115
116 open(PREFS, "<$data_dir/$filename") or
117 die "FILE READ ERROR: Could not open $filename!!\n";
118
119 while (my $line = <PREFS> ) {
120
121 chomp $line;
122 my ( $prefkey, $prefval ) = split(/=/, $line);
123
124 print "INSERT INTO $db.$pref_table "
125 . "(user,prefkey,prefval) "
1c6b3a71 126 . "VALUES ('"
127 .escape_sql_string($username)."','"
128 .escape_sql_string($prefkey)."','"
129 .escape_sql_string($prefval)."');\n";
209b9127 130
131 }
132
133 close(PREFS);
134}
135
1c6b3a71 136# Process a user signature file
209b9127 137sub sig {
138
139 $del_ext = $1;
140 $del_ext = "nature" if ( $del_ext eq "g" );
141 print "DELETE FROM $db.$pref_table "
1c6b3a71 142 . "WHERE user = '".escape_sql_string($username,true)."' and prefkey like '___sig" . escape_sql_string($del_ext,true) . "___';\n"
209b9127 143 if ( defined $opts{delete} );
144
145 open(SIG, "<$data_dir/$filename") or
146 die "FILE READ ERROR: Could not open $filename!!\n";
147
148 my @lines = <SIG>;
149 close(SIG);
150
151 $filename =~ /.*\.si([g,\d]$)/;
152 $prefkey = "___sig";
153 if ( $1 eq "g" ) {
154 $prefkey .= "nature___";
155 } else {
156 $prefkey .= "$1___";
157 }
158
208870cf 159 print "INSERT INTO $db.$pref_table (user,prefkey,prefval) "
1c6b3a71 160 . "VALUES ('".escape_sql_string($username)."','"
161 .escape_sql_string($prefkey)."','"
162 .escape_sql_string(join("", @lines))."');\n";
209b9127 163}
164
1c6b3a71 165# Escapes sql strings
166# MySQL escaping:
167# http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html
168# full - \x00 (null), \n, \r, \, ', " and \x1a (Control-Z)
169# add % and _ in pattern matching expressions.
170# short - only character used for quoting and backslash should be escaped
171# PostgreSQL
172# Oracle
173# Sybase - different quoting of '
174sub escape_sql_string() {
175 my ($str,$isPattern) = @_;
176
177 if ($dbtype eq 'mysql'){
178 # escape \, ' and "
179 $str =~ s/(['"\\])/\\$1/g;
180 # escape \x1a
181 $str =~ s/([\x1a])/\\Z/g;
182 # escape ascii null
183 $str =~ s/([\x0])/\\0/g;
184 # escape line feed
185 $str =~ s/([\n])/\\n/g;
186 # escape cr
187 $str =~ s/([\r])/\\r/g;
188 if ($isPattern) {
189 $str =~ s/([%_])/\\$1/g;
190 }
191 } else {
192 die "ERROR: Unsupported database type";
193 }
194 return $str;
195}
209b9127 196
1c6b3a71 197
198# Print out the usage screen
209b9127 199sub Usage {
200
201$0 =~ /.*\/(.*)/;
202$prog = $1;
203 print <<EOL;
208870cf 204
205This program generates SQL statements to aid importing SquirrelMail
209b9127 206user config into a database.
207
208870cf 208WARNING: this script is experimental. Don't use it as
209privileged user or backup your data directory before using it.
210
211Usage: $prog --data_dir=<data_dir> [--delete] [--abook] [--pref] [--sig]
212 [--user=<username0[,username1[,username2]...]]
213 [--db=<database>] [--pref_table=<userprefs>] [--abook_table=<address>]
214
215--data_dir option must define path to SquirrelMail data directory. If
216option is not defined, script displays this help message.
217
218--abook option is used to generate SQL with address books.
219--pref option is used to generate SQL with user preferences.
220--sig option is used to generate SQL with signatures.
221
222--db option can be used to set database name. Script defaults to
223'squirrelmail'.
224
225--pref_table option can be used to set preference table name. Script
226defaults to 'userprefs'.
227
228--abook_table option can be used to set address book table name. Script
229defaults to 'address'.
209b9127 230
231Prefs --abook, --pref, and --sig are assumed if none of them as passed
232
233--delete removes all previous values for users ( --users=<> ) already in
234the database. This is useful to reimport users.
235It respects --abook, --pref, and --sig.
236
208870cf 237If --user is not specified, script extracts all user data.
209b9127 238
239EOL
240 exit 1;
241}