SQLite Forum

Perl backup of database
There are several possible improvements here:

1. You don't need the pipe: the `sqlite3` command will also take SQL and shell commands as parameters after any flags and the database name.

2. We can get rid of the stdout redirection by using Perl's built-in I/O redirection.

3. ...which then means we don't need to use `system()`, which is dangerous when used with variables if their content can come from user-provided sources, since `system()` invokes the shell, so you're subject to complicated parsing rules over user-provided data.

All of that together gives an alternative like this:

die "No such backup directory $TARDIR!\n" unless -d $TARDIR;
my $bfile = "$TARDIR/$Today.sql";
open my $backup, '>', $bfile or die "Cannot create backup file $bfile: $!\n";
select $bfile;   # send STDOUT to backup file
my $dfile = "$SRCDIR/kba.db";
open my $cmd, '|-', '/usr/bin/sqlite3', $dfile, '.dump'
       or die "Failed to dump DB $dfile: $!\n";
close $cmd or die "Backup to $dfile failed: $!\n";
select STDOUT;   # restore STDOUT redirect

This has several non-obvious virtues not already called out above:

* This is many more lines of code, but it's safer and allows a lot more error checking and diagnosis, all made possible because we're breaking the process up into smaller steps, so we can check each one separately.

* Explicitly passing the path to the `sqlite3` binary is safer than searching for it in the `PATH`, so that should be done wherever practical. If someone can put a `sqlite3` executable into your `PATH` ahead of the one you expected to run, they take over all running permissions of your script.

* Use of the `LIST` form of `open` means the call to `sqlite3` cannot fail due to spaces or quotes in file names. It can also prevent games like splitting commands by injecting a semicolon into one of the variables. Consider:

        $SRCDIR = "./junk; rm ~/.profile ; touch ";

* Despite its complexity, it is more efficient, not requiring a call to an external shell. And on primitive systems, it avoids a call to `/bin/echo` as well.