Import .sql file via PHP upload

Question

I working on doing a file upload in-order to restore a backup table (.sql format)... The code below is for uploading the .sql file :

<form enctype="multipart/form-data" method="post">

<font style="font-size:18px;">File to import:</font><br />

<input size='30' type='file' name='filename'>

<input type="submit" name="submit" value="Upload File">
</form>

After doing a submit, the following codes are :

set_time_limit ( 0 );

   //Upload File 
    if (isset($_POST['submit'])) { 
       if (is_uploaded_file($_FILES['filename']['tmp_name'])) { 
    if (move_uploaded_file($_FILES['filename']['tmp_name'], $_FILES['filename']['name'])) {



$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$table_name = "items";

$backup_file = $_FILES['filename']['name'];

if(!file_exists($backup_file)){
    echo "File Not Exists";
}

$sql = "LOAD DATA INFILE '$backup_file' INTO TABLE $table_name";

mysql_select_db('MM_db');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not load data : ' . mysql_error());
}
echo "Loaded  data successfully\n";
mysql_close($conn);
fclose($handle); 
     }
     ?>
     <script>
     alert('Items Uploaded Successfully');
     </script>
     <?php  
exit();
       }
    }

When doing the upload , I receive this error :

Could not load data : File 'c:\wamp\bin\mysql\mysql5.5.24\data\MM_db\items.sql' not found (Errcode: 2)

Its like it did not find my items.sql file, although I've checked if its there or not by doing file_exists($backup_file), but still I'm facing this problem. I think there might be something missing with the file upload code?

Please don't do this. You've got zero access control here. If anything you should be using phpMyAdmin instead of rolling your own. Remember LOAD DATA INFILE needs to have the file on the MySQL server, not the web server. — tadman
– tadman, Commented Jun 10, 2013 at 18:09
@tadman Sorry but you are wrong, If i replace the $backup_file value into a specific file path - Example C:/items.sql And remove the whole file upload ... It will work like a charm, but I like to take things more in challenging way ! — Ali Hamra
– Ali Hamra, Commented Jun 10, 2013 at 18:11
That path has to be on the same machine as the MySQL server process though. Maybe that's the same machine in this case. — tadman
– tadman, Commented Jun 10, 2013 at 18:37

Community · Accepted Answer · 2017-05-23 11:58:02Z

LOAD DATA INFILE cannot execute SQL statements found in an SQL script. It expects the file to contain text data with options for symbols as separators and delimiters. It does not recognize SQL statements.

If you use LOAD DATA INFILE on an SQL script, then you only load the text SQL statements into your database, which is quite different from executing the SQL statements themselves.

If you need to execute SQL statements in a script, you can find code to do this in the phpMyAdmin application. Duplicating this functionality from scratch is a lot harder than it sounds. See examples about this in my answers to:

The better solution is to execute a SQL script by using shell_exec() to launch the mysql client with the upload SQL script as input.

But @tadman is correct, this is a dangerous and insecure thing to do, allowing user input to send an SQL script that you execute verbatim. You can't know what malicious queries they will include in that script file!

Genes · Accepted Answer · 2020-05-23 16:22:48Z

0

I used file_get_contents() to access the file contents and executed the code with PDO.

    if (isset($_FILES['data'])) {
        if (is_uploaded_file($_FILES['filename']['tmp_name'])) { 
            if (move_uploaded_file($_FILES['filename']['tmp_name'], $_FILES['filename']['name'])) {
                $db = new PDO('dns', 'user', 'pass');
                $sql = file_get_contents($_FILES['filename']['name']);                  
                $data = $db->prepare($sql);
                $data->execute();
            }
        }
    }

answered May 23, 2020 at 16:22

Genes

2981 gold badge3 silver badges16 bronze badges

Collectives™ on Stack Overflow

Import .sql file via PHP upload

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related