1
\$\begingroup\$

I have a function which converts a file from CSV to MYSQL

The file could be 5,000 rows long up to 90,000 rows long. Based on that I decided in case of problems with uploading the file I would just have the client upload the file with an FTP to a predefined path and then have my program execute that file when the submit button is pressed.

This was in Wordpress but I had a lot of trouble getting this to work with the built in functions. So I wrote my own and got it working. Now I am worried about security and the overall professionalism of my code.

Also I have only tested this with a file that has 5000 rows.

I would like to know if I can do anything to make my program a higher quality and how safe should I feel about larger files coming in based on my code?

<?php
/**
 * Plugin Name: Wuno Uploader
 * Plugin URI: https://www.example.com
 * Description: This plugin adds functionality for uploading CSV files to the database
 * Version: 1.0.0
 * Author: Me
 * Author URI: https://www.example.com
 */

// create custom plugin settings menu
add_action('admin_menu', 'wuno_plugin_create_menu');

function wuno_plugin_create_menu() {

    //create new top-level menu
    add_menu_page('Wuno Plugin Settings', 'Wuno Installer', 'administrator', __FILE__, 'wuno_settings_page' , plugins_url('/images/icon.png', __FILE__) );

    //call register settings function
    add_action( 'admin_init', 'register_wuno_settings' );
}

function register_wuno_settings() {
    //register our settings
    register_setting( 'wuno-settings-group', 'file_to_install' );
    register_setting( 'wuno-settings-group', 'some_other_option' );
    register_setting( 'wuno-settings-group', 'option_etc' );
}  

function productsExec() {
      $hostname='localhost';
      $username='usernmae';
      $password='password';
      $database='db';
      $table_name = "myTable";
      // path where your CSV file is located
      define('CSV_PATH', '../wp-content/plugins/wuno-uploader/');
      // Name of your CSV file
      $csv_file = CSV_PATH . "inventory.csv"; 

      try {
      $dbh = new PDO("mysql:host=$hostname;dbname=$database",$username,$password);
      $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
      $sql = "DROP TABLE IF EXISTS " . $table_name;
      $dbh->query($sql);

      $sql = "CREATE TABLE IF NOT EXISTS " . $table_name . " (
      id int(8) NOT NULL AUTO_INCREMENT,
      wuno_product varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      wuno_description varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      wuno_alternates varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      wuno_onhand varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      wuno_condition varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY KEY  (id)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;"; 
          $dbh->query($sql);

        file_put_contents( $csv_file, preg_replace( "@(\r\n),@", ',', file_get_contents( $csv_file ) ) );
    if (($handle = fopen($csv_file, "r")) !== FALSE) {
           fgetcsv($handle);   
           while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
                $num = count($data);
                for ($c=0; $c < $num; $c++) {
                  $col[$c] = $data[$c];
                }

         $col1 = $col[0];
         $col2 = $col[1];
         $col3 = $col[2];
         $col4 = $col[3];
         $col5 = $col[4];

        // SQL Query to insert data into DataBase
        $sql = "INSERT INTO " . $table_name . "(wuno_product, wuno_description, wuno_alternates, wuno_onhand, wuno_condition) 
        VALUES('".$col1."','".$col2."','".$col3."','".$col4."','".$col5."')";
        $dbh->query( $sql );
        }
        fclose($handle);
        }
        if ($dbh->query($sql)) {
        echo "<script type= 'text/javascript'>alert('New Record Inserted Successfully');</script>";
        }
        else{
        echo "<script type= 'text/javascript'>alert('Data not successfully Inserted.');</script>";
        }
        $dbh = null;
        }
        catch(PDOException $e)
        {
        echo $e->getMessage();
        }  

 }

function wuno_settings_page() {
 if (isset($_POST['wuno-inventory'])) {
        productsExec();
    } 
?>

<h1>Wuno Inventory Updater</h1>

<form method="POST">
    <label for="wuno-inventory">Path To Inventory</label>
    <input type="text" name="wuno-inventory" id="wuno-inventory" value="inventory.csv">
    <input type="submit" value="Install" class="button button-primary button-large">
</form>

<?php
    }   
?>
\$\endgroup\$
3
  • \$\begingroup\$ thanks for your input. Yes I would love to make this more secure. Would you please answer the question and show me how and where to make those changes. As well as why at each point? Overall could you please give me some more feedback on this program? \$\endgroup\$
    – wuno
    Feb 12, 2016 at 17:10
  • \$\begingroup\$ I really don't understand what your saying is insecure. I am using PDO and yet you sent me a link of a guy talking about using PDO as one way to make DB entries more secure. I would appreciate if you truly see something insecure in my code to state what it is. Thanks, \$\endgroup\$
    – wuno
    Feb 14, 2016 at 18:40
  • \$\begingroup\$ I understand what SQL Injection is. Typing a php variable in the comments does not really answer this question. Once again if you see something that needs to be improved in this question please answer it. That is the point of this website. \$\endgroup\$
    – wuno
    Feb 15, 2016 at 9:37

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.