3 # Converts file-based preferences into SQL statements.
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
10 # Copyright (c) 2002, Michael Blandford and Tal Yardeni
11 # Copyright (c) 2005-2018 The SquirrelMail Project Team
13 # This script is licensed under the GNU Public License (GPL).
14 # See: http://opensource.org/licenses/gpl-license.php
18 ##### Default values #####
19 # TODO: expose the database type as a CLI option, but first need more sections in sub escape_sql_string()
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";
38 my (%opts, $verbose, $data_dir);
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) );
42 &Usage
if ( defined $opts{h
} or defined $opts{help
} );
44 unless ( defined $opts{abook
} or defined $opts{pref
} or defined $opts{sig
}) {
51 if ( defined $opts{verbose
} or defined $opts{v
} ) {
54 if ( defined $opts{abookdb
} and $opts{abookdb
} ) {
55 $abookdb = $opts{abookdb
};
57 if ( defined $opts{prefdb
} and $opts{prefdb
} ) {
58 $prefdb = $opts{prefdb
};
60 if ( defined $opts{pref_table
} and $opts{pref_table
} ) {
61 $pref_table = $opts{pref_table
};
63 if ( defined $opts{pref_user
} and $opts{pref_user
} ) {
64 $pref_user = $opts{pref_user
};
66 if ( defined $opts{pref_key
} and $opts{pref_key
} ) {
67 $pref_key = $opts{pref_key
};
69 if ( defined $opts{pref_value
} and $opts{pref_value
} ) {
70 $pref_value = $opts{pref_value
};
72 if ( defined $opts{abook_table
} and $opts{abook_table
}) {
73 $abook_table = $opts{abook_table
};
75 if ( defined $opts{abook_owner
} and $opts{abook_owner
} ) {
76 $abook_owner = $opts{abook_owner
};
78 if ( defined $opts{abook_nickname
} and $opts{abook_nickname
} ) {
79 $abook_nickname = $opts{abook_nickname
};
81 if ( defined $opts{abook_firstname
} and $opts{abook_firstname
} ) {
82 $abook_firstname = $opts{abook_firstname
};
84 if ( defined $opts{abook_lastname
} and $opts{abook_lastname
} ) {
85 $abook_lastname = $opts{abook_lastname
};
87 if ( defined $opts{abook_email
} and $opts{abook_email
} ) {
88 $abook_email = $opts{abook_email
};
90 if ( defined $opts{abook_label
} and $opts{abook_label
} ) {
91 $abook_label = $opts{abook_label
};
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
};
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
};
105 inspect_files
($data_dir);
107 # All done. Below are functions
110 # Finds needed user files in the given directory
111 # and recurses any nested data directories as needed
116 my ($filename, $username, $ext);
120 # If no arguments are passed, and we cant open the dir, we should
122 opendir(DIR
, $data_dir) or
123 die "DIRECTORY READ ERROR: Could not open $data_dir!!\n";
125 while ( $filename = readdir DIR
) {
127 next if ( $filename eq "." or $filename eq ".." );
129 if ($verbose) { print STDERR
"; INSPECTING: $data_dir/$filename\n"; }
131 # recurse into nested (hashed) directories
133 if ($filename =~ /^[0123456789abcdef]$/ && -d
"$data_dir/$filename") {
134 inspect_files
("$data_dir/$filename");
137 next unless $filename =~ /(.*)\.(.*)/;
139 next unless $username;
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));
145 # Deal with the extension files
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
});
160 # Process a user address file
164 my ($filepath, $username) = @_;
166 if ($verbose) { print STDERR
"; PARSING ADDRESS BOOK DATA FROM: $filepath\n"; }
168 if ( defined $opts{delete} ) {
169 print "DELETE FROM $abookdb.$abook_table WHERE $abook_owner = '"
170 . escape_sql_string
($username,'TRUE')
174 open(ABOOK
, "<$filepath") or
175 die "FILE READ ERROR: Could not open $filepath!!\n";
177 while (my $line = <ABOOK
> ) {
180 my ( $nickname,$firstname,$lastname,$email,$label ) = split(/\|/, $line);
182 print "INSERT INTO $abookdb.$abook_table "
183 . "($abook_owner, $abook_nickname, $abook_firstname, $abook_lastname, $abook_email, $abook_label) "
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";
196 # Process a user preference file
199 my ($filepath, $username) = @_;
201 if ($verbose) { print STDERR
"; PARSING PREFERENCE DATA FROM: $filepath\n"; }
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"
210 open(PREFS
, "<$filepath") or
211 die "FILE READ ERROR: Could not open $filepath!!\n";
213 while (my $line = <PREFS
> ) {
216 my ( $prefkey, $prefval ) = split(/=/, $line, 2);
218 print "INSERT INTO $prefdb.$pref_table "
219 . "($pref_user, $pref_key, $pref_value) "
221 . escape_sql_string
($username) . "', '"
222 . escape_sql_string
($prefkey) . "', '"
223 . escape_sql_string
($prefval) . "');\n";
230 # Process a user signature file
233 my ($filepath, $username) = @_;
235 if ($verbose) { print STDERR
"; PARSING SIGNATURE DATA FROM: $filepath\n"; }
238 $del_ext = "nature" if ( $del_ext eq "g" );
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')
249 open(SIG
, "<$filepath") or
250 die "FILE READ ERROR: Could not open $filepath!!\n";
255 $filepath =~ /.*\.si([g,\d])$/;
256 my $prefkey = "___sig";
258 $prefkey .= "nature___";
263 print "INSERT INTO $prefdb.$pref_table ($pref_user, $pref_key, $pref_value) "
265 . escape_sql_string
($username) . "', '"
266 . escape_sql_string
($prefkey) . "', '"
267 . escape_sql_string
(join("", @lines)) . "');\n";
270 # Escapes sql strings
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
278 # Sybase - different quoting of '
279 sub escape_sql_string
() {
280 my ($str,$isPattern) = @_;
282 if ($dbtype eq 'mysql'){
284 $str =~ s/(['"\\])/\\$1/g;
286 $str =~ s/([\x1a])/\\Z/g;
288 $str =~ s/([\x00])/\\0/g;
290 $str =~ s/([\n])/\\n/g;
292 $str =~ s/([\r])/\\r/g;
294 $str =~ s/([%_])/\\$1/g;
297 die "ERROR: Unsupported database type";
303 # Print out the usage screen
310 This program generates SQL statements that aid in importing
311 SquirrelMail user configuration settings from files to
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
319 Usage: $prog --data_dir=<path to data directory> \\
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>] \\
339 When none of --abook, --sig or --pref is specified, all three
342 If --user is not specified, data for all users will be extracted.
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.
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.
353 --abook causes the inclusion of SQL statements that import user
356 --sig causes the inclusion of SQL statements that import user
357 (email) signature data.
359 --pref causes the inclusion of SQL statements that import all
360 other general user preference data.
362 --user can be used to limit the users for which to extract data.
363 One or more (comma-separated) usernames can be given.
365 --abookdb can be used to specify a custom database name for the
366 address book database. If not given, "squirrelmail"
369 --abook_table can be used to specify a custom address book table
370 name. If not given, "address" is used.
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,
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.
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.
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.
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.
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.
398 --prefdb can be used to specify a custom database name for the
399 user preferences database. If not given, "squirrelmail"
402 --pref_table can be used to specify a custom preference table
403 name. If not given, "userprefs" is used.
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,
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,
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.
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.
424 -v Same as --verbose.
426 --help Displays this usage information.