1
\$\begingroup\$

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>';   
  }
}
}
?>
\$\endgroup\$
1
\$\begingroup\$

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.
\$\endgroup\$
  • 1
    \$\begingroup\$ Thanks for taking the time to answer my question. The answer to this question is important to me. I would really appreciate an answer and example based on my exact situation. If you do find the time to help me with this please know your effort really will make a huge difference to me. Thank you, \$\endgroup\$ – wuno Feb 16 '16 at 16:32
  • 1
    \$\begingroup\$ @wuno Did you click the link "Documentation of PDO"? It contains exactly the examples you are looking for. \$\endgroup\$ – Kyborek Feb 26 '16 at 11:50

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.