209b9127 |
1 | #!/usr/bin/perl |
c47b981d |
2 | # |
980c6231 |
3 | # Converts file-based preferences into SQL statements. |
208870cf |
4 | # |
980c6231 |
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. |
208870cf |
9 | # |
c47b981d |
10 | # Copyright (c) 2002, Michael Blandford and Tal Yardeni |
c997cbe6 |
11 | # Copyright (c) 2005-2021 The SquirrelMail Project Team |
0575fe8b |
12 | # |
980c6231 |
13 | # This script is licensed under the GNU Public License (GPL). |
14 | # See: http://opensource.org/licenses/gpl-license.php |
c47b981d |
15 | # $Id$ |
208870cf |
16 | # |
209b9127 |
17 | |
208870cf |
18 | ##### Default values ##### |
980c6231 |
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"; |
209b9127 |
34 | ##### ##### ##### |
35 | |
36 | use Getopt::Long; |
37 | |
980c6231 |
38 | my (%opts, $verbose, $data_dir); |
209b9127 |
39 | |
980c6231 |
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} ); |
209b9127 |
43 | |
c47b981d |
44 | unless ( defined $opts{abook} or defined $opts{pref} or defined $opts{sig}) { |
980c6231 |
45 | $opts{abook}='TRUE'; |
46 | $opts{pref}='TRUE'; |
47 | $opts{sig}='TRUE'; |
209b9127 |
48 | } |
49 | |
208870cf |
50 | |
980c6231 |
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}; |
208870cf |
59 | } |
60 | if ( defined $opts{pref_table} and $opts{pref_table} ) { |
61 | $pref_table = $opts{pref_table}; |
62 | } |
980c6231 |
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 | } |
208870cf |
72 | if ( defined $opts{abook_table} and $opts{abook_table}) { |
73 | $abook_table = $opts{abook_table}; |
74 | } |
980c6231 |
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 | } |
208870cf |
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 | } |
209b9127 |
100 | |
101 | # Are we looking for specific users or all users? |
102 | # There has to be a better way to do this - Below |
980c6231 |
103 | my @user_list = split ( /,/, $opts{user} ) if defined $opts{user}; |
104 | |
105 | inspect_files($data_dir); |
209b9127 |
106 | |
980c6231 |
107 | # All done. Below are functions |
209b9127 |
108 | |
209b9127 |
109 | |
980c6231 |
110 | # Finds needed user files in the given directory |
111 | # and recurses any nested data directories as needed |
112 | # |
113 | sub inspect_files { |
209b9127 |
114 | |
980c6231 |
115 | my ($data_dir) = @_; |
116 | my ($filename, $username, $ext); |
117 | local *DIR; |
209b9127 |
118 | |
980c6231 |
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 | } |
209b9127 |
159 | |
160 | # Process a user address file |
161 | |
162 | sub abook { |
209b9127 |
163 | |
980c6231 |
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"; |
209b9127 |
176 | |
177 | while (my $line = <ABOOK> ) { |
178 | |
179 | chomp $line; |
180 | my ( $nickname,$firstname,$lastname,$email,$label ) = split(/\|/, $line); |
181 | |
980c6231 |
182 | print "INSERT INTO $abookdb.$abook_table " |
183 | . "($abook_owner, $abook_nickname, $abook_firstname, $abook_lastname, $abook_email, $abook_label) " |
1c6b3a71 |
184 | . "VALUES ('" |
980c6231 |
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"; |
209b9127 |
191 | } |
192 | |
193 | close(ABOOK); |
194 | } |
195 | |
1c6b3a71 |
196 | # Process a user preference file |
209b9127 |
197 | sub pref { |
209b9127 |
198 | |
980c6231 |
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"; |
209b9127 |
212 | |
213 | while (my $line = <PREFS> ) { |
214 | |
215 | chomp $line; |
980c6231 |
216 | my ( $prefkey, $prefval ) = split(/=/, $line, 2); |
209b9127 |
217 | |
980c6231 |
218 | print "INSERT INTO $prefdb.$pref_table " |
219 | . "($pref_user, $pref_key, $pref_value) " |
1c6b3a71 |
220 | . "VALUES ('" |
980c6231 |
221 | . escape_sql_string($username) . "', '" |
222 | . escape_sql_string($prefkey) . "', '" |
223 | . escape_sql_string($prefval) . "');\n"; |
209b9127 |
224 | |
225 | } |
226 | |
227 | close(PREFS); |
228 | } |
229 | |
1c6b3a71 |
230 | # Process a user signature file |
209b9127 |
231 | sub sig { |
232 | |
980c6231 |
233 | my ($filepath, $username) = @_; |
234 | |
235 | if ($verbose) { print STDERR "; PARSING SIGNATURE DATA FROM: $filepath\n"; } |
236 | |
237 | my $del_ext = $1; |
209b9127 |
238 | $del_ext = "nature" if ( $del_ext eq "g" ); |
209b9127 |
239 | |
980c6231 |
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"; |
209b9127 |
251 | |
252 | my @lines = <SIG>; |
253 | close(SIG); |
254 | |
980c6231 |
255 | $filepath =~ /.*\.si([g,\d])$/; |
256 | my $prefkey = "___sig"; |
209b9127 |
257 | if ( $1 eq "g" ) { |
258 | $prefkey .= "nature___"; |
259 | } else { |
260 | $prefkey .= "$1___"; |
261 | } |
262 | |
980c6231 |
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"; |
209b9127 |
268 | } |
269 | |
1c6b3a71 |
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 |
980c6231 |
288 | $str =~ s/([\x00])/\\0/g; |
1c6b3a71 |
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 | } |
209b9127 |
301 | |
1c6b3a71 |
302 | |
303 | # Print out the usage screen |
209b9127 |
304 | sub Usage { |
305 | |
306 | $0 =~ /.*\/(.*)/; |
980c6231 |
307 | my $prog = $1; |
209b9127 |
308 | print <<EOL; |
208870cf |
309 | |
980c6231 |
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. |
209b9127 |
429 | |
430 | EOL |
431 | exit 1; |
432 | } |