Issue Overview: Accidental Database Overwrite During PHP File Updates

When migrating a PHP-based web application from MariaDB to SQLite, a critical operational challenge arises: accidental overwrites of the SQLite database file(s) during routine code deployments. Unlike MariaDB, which operates as a separate service with its data stored in a managed directory structure outside the web server’s document root, SQLite relies on direct file access. This creates a vulnerability when deploying new PHP files via scripts or manual updates. If the SQLite database file (e.g., app.db) and its associated write-ahead log (WAL) files (app.db-wal, app.db-shm) reside in the same directory as the PHP application code, standard file replacement workflows (e.g., rsync, scp, or ZIP extraction) can inadvertently overwrite or delete the database. This risk is exacerbated in environments where deployment tooling lacks granular control over file exclusion or where directory structures conflate code and data storage.

The core problem revolves around three interrelated factors:

1. File System Co-Location: SQLite databases stored within the web server’s document root (e.g., /var/www/html/) or nested within PHP application directories.
2. Deployment Workflow Design: Deployment scripts or tools that blindly overwrite entire directories without preserving existing non-code files.
3. Configuration Rigidity: Hardcoded database file paths in PHP code, preventing dynamic adjustment for different environments (development, staging, production).

This issue is particularly acute in scenarios where developers:

• Use version control systems (e.g., Git) to manage both code and database files, leading to conflicts during pulls or checkouts.
• Rely on simplistic deployment pipelines that mirror entire project directories without file exclusion rules.
• Test database changes directly in production-like environments without proper isolation.

Possible Causes: Why SQLite Databases Get Overwritten in PHP Deployments

1. Web Server Document Root Misconfiguration
SQLite databases stored within the web server’s document root (e.g., /var/www/html/db/app.db) are vulnerable to overwrites during code deployments. PHP applications often default to relative paths for database connections (e.g., sqlite:./db/app.db), creating a false assumption that the database should reside alongside application code. This practice exposes the database to:

• File Replacement: Deployment tools replacing the entire db directory with a fresh copy from a repository or build system.
• Permission Resets: File ownership and permission changes during deployments that render the database unreadable or unwritable by the web server process.
• Accidental Deletion: Scripts using rm -rf *-style commands in the document root during cleanup phases.

2. Insufficient Environment Separation
Developers often use identical database filenames across environments (e.g., app.db in both development and production). When deployment scripts synchronize files between environments without path adjustments, the production database becomes a overwrite target. For example, a deployment script might sync /home/dev/project/db/app.db to /var/www/html/db/app.db, overwriting the production database with development data.

3. Lack of Deployment Pipeline Safeguards
CI/CD pipelines that lack explicit rules to preserve SQLite files contribute to accidental overwrites. Common oversights include:

• Unfiltered File Transfers: Using rsync --delete without excluding *.db, *-wal, or *-shm files.
• Missing Pre/Post-Deployment Hooks: Failing to execute database backup scripts before file replacements.
• Environment-Agnostic Builds: Generating deployment packages (e.g., ZIP files) that include placeholder or test databases, which then overwrite production instances.

4. SQLite Write-Ahead Log (WAL) File Proliferation
SQLite’s WAL mode creates auxiliary files (*.db-wal, *.db-shm) that are often overlooked in deployment configurations. If these files are not excluded during deployments, they can trigger file locking issues or cause the database to enter an inconsistent state. For example, overwriting app.db while app.db-wal remains from a previous deployment may corrupt the database.

5. Hardcoded Database Paths in Application Code
PHP applications that hardcode database paths (e.g., $db = new SQLite3('/var/www/html/db/app.db');) create brittleness. During deployments, even if the database is moved, the old path remains referenced in the code, leading to:

• Silent Database Reinitialization: The application creating a new, empty database at the hardcoded path if the original is missing.
• Permission Conflicts: Newly created database files inheriting incorrect ownership (e.g., root:root instead of www-data:www-data).

Troubleshooting Steps, Solutions & Fixes: Securing SQLite Databases in PHP Deployment Workflows

Step 1: Relocate the SQLite Database Outside the Web Server’s Document Root
Move the SQLite database and its auxiliary files to a directory inaccessible via HTTP requests and isolated from deployment operations.

• Linux/Unix Systems:

  sudo mkdir -p /var/lib/mysite/db  
  sudo chown -R www-data:www-data /var/lib/mysite/db  
  sudo chmod 750 /var/lib/mysite/db  
  

  Update PHP database connection code to reference the new path:

  $db_path = '/var/lib/mysite/db/app.db';  
  $db = new SQLite3($db_path);  
  

• Windows Systems:
  Store the database in a directory like C:\ProgramData\MySite\db, ensuring the IIS or Apache service account has modify permissions.

Step 2: Implement Environment-Specific Database Configuration
Use environment variables or configuration files to decouple database paths from application code, enabling distinct paths per environment.

• PHP Environment Variable Example:

  $env = getenv('APP_ENV') ?: 'production';  
  $db_paths = [  
      'development' => '/home/dev/mysite/db/app.db',  
      'production' => '/var/lib/mysite/db/app.db'  
  ];  
  $db = new SQLite3($db_paths[$env]);  
  

• .htaccess Configuration (Apache):

  SetEnv APP_ENV production  
  SetEnv DB_PATH /var/lib/mysite/db/app.db  
  

Step 3: Harden Deployment Scripts Against Database Overwrites
Modify CI/CD pipelines and deployment scripts to exclude SQLite files during file transfers.

• rsync Exclusion Example:

  rsync -av --delete --exclude '*.db' --exclude '*-wal' --exclude '*-shm' /source/path/ user@prod:/var/www/html/  
  

• Git Exclusion:
  Add to .gitignore:

  *.db  
  *-wal  
  *-shm  
  

Step 4: Leverage SQLite’s Backup API for Safe Deployment Transitions
Before initiating deployments, create a hot backup of the production database using SQLite’s Online Backup API.

• PHP Backup Script:

  $production_db = new SQLite3('/var/lib/mysite/db/app.db');  
  $backup_db = new SQLite3('/var/lib/mysite/db/app.backup.db');  
  $production_db->querySingle("SELECT count(*) FROM sqlite_master"); // Force connection  
  $backup_handle = $backup_db->openBlob('main', 'backup', 1);  
  $production_db->backup($backup_handle);  
  

Step 5: Utilize Filesystem Permissions to Prevent Accidental Writes
Configure filesystem permissions to make the database directory read-only during deployments.

• Linux/Unix Lockdown:

  sudo chattr +i /var/lib/mysite/db/app.db  
  

  (Remove immutability post-deployment with sudo chattr -i /var/lib/mysite/db/app.db)

Step 6: Adopt Database Migration Tools for Schema Changes
Instead of overwriting entire databases, manage schema changes incrementally using migration tools compatible with SQLite.

• PHP-Based Migration Example (Using Phinx):

  use Phinx\Migration\AbstractMigration;  
  class AddUserTable extends AbstractMigration {  
      public function change() {  
          $table = $this->table('users');  
          $table->addColumn('email', 'string', ['limit' => 100])  
                ->addColumn('created', 'datetime')  
                ->create();  
      }  
  }  
  

  Execute migrations via CLI during deployments:

  php vendor/bin/phinx migrate -e production  
  

Step 7: Monitor and Alert on Database File Modifications
Implement filesystem monitoring to detect unauthorized database file changes.

• Linux Auditd Rules:

  sudo auditctl -w /var/lib/mysite/db/app.db -p war -k mysite_db_changes  
  

  Review logs with:

  ausearch -k mysite_db_changes  
  

Step 8: Validate Deployment Integrity with Pre-Flight Checks
Add pre-deployment verification scripts that abort the process if critical database files are at risk.

• Bash Pre-Flight Check:

  if [[ -f "/var/lib/mysite/db/app.db" ]]; then  
      echo "Production database exists. Proceeding."  
  else  
      echo "CRITICAL: Production database missing. Aborting deployment."  
      exit 1  
  fi  
  

Step 9: Implement Database Connection Pooling with Persistent Handles
Reduce the likelihood of file locking conflicts during deployments by reusing database connections.

• PHP Persistent Connection Example:

  $db = new SQLite3('file:/var/lib/mysite/db/app.db?persistent=1', SQLITE3_OPEN_READWRITE);  
  

Step 10: Establish a Disaster Recovery Protocol
Prepare for worst-case scenarios with documented recovery procedures:

1. Regular Verified Backups:

   sqlite3 /var/lib/mysite/db/app.db ".backup '/backups/app-$(date +%F).db'"  
   

2. Automated Backup Rotation:

   find /backups -name 'app-*.db' -mtime +30 -delete  
   

3. Emergency Rollback Script:

   sudo systemctl stop apache2  
   cp -v /backups/app-latest.db /var/lib/mysite/db/app.db  
   sudo systemctl start apache2  
   

By systematically addressing file location, deployment hygiene, configuration management, and recovery preparedness, PHP applications can leverage SQLite’s performance and simplicity without risking data loss during routine updates. This approach maintains the "set-and-forget" operational model familiar from MariaDB while adapting it to SQLite’s file-based paradigm.