2

I need to get the results of the command line function .dump from an sqlite3 database, but through PHP. Specifically, I am using Zend_Db.

Is it possible to call the .dump command through the database adapter? If not, is there a work around, that is simpler than pulling each table schema, each row and write my own output?

I have tried simply running .dump as a query, but I get a general syntax error.

Sosy
  • 173
  • 2
  • 8
  • 1
    It's not pretty, but you could potentially run the command line client from PHP via `exec()`. In the case of mysql and postgres, there isn't really a PHP equivalent of the dump operation, so you're pretty much stuck invoking the CLI client. – Frank Farmer Jun 03 '11 at 00:26
  • Ok, thanks for the info. I was hoping to avoid using the command line, but that may not be possible for now. – Sosy Jun 03 '11 at 01:51

2 Answers2

3

The SQLite3 .dump command is part of the command shell, and not part of the database library itself.

See the section Special commands to sqlite3 on the page Command Line Shell For SQLite

The only way you can do this is via PHP exec()

Noah
  • 15,080
  • 13
  • 104
  • 148
0

I released a code snippet because I wanted to do exactly this, and noone had released a premade solution: https://www.ephestione.it/dump-sqlite-database-to-sql-with-php-alone/

<?php

$db = new SQLite3(dirname(__FILE__)."/your/db.sqlite");
$db->busyTimeout(5000);

$sql="";

$tables=$db->query("SELECT name FROM sqlite_master WHERE type ='table' AND name NOT LIKE 'sqlite_%';");

while ($table=$tables->fetchArray(SQLITE3_NUM)) {
    $sql.=$db->querySingle("SELECT sql FROM sqlite_master WHERE name = '{$table[0]}'").";\n\n";
    $rows=$db->query("SELECT * FROM {$table[0]}");
    $sql.="INSERT INTO {$table[0]} (";
    $columns=$db->query("PRAGMA table_info({$table[0]})");
    $fieldnames=array();
    while ($column=$columns->fetchArray(SQLITE3_ASSOC)) {
        $fieldnames[]=$column["name"];
    }
    $sql.=implode(",",$fieldnames).") VALUES";
    while ($row=$rows->fetchArray(SQLITE3_ASSOC)) {
        foreach ($row as $k=>$v) {
            $row[$k]="'".SQLite3::escapeString($v)."'";
        }
        $sql.="\n(".implode(",",$row)."),";
    }
    $sql=rtrim($sql,",").";\n\n";
}
file_put_contents("sqlitedump.sql",$sql);
ephestione
  • 43
  • 9
  • Please note that this solution is vulnerable to SQL injection. You should use prepared statements. – Dharman Oct 02 '19 at 11:40
  • 1
    @Dharman this solution I use to backup a local system to a local location with a local request source, and without the possibility for an attacker to access the file, that's how I intend its usage... could you please make an example of SQL injection attack on this script? Please note that user input is not taken into consideration anywhere in the code. – ephestione Oct 02 '19 at 17:55
  • I didn't say anything about user input. SQL injection does not imply that someone is doing it on purpose. You can do it to yourself. Never trust any input even if it is your own. – Dharman Oct 02 '19 at 18:03
  • What if your table/column name contains a special character, what if one of your values in the DB is a valid PHP escape character? e.g. `\n`. I have done that in the past and I have been there. Believe me, creating such bespoke solution will come to bite you sooner or later. – Dharman Oct 02 '19 at 19:17
  • 1
    I understand you "phylosophy" as clearly stated in your profile on stackexchange, yet you are forcing this in my opinion. In this specific case, there is `SQLite3::escapeString($v)` in place to think about that. Still, seriously, I would love an example. A clean, real world case, with real values in it, and possible bad outcomes :) This is still, at the time of writing and to the best of my knowledge, the only solution to "dump SQLite database with pure PHP" available on the internet :P I know because I searched a lot before spending time in writing this :D – ephestione Oct 03 '19 at 09:46
  • Here is an example: `SQLite3::escapeString('\n')`. `escapeString` doesn't help with such cases. You would need to escape it twice yourself. – Dharman Oct 03 '19 at 09:52
  • If you have a question whether this approach is safe or not, you should ask another question on Stack Overflow. The chat section is not designed for long discussions. – Dharman Oct 03 '19 at 09:54
  • @Dharman See https://stackoverflow.com/questions/58225998/vulnerability-to-sql-injection-even-when-sqlite3escapestring-is-used-and-no – Barmar Oct 03 '19 at 19:52