| 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-2019 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 | } |