Product inventory database

Question

I just finished writing a program which working with a database. Throughout the process when working with people to get past some of the hurdles I faced I was ridiculed for my SQL statements. I have never fully understand what to do to protect myself and I know there is a lot of information out there to read. But now that I have a working program all done with PDO would someone mind showing me what I can do to make myself more secure. I would like to produce the most professional code I can.

File One, Drops a table if it exist then creates a new table. Opens a file and inserts the csv file into the database.

      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();
        }  

 }

File 2, Loads the paginated the total table from the database.

   $limit = (intval($_GET['limit']) != 0 ) ? $_GET['limit'] : 5;
    $offset = (intval($_GET['offset']) != 0 ) ? $_GET['offset'] : 0;

    $sql = "SELECT * FROM wuno_inventory WHERE 1 ORDER BY id ASC LIMIT $limit OFFSET $offset";
    try {
      $stmt = $DB_con->prepare($sql);
      $stmt->execute();
      $results = $stmt->fetchAll();
    } catch (Exception $ex) {
      echo $ex->getMessage();
    }
    if (count($results) > 0) {
      foreach ($results as $res) {
        echo '<tr class="invent">';  
        echo '<td>' . $res['wuno_product'] . '</td>';  
        echo '<td>' . $res['wuno_alternates'] . '</td>';  
        echo '<td>' . $res['wuno_description'] . '</td>';  
        echo '<td>' . $res['wuno_onhand'] . '</td>';  
        echo '<td>' . $res['wuno_condition'] . '</td>';  
        echo '</tr>';   
      }
    }

File 3, Handles the search query displaying the paginated data to the user when they search,

<?php
require_once ('Dbconfig.php');

$limit = (intval($_GET['limit']) != 0 ) ? $_GET['limit'] : 5;
$offset = (intval($_GET['offset']) != 0 ) ? $_GET['offset'] : 0;

if(!empty($_POST["itemID"])) {
 $sql=" SELECT * FROM wuno_inventory WHERE wuno_product like '%". $_POST["itemID"] ."%' OR wuno_alternates like '%". $_POST["itemID"] ."%' ORDER BY wuno_product ASC LIMIT $limit OFFSET $offset ";
try {
  $stmt = $DB_con->prepare($sql);
  $stmt->execute();
  $results = $stmt->fetchAll();
} catch (Exception $ex) {
  echo $ex->getMessage();
}
if (count($results) > 0) {
  foreach ($results as $res) {
    echo '<tr class="invent">';  
    echo '<td>' . $res['wuno_product'] . '</td>';  
    echo '<td>' . $res['wuno_alternates'] . '</td>';  
    echo '<td>' . $res['wuno_description'] . '</td>';  
    echo '<td>' . $res['wuno_onhand'] . '</td>';  
    echo '<td>' . $res['wuno_condition'] . '</td>';  
    echo '</tr>';   
  }
}
}
?>

Answer 1

SQL Injection

Your code is completely open to SQL injection.

It is not enough to simply use prepare. The important part is that you should never put variables directly into the query. The query should only contain placeholders (either :key or ?). The variables are then later bound to the query.

You have variables in most of your queries, for example:

// $limit and $offset are user controlled:
$sql = "SELECT * FROM wuno_inventory WHERE 1 ORDER BY id ASC LIMIT $limit OFFSET $offset";
$sql=" SELECT * FROM wuno_inventory WHERE wuno_product like '%". $_POST["itemID"] ."%' OR wuno_alternates like '%". $_POST["itemID"] ."%' ORDER BY wuno_product ASC LIMIT $limit OFFSET $offset ";
$sql = "INSERT INTO " . $table_name . "(wuno_product, wuno_description, wuno_alternates, wuno_onhand, wuno_condition) 
    VALUES('".$col1."','".$col2."','".$col3."','".$col4."','".$col5."')";

You can see a correct usage of prepared statements in the documentation of PDO.

Misc

• your indention is off, making your code really hard to read.