从Codeigniter备份还原数据库

Question

I was trying to make an automated database backup using built-in CodeIgniter $this->dbutil->backup().

The script is:

    $this->load->dbutil();

    $backup = $this->dbutil->backup(array(
        'tables'        => array(),   // Array of tables to backup.
        'ignore'        => array('regencies', 'villages', 'provinces'),                     // List of tables to omit from the backup
        'add_drop'      => TRUE,                        // Whether to add DROP TABLE statements to backup file
        'add_insert'    => TRUE,                        // Whether to add INSERT data to backup file
        'newline'       => "
"                         // Newline character used in backup file
    ));

    $this->load->helper('file');

    $latest = md5(uniqid());

    write_file(APPPATH . 'backup/'. $latest .'.gz', $backup);

The scripts run just fine. But the problem comes when i wan't to restore. It comes like this:

As a result of that i cannot restore it from CLI (mysql -u root -p dbname < db.sql) and from Navicat/Sequel Pro.

The question is, how to restore that it to database again? (Notice that there's no quot after VALUES)

Answer 1

This is a simple SQL syntax issue. String literals need to be enclosed in single quotes.

INSERT INTO `foo` (`bar`,`bell`) VALUES ( Hey diddle diddle , cat and fiddle )

won't work. We need

INSERT INTO `foo` (`bar`,`bell`) VALUES ('Hey diddle diddle','cat and fiddle')
                                         ^-----------------^ ^--------------^

Take a closer look at the actual SQL statement you are trying to execute.

Your "unloader" is going to need to be smarter, enclosing string and date values in single quotes when they get put into a SQL statement. (For convenience, numeric literals can be enclosed in single quotes as well, so you don't need to discriminate, just enclose all values in the VALUES list in single quotes.

Also be aware of what will happen when a value includes a single quote. For example:

 ... VALUES ( 'O'Leary' , ... 
              ^_^     

That's also going to be an error. Single quotes within a value will need to be escaped. One way to do that in MySQL is to precede a single quote with another single quote, like this:

 ... VALUES ( 'O''Leary' , ... 
              ^--------^

This would be valid, and would evaluate to a string containing a single single quote O'Leary

Also, MySQL will interpret a backslash character in a string literal as an escape character. For example:

 ... VALUES ( 'this probably 
ot what we want' , 
                             ^^       

That sequence is going to interpreted as a newline character, not as the character n. So, backslash characters are also going to need to be escaped.

et al.