I am trying to export a MySQL table, but I am receiving --secure-file-priv

Question

I understand there are a lot of other similar questions, but I have pretty much looked into all the other posts and all of the other suggestions have not worked.

I am currently using a macOS Sierra version 10.12.2

MySQL version 5.7.17

SELECT @@GLOBAL.secure_file_priv; and SHOW VARIABLES LIKE "secure_file_priv"; are showing secure_file_priv = to NULL.

I do not have a my.cnf file on my system (find: my.cnf: No such file or directory)

I have read many posts that mentioned changing the value of secure_file_priv, but I am unable to find it.

The following posts on here I have addressed:

Solution 1

Solution 2

Solution 3

I wish this one was answered as it relates to my situation:

Solution 4

Solution 5

At the end of the day it looks like changing secure_file_priv is the place to start, I just cannot find it.

I am sure that the OUTFILE location may be incorrect for the above, but that is because I do not know where I have file access because I do not know how to change/locate secure_file_priv.

score 1 · Answer 1 · answered Jan 24 '17 at 19:20

So I decided to not worry about command line and created a .php file that exports the MySQL database directly into Excel.

Solution inspired from the following: (Show this dude some love!)

Author: Shahroze Nawaz

Date: November 8, 2016

Title: How To Import And Export CSV Files Using PHP And MySQL

URL: https://www.cloudways.com/blog/import-export-csv-using-php-and-mysql/

This is the main index.php

<pre>

    <!DOCTYPE html>
    <html lang="en">

    <head>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script>
    </head>

    <body>
        <div id="wrap">
            <div class="container">
                <div class="row">

        <!--Database Table Display-->
                    <?php 
                        include 'database_table.php';
                    ?> 
        <!--End Import Form Button-->

        <!--Export Form Button-->
                    <form class="form-horizontal" action="export.php" method="POST" name="upload_excel"   
                          enctype="multipart/form-data">
                        <div class="form-group">
                            <div class="col-md-4 col-md-offset-4">
                                <input type="submit" name="Export" class="btn btn-success" value="Export to Excel"/>
                            </div>
                        </div>                    
                    </form> 
        <!--End Export Form Button-->

                </div>
            </div>
        </div>
    </body>

    </html>

</pre>

And this is the export.php that is the form action:

<pre>
      $dbhost = "******";
      $dbuser = "******";
      $dbpass = "******";
      $dbname = "******";
      $connection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname); 

      $selection = "SELECT * <table name>";
      $result = mysqli_query($connection, $selection);

      if(isset($_POST["Export"])) {
            header('Content-Type: text/csv; charset=utf-8');  
            header('Content-Disposition: attachment; filename = leads.csv');

            $output = fopen("php://output", "w"); 
            fputcsv($output, array('ID', 'First Name', 'Last Name', 'Email', 'Phone', 'State', 'Specialty', 'Sourcecode', 'Date'));  

            while($row = mysqli_fetch_assoc($result)) {  
                 fputcsv($output, $row);  
            }  
            fclose($output);  
       }
</pre>

And here is the database table printed into the index.php file that shows the MySQL database with the bootstrap formats:

<pre>

    <?php
        $dbhost = "******";
        $dbuser = "******";
        $dbpass = "******";
        $dbname = "******";
        $connection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname); 

        $selection = "SELECT * <table name>";
        $result = mysqli_query($connection, $selection);

        if (mysqli_num_rows($result) > 0) {
              echo "<div class='table-responsive'>
                          <table id='myTable' class='table table-striped table-bordered'>
                                <thead>
                                      <tr>
                                            <th>ID</th>
                                            <th>First Name</th>
                                            <th>Last Name</th>
                                            <th>Email</th>
                                            <th>Phone</th>
                                            <th>State</th>
                                            <th>Specialty</th>
                                            <th>Sourcecode</th>
                                            <th>Time</th>
                                      </tr>
                                </thead>

                                <tbody>";
                                while($row = mysqli_fetch_assoc($result)) {
                                      echo "<tr>
                                            <td>" . $row['id']."</td>
                                            <td>" . $row['first_name']."</td>
                                            <td>" . $row['last_name']."</td>
                                            <td>" . $row['email']."</td>
                                            <td>" . $row['phone']."</td>
                                            <td>" . $row['state']."</td>
                                            <td>" . $row['specialty']."</td>
                                            <td>" . $row['source_code']."</td>
                                            <td>" . $row['date_submitted']."</td>
                                      </tr>";        
                          }

                                echo "</tbody>
                          </table>
                    </div>";

        } else {
              echo "You have no records...";
        }
    ?>

</pre>

I am trying to export a MySQL table, but I am receiving --secure-file-priv

1 Answers1