REQUEST_URI is used in php_self(), so make sure it's sanitized too
[squirrelmail.git] / contrib / flat2sql.pl
1 #!/usr/bin/perl
2 #
3 # Converts file-based preferences into SQL statements.
4 #
5 # WARNING: this script is experimental. We recommend that
6 # you not use it when logged in as a privileged user (such
7 # as root). Also, ALWAYS back up your data directory before
8 # using this script.
9 #
10 # Copyright (c) 2002, Michael Blandford and Tal Yardeni
11 # Copyright (c) 2005-2010 The SquirrelMail Project Team
12 #
13 # This script is licensed under the GNU Public License (GPL).
14 # See: http://opensource.org/licenses/gpl-license.php
15 # $Id$
16 #
17
18 ##### Default values #####
19 # TODO: expose the database type as a CLI option, but first need more sections in sub escape_sql_string()
20 my $dbtype = 'mysql';
21 my $abookdb = "squirrelmail";
22 my $prefdb = "squirrelmail";
23 my $abook_table = "address";
24 my $abook_owner="owner";
25 my $abook_nickname="nickname";
26 my $abook_firstname="firstname";
27 my $abook_lastname="lastname";
28 my $abook_email="email";
29 my $abook_label="label";
30 my $pref_table = "userprefs";
31 my $pref_user = "user";
32 my $pref_key = "prefkey";
33 my $pref_value = "prefval";
34 ##### ##### #####
35
36 use Getopt::Long;
37
38 my (%opts, $verbose, $data_dir);
39
40 &GetOptions( \%opts, qw( abook data_dir:s delete h help v verbose pref sig user:s abookdb:s prefdb:s pref_table:s abook_table:s abook_owner:s abook_nickname:s abook_firstname:s abook_lastname:s abook_email:s abook_label:s pref_user:s pref_key:s pref_value:s) );
41
42 &Usage if ( defined $opts{h} or defined $opts{help} );
43
44 unless ( defined $opts{abook} or defined $opts{pref} or defined $opts{sig}) {
45 $opts{abook}='TRUE';
46 $opts{pref}='TRUE';
47 $opts{sig}='TRUE';
48 }
49
50
51 if ( defined $opts{verbose} or defined $opts{v} ) {
52 $verbose = 1;
53 }
54 if ( defined $opts{abookdb} and $opts{abookdb} ) {
55 $abookdb = $opts{abookdb};
56 }
57 if ( defined $opts{prefdb} and $opts{prefdb} ) {
58 $prefdb = $opts{prefdb};
59 }
60 if ( defined $opts{pref_table} and $opts{pref_table} ) {
61 $pref_table = $opts{pref_table};
62 }
63 if ( defined $opts{pref_user} and $opts{pref_user} ) {
64 $pref_user = $opts{pref_user};
65 }
66 if ( defined $opts{pref_key} and $opts{pref_key} ) {
67 $pref_key = $opts{pref_key};
68 }
69 if ( defined $opts{pref_value} and $opts{pref_value} ) {
70 $pref_value = $opts{pref_value};
71 }
72 if ( defined $opts{abook_table} and $opts{abook_table}) {
73 $abook_table = $opts{abook_table};
74 }
75 if ( defined $opts{abook_owner} and $opts{abook_owner} ) {
76 $abook_owner = $opts{abook_owner};
77 }
78 if ( defined $opts{abook_nickname} and $opts{abook_nickname} ) {
79 $abook_nickname = $opts{abook_nickname};
80 }
81 if ( defined $opts{abook_firstname} and $opts{abook_firstname} ) {
82 $abook_firstname = $opts{abook_firstname};
83 }
84 if ( defined $opts{abook_lastname} and $opts{abook_lastname} ) {
85 $abook_lastname = $opts{abook_lastname};
86 }
87 if ( defined $opts{abook_email} and $opts{abook_email} ) {
88 $abook_email = $opts{abook_email};
89 }
90 if ( defined $opts{abook_label} and $opts{abook_label} ) {
91 $abook_label = $opts{abook_label};
92 }
93
94 # Get data directory option and display help if it is not defined
95 if ( defined $opts{data_dir} and $opts{data_dir} ) {
96 $data_dir = $opts{data_dir};
97 } else {
98 &Usage;
99 }
100
101 # Are we looking for specific users or all users?
102 # There has to be a better way to do this - Below
103 my @user_list = split ( /,/, $opts{user} ) if defined $opts{user};
104
105 inspect_files($data_dir);
106
107 # All done. Below are functions
108
109
110 # Finds needed user files in the given directory
111 # and recurses any nested data directories as needed
112 #
113 sub inspect_files {
114
115 my ($data_dir) = @_;
116 my ($filename, $username, $ext);
117 local *DIR;
118
119 # Here we go
120 # If no arguments are passed, and we cant open the dir, we should
121 # get a usage.
122 opendir(DIR, $data_dir) or
123 die "DIRECTORY READ ERROR: Could not open $data_dir!!\n";
124
125 while ( $filename = readdir DIR ) {
126
127 next if ( $filename eq "." or $filename eq ".." );
128
129 if ($verbose) { print STDERR "; INSPECTING: $data_dir/$filename\n"; }
130
131 # recurse into nested (hashed) directories
132 #
133 if ($filename =~ /^[0123456789abcdef]$/ && -d "$data_dir/$filename") {
134 inspect_files("$data_dir/$filename");
135 }
136
137 next unless $filename =~ /(.*)\.(.*)/;
138 $username = $1;
139 next unless $username;
140
141 # Deal with the people
142 # There has to be a better way to do this - Above
143 next if (defined $opts{user} and !grep($username eq $_, @user_list));
144
145 # Deal with the extension files
146 $ext = $2;
147 next unless $ext;
148
149 &abook("$data_dir/$filename", $username)
150 if ( $ext eq "abook" and defined $opts{abook} );
151 &pref("$data_dir/$filename", $username)
152 if ( $ext eq "pref" and defined $opts{pref} );
153 &sig("$data_dir/$filename", $username)
154 if ( $ext =~ /si([g\d])$/ and defined $opts{sig});
155 }
156 closedir ( DIR );
157
158 }
159
160 # Process a user address file
161
162 sub abook {
163
164 my ($filepath, $username) = @_;
165
166 if ($verbose) { print STDERR "; PARSING ADDRESS BOOK DATA FROM: $filepath\n"; }
167
168 if ( defined $opts{delete} ) {
169 print "DELETE FROM $abookdb.$abook_table WHERE $abook_owner = '"
170 . escape_sql_string($username,'TRUE')
171 . "';\n"
172 }
173
174 open(ABOOK, "<$filepath") or
175 die "FILE READ ERROR: Could not open $filepath!!\n";
176
177 while (my $line = <ABOOK> ) {
178
179 chomp $line;
180 my ( $nickname,$firstname,$lastname,$email,$label ) = split(/\|/, $line);
181
182 print "INSERT INTO $abookdb.$abook_table "
183 . "($abook_owner, $abook_nickname, $abook_firstname, $abook_lastname, $abook_email, $abook_label) "
184 . "VALUES ('"
185 . escape_sql_string($username) . "', '"
186 . escape_sql_string($nickname) . "', '"
187 . escape_sql_string($firstname) . "', '"
188 . escape_sql_string($lastname) . "', '"
189 . escape_sql_string($email) . "', '"
190 . escape_sql_string($label) . "');\n";
191 }
192
193 close(ABOOK);
194 }
195
196 # Process a user preference file
197 sub pref {
198
199 my ($filepath, $username) = @_;
200
201 if ($verbose) { print STDERR "; PARSING PREFERENCE DATA FROM: $filepath\n"; }
202
203 if ( defined $opts{delete} ) {
204 print "DELETE FROM $prefdb.$pref_table "
205 . "WHERE $pref_user = '"
206 . escape_sql_string($username,'TRUE')
207 . "' AND $pref_key NOT LIKE '\\_\\_\\_sig%\\_\\_\\_';\n"
208 }
209
210 open(PREFS, "<$filepath") or
211 die "FILE READ ERROR: Could not open $filepath!!\n";
212
213 while (my $line = <PREFS> ) {
214
215 chomp $line;
216 my ( $prefkey, $prefval ) = split(/=/, $line, 2);
217
218 print "INSERT INTO $prefdb.$pref_table "
219 . "($pref_user, $pref_key, $pref_value) "
220 . "VALUES ('"
221 . escape_sql_string($username) . "', '"
222 . escape_sql_string($prefkey) . "', '"
223 . escape_sql_string($prefval) . "');\n";
224
225 }
226
227 close(PREFS);
228 }
229
230 # Process a user signature file
231 sub sig {
232
233 my ($filepath, $username) = @_;
234
235 if ($verbose) { print STDERR "; PARSING SIGNATURE DATA FROM: $filepath\n"; }
236
237 my $del_ext = $1;
238 $del_ext = "nature" if ( $del_ext eq "g" );
239
240 if ( defined $opts{delete} ) {
241 print "DELETE FROM $prefdb.$pref_table "
242 . "WHERE $pref_user = '"
243 . escape_sql_string($username,'TRUE')
244 . "' AND $pref_key = '___sig"
245 . escape_sql_string($del_ext,'TRUE')
246 . "___';\n"
247 }
248
249 open(SIG, "<$filepath") or
250 die "FILE READ ERROR: Could not open $filepath!!\n";
251
252 my @lines = <SIG>;
253 close(SIG);
254
255 $filepath =~ /.*\.si([g,\d])$/;
256 my $prefkey = "___sig";
257 if ( $1 eq "g" ) {
258 $prefkey .= "nature___";
259 } else {
260 $prefkey .= "$1___";
261 }
262
263 print "INSERT INTO $prefdb.$pref_table ($pref_user, $pref_key, $pref_value) "
264 . "VALUES ('"
265 . escape_sql_string($username) . "', '"
266 . escape_sql_string($prefkey) . "', '"
267 . escape_sql_string(join("", @lines)) . "');\n";
268 }
269
270 # Escapes sql strings
271 # MySQL escaping:
272 # http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html
273 # full - \x00 (null), \n, \r, \, ', " and \x1a (Control-Z)
274 # add % and _ in pattern matching expressions.
275 # short - only character used for quoting and backslash should be escaped
276 # PostgreSQL
277 # Oracle
278 # Sybase - different quoting of '
279 sub escape_sql_string() {
280 my ($str,$isPattern) = @_;
281
282 if ($dbtype eq 'mysql'){
283 # escape \, ' and "
284 $str =~ s/(['"\\])/\\$1/g;
285 # escape \x1a
286 $str =~ s/([\x1a])/\\Z/g;
287 # escape ascii null
288 $str =~ s/([\x00])/\\0/g;
289 # escape line feed
290 $str =~ s/([\n])/\\n/g;
291 # escape cr
292 $str =~ s/([\r])/\\r/g;
293 if ($isPattern) {
294 $str =~ s/([%_])/\\$1/g;
295 }
296 } else {
297 die "ERROR: Unsupported database type";
298 }
299 return $str;
300 }
301
302
303 # Print out the usage screen
304 sub Usage {
305
306 $0 =~ /.*\/(.*)/;
307 my $prog = $1;
308 print <<EOL;
309
310 This program generates SQL statements that aid in importing
311 SquirrelMail user configuration settings from files to
312 those in a database.
313
314 WARNING: this script is experimental. We recommend that
315 you not use it when logged in as a privileged user (such
316 as root). Also, ALWAYS back up your data directory before
317 using this script.
318
319 Usage: $prog --data_dir=<path to data directory> \\
320 [--delete] \\
321 [--abook] [--sig] [--pref] \\
322 [--user=<username1[,username2[,username3]...]>] \\
323 [--abookdb=<database>] \\
324 [--abook_table=<table name>] \\
325 [--abook_owner=<field name>] \\
326 [--abook_nickname=<field name>] \\
327 [--abook_firstname=<field name>] \\
328 [--abook_lastname=<field name>] \\
329 [--abook_email=<field name>] \\
330 [--abook_label=<field name>] \\
331 [--prefdb=<database>] \\
332 [--pref_table=<table name>] \\
333 [--pref_user=<field name>] \\
334 [--pref_key=<field name>] \\
335 [--pref_value=<field name>] \\
336 [--verbose] [-v]
337 [--help] [-h]
338
339 When none of --abook, --sig or --pref is specified, all three
340 will be assumed.
341
342 If --user is not specified, data for all users will be extracted.
343
344 --data_dir is not optional and must define the path to the
345 SquirrelMail data directory. If it is not given,
346 this help message is displayed.
347
348 --delete causes the inclusion of SQL statements that remove all
349 previous setting values from the database for each user.
350 This setting obeys --user, --abook, --pref and --sig.
351 This setting is useful when re-importing settings.
352
353 --abook causes the inclusion of SQL statements that import user
354 address book data.
355
356 --sig causes the inclusion of SQL statements that import user
357 (email) signature data.
358
359 --pref causes the inclusion of SQL statements that import all
360 other general user preference data.
361
362 --user can be used to limit the users for which to extract data.
363 One or more (comma-separated) usernames can be given.
364
365 --abookdb can be used to specify a custom database name for the
366 address book database. If not given, "squirrelmail"
367 is used.
368
369 --abook_table can be used to specify a custom address book table
370 name. If not given, "address" is used.
371
372 --abook_owner can be used to specify a custom field name for the
373 "owner" field in the address book database table
374 (the username goes in this field). If not given,
375 "owner" is used.
376
377 --abook_nickname can be used to specify a custom field name for the
378 "nickname" field in the address book database table.
379 If not given, "nickname" is used.
380
381 --abook_firstname can be used to specify a custom field name for the
382 "firstname" field in the address book database table.
383 If not given, "firstname" is used.
384
385 --abook_lastname can be used to specify a custom field name for the
386 "lastname" field in the address book database table.
387 If not given, "lastname" is used.
388
389 --abook_email can be used to specify a custom field name for the
390 email field in the address book database table
391 (the actual email address goes in this field). If
392 not given, "email" is used.
393
394 --abook_label can be used to specify a custom field name for the
395 "label" field in the address book database table.
396 If not given, "label" is used.
397
398 --prefdb can be used to specify a custom database name for the
399 user preferences database. If not given, "squirrelmail"
400 is used.
401
402 --pref_table can be used to specify a custom preference table
403 name. If not given, "userprefs" is used.
404
405 --pref_user can be used to specify a custom field name for the
406 "user" field in the preferences database table
407 (the username goes in this field). If not given,
408 "user" is used.
409
410 --pref_key can be used to specify a custom field name for the
411 key field in the preferences database table (the
412 preference name goes in this field). If not given,
413 "prefkey" is used.
414
415 --pref_value can be used to specify a custom field name for the
416 value field in the preferences database table
417 (the preference value goes in this field). If not
418 given, "prefval" is used.
419
420 --verbose Displays extra diagnostic output on STDERR. If you
421 redirect standard output to a file, verbose output
422 will not interfere with other normal output.
423
424 -v Same as --verbose.
425
426 --help Displays this usage information.
427
428 -h Same as --help.
429
430 EOL
431 exit 1;
432 }