Restoring a MySQL database in PHP via client upload (.sql file)

Question

Alright, so I've successfully been able to take a database created in PHPMyAdmin, and back it up to the client with php; now I'm trying to restore that database as a new database within the same environment.

I've found the command that works within mysql, but I can't seem to get it to work with a file that the client uploads from their computer.

Here is the code I have right now:

<?php
    // display form if user has not clicked submit
    if (!isset($_POST["btn_submit"])) 
    {
?>

<!--This will be the form that will hold the information of the entire page.-->
<form enctype="multipart/form-data"class="elegant-aero" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
    <h1>Restore Database</h1>
    <p>
        <label>
            <span>Database File</span>
            <input type="file" name="backupFile" value="Upload File">
        </label>

        <!--Submit Button-->
        <label>
            <span>&nbsp;</span>
            <input type="submit" name="btn_submit" class="button" value="Add Scenario"/>
        </label>
    </p>
</form>

<?php

    } //end if

    else 
    {
        if(isset($_FILES['backupFile']['error']) && UPLOAD_ERR_OK == $_FILES['backupFile']['error'])
        {
            //Format sql file
            $file = addslashes(file_get_contents($_FILES['backupFile']['tmp_name']));
        } //end if

        //Set up the MySQL Server
        $servername = "localhost";
        $username = "root";
        $password = "password";//hide your password
        $dbname = "test";

        //Create connection to the MySQL server
        $conn = new mysqli($servername, $username, $password);

        $sql1 = "CREATE DATABASE test"; 

        if($conn->query($sql1) === TRUE) 
        {
            echo "Database created successfully<br>"; 
        } else {
            echo "Error creating database: " . $conn->error; 
        } //end else

        $sql2 = "mysql --user={$username} --password={$password} --database={$dbname} < $file";

        if($conn->query($sql2) === TRUE) 
        {
            echo "Information uploaded successfully<br>"; 
        } else {
            echo "Error uploading information: " . $conn->error; 
        } //end else

    } //end else

?>

You are vulnerable to [sql injection attacks](http://bobby-tables.com) (`addslashes()` is **NOT** a defence), and also vulnerable to shell injection attacks, as again, `addslashes()` is NOT a defence. — Marc B, Jul 02 '15 at 21:19

score 1 · Accepted Answer · answered Jul 02 '15 at 21:22

1

There are a bunch of problems with your code (Where does fileScenDetails come from? What happened to backupFile in the form?), but per your question:

   $sql2 = "mysql --user={$username} --password={$password} --database={$dbname} < $file";

    if($conn->query($sql2) === TRUE) 
    {
        echo "Information uploaded successfully<br>"; 
    } else {
        echo "Error uploading information: " . $conn->error; 
    } //end else

You're trying to run a *nix command as a database query. Instead of:

if($conn->query($sql2) === TRUE)

You'd need to do something like:

if(exec($sql2) === TRUE)

You really need to read up on what you're trying to do here, as is it really dangerous, and shouldn't be attempted by beginners.

answered Jul 02 '15 at 21:22

FlipperPA

13,607
4
39
71

My bad, fileScenDetails is supposed to be backupFile. I've been researching this all day and that nix command is the only thing I could find. – Michael Anthony Leber Jul 02 '15 at 21:28
Ok FlipperPA I've tried that fix and it's defaulting to my error command; but I only know how to get errors from mysqli ($conn->error); how do I grab the errors from this exec command? – Michael Anthony Leber Jul 02 '15 at 21:37
You can use PHP's 'echo' command to see what you are actually running: `echo $sql2;` Then try running what is echo'd at the command line, and you should see the error occurring. Again, I would strongly recommend reading up on this; the potential for someone to exploit a security flaw is very high. Good luck. – FlipperPA Jul 03 '15 at 09:07
Ok, I found the ability to use an optional parameter in the exec command to save the output into a php variable; and then I used another command called var_dump to output that information. The result I get is this: "array(0) { }" Any idea what that means? – Michael Anthony Leber Jul 06 '15 at 13:41
After researching more, I've decided to use the first answer from this solution, replacing their file name variable with $_FILES['backupFile']['tmp_name'] and it works: http://stackoverflow.com/questions/19751354/how-to-import-sql-file-in-mysql-database-using-php – Michael Anthony Leber Jul 06 '15 at 15:00

Restoring a MySQL database in PHP via client upload (.sql file)

1 Answers1