Filtering a CRUD table using php

Question

First off - I am a novice, so please excuse my lack of knowledge.

I found some code that I have manipulated to do most of what I need done. There's one small step that eludes me. I'm hoping someone here might shed some light on this.

The 'Filter Button' is supposed to capture the input catID #, which is used in the WHERE clause in the SELECT statement. I've tried replacing the 13 on line 72 with '$catID' to no avail.

When entering a specific value for the 'category_id' number within the php file, then saving the file, and refreshing the browser - it works, but using the button in the browser - it doesn't.

The SELECT statement is sound...it works in phpMyAdmin.

If there's anything I've missed, please let me know.

Thanks in advance,

Dave

<!DOCTYPE html>
<html lang="en">
   <head>
      <meta charset="UTF-8">
      <title>Dashboard</title>
      <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
      <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
      <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.js"></script>
      <style type="text/css">
         .wrapper{
         width: 650px;
         margin: 0 auto;
         }
         .page-header h2{
         margin-top: 0;
         }
         table tr td:last-child a{
         margin-right: 15px;
         }
      </style>
      <script type="text/javascript">
         $(document).ready(function(){
             $('[data-toggle="tooltip"]').tooltip();   
         });
      </script>
   </head>
   <body>
      <div class="wrapper">
         <div class="container-fluid">
            <div class="row">
               <div class="col-md-12">
                  <div class="page-header clearfix">
                     <h2 class="center">Tag H Details</h2>
                     <div>
                        <!--Filter Button  -->
                        <d
                        <div class="col-md-3">
                           <p class="margin"></p>
                           <div class="input-group">
                              <div class="input-group-btn">
                                 <form action="http://localhost/fp/rt/index.php" method="post">
                                 <input type="text" name = "catID" placeholder="Category" class="form-control" >
                                 <input type="submit" name = "catID" value= "Filter" class="btn btn-primary ">Filter</button></d>
                              </div>
                           </div>
                        </div>
                     </div>
                  </div>
                  <?php
                     // Include config file
                     require_once "config.php";

                     // Attempt select query execution

                     if(isset($_POST['catID']))
                     {

                     $catID = $_POST['catID'];

                     $sql = "SELECT
                     coh_items.id,
                     coh_items.tag_h_id,
                     coh_vendors.name,                          
                     coh_vendors.phone,
                     coh_vendors.c_code,
                     coh_items.purchase_price,
                     COUNT(tag_h_id) AS Reports
                     FROM
                     coh_items
                     INNER JOIN coh_vendors ON coh_items.tag_h_id = coh_vendors.id
                     WHERE
                     category_id = 13
                     GROUP BY
                     tag_h_id;";

                     $search_result = catID($sql);
                     }

                     else {
                     $sql = "SELECT * FROM `coh_items`";
                     $search_result = catID($sql);
                     }
                     function catID($sql){

                     $connect = mysqli_connect("localhost", "root", "", "green");
                     $filter_Result = mysqli_query($connect, $sql);
                     return $filter_Result;
                     };

                     if($result = mysqli_query($link, $sql)){
                         if(mysqli_num_rows($result) > 0){
                             echo "<table class='table table-bordered table-striped'>";
                                 echo "<thead>";
                                     echo "<tr>";
                                      echo "<th>ID</th>";
                                      echo "<th>Name</th>"; 
                                      echo "<th>Phone</th>";
                                      echo "<th>Status</th>";   
                                      echo "<th>Reports</th>";
                                      echo "<th>Rate</th>";
                                      echo "<th>Action</th>";
                                      echo "</tr>";
                                      echo "</thead>";
                                      echo "<tbody>";
                                      while($row = mysqli_fetch_array($result)){
                                      echo "<tr>";
                                      echo "<td>" . $row['tag_h_id'] . "</td>";
                                      echo "<td>" . $row['name'] . "</td>"; 

                                      echo "<td>" . $row['phone'] . "</td>";
                                      echo "<td>" . $row['c_code'] . "</td>";
                                       echo "<td>" . $row['Reports'] . "</td>";
                                       echo "<td>" . $row['purchase_price'] . "</td>";
                                       echo "<td>";
                                       echo "<a href='read.php?id=". $row['id'] ."' title='View Record' data-toggle='tooltip'><span class='glyphicon glyphicon-eye-open'></span></a>";
                                       echo "<a href='update.php?id=". $row['id'] ."' title='Update Record' data-toggle='tooltip'><span class='glyphicon glyphicon-pencil'></span></a>";
                                       echo "<a href='delete.php?id=". $row['id'] ."' title='Delete Record' data-toggle='tooltip'><span class='glyphicon glyphicon-trash'></span></a>";

                                         echo "</td>";
                                     echo "</tr>";
                                 }
                                 echo "</tbody>";                            
                             echo "</table>";
                             // Free result set
                             mysqli_free_result($result);
                         } else{
                             echo "<p class='lead'><em>No records were found.</em></p>";
                         }
                     } else{
                         echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
                     }

                     // Close connection
                     mysqli_close($link);
                     ?>
               </div>
            </div>
         </div>
      </div>
   </body>
</html>

This answer should give you some guidance on who to solve you problem **and** protect yourself from SQL Injection Attacks: https://stackoverflow.com/a/60496/4665 — Jon P, Oct 05 '18 at 04:47

ECPerth · Accepted Answer · 2018-10-08T04:23:17.620

0

As Jon P correctly points out, without prepared statements your code is vulnerable to SQL injection attacks. So you really need to re-code the SQL using prepared statements. However if you are just working on a home machine an are not worried about the security of your database then below is a quick fix.

The $sql variable is just a string and you need to build it up using concatenation. As below:

$sql = "SELECT
          coh_items.id,
          coh_items.tag_h_id,
          coh_vendors.name,                          
          coh_vendors.phone,
          coh_vendors.c_code,
          coh_items.purchase_price,
          COUNT(tag_h_id) AS Reports
        FROM
          coh_items
          INNER JOIN coh_vendors ON coh_items.tag_h_id = coh_vendors.id
        WHERE
          category_id = ".$catID." 
        GROUP BY
          tag_h_id;";

EDIT:

OK, so i found the problem. You named both the text input and the submit input as catID but, since the submit comes after, it's value (filter) overwrites the value from the text and is used in the POST method. Simply changing the name from the submit or removing the name will solve the issue.

    <p class="margin"></p>
    <div class="input-group-btn">
        <form action="http://localhost/fp/rt/index.php" method="post">
            <input type="text" name = "catID" placeholder="Category" class="form-control"/>
            <input type="submit" value= "Filter" class="btn btn-primary "/>
        </form>
    </div>

edited Oct 08 '18 at 04:23

answered Oct 05 '18 at 02:22

ECPerth

42
6

Let me know if this helps. – ECPerth Oct 05 '18 at 02:24
This answer is an extremely dangerous example. This makes the query vulnerable to SQL Injection attacks. – Jon P Oct 05 '18 at 04:14
Thank you for your prompt responses...I really appreciate them both. Fortunately, as this will only be used on a server on my machine, the sql injection attack is not a major concern for me. I do appreciate you pointing this out - guess I'm still very green. The solution offered by ECPerth, unfortunately throws an error. Any ideas? – Dave Zest Oct 05 '18 at 18:00
So it works as expected when you have the '13' hard coded in? Have you tried to echo $catID prior to running the query (right after $catID = $_POST['catID'];) and making sure it holds the value you expect. – ECPerth Oct 06 '18 at 07:52
Not only is this vulnerable to SQL injection, this is also prone to errors. For example, if the input $catID contains a single quote, the SQL query becomes syntactically wrong. – Pang Oct 07 '18 at 05:37
ECPerth - that is correct, the hard coded 13 (and other values that my table has, works), and trying the echo suggestion doesn't show the input value. Pang - thank you for sharing your observation. – Dave Zest Oct 07 '18 at 16:54
I edited my answer again, i now know this does not automatically notify you. Hopefully this will solve the issue. I too am still learning correct practise on this site. – ECPerth Oct 08 '18 at 07:02
Seems you identified the real reason for this issue, and Pang's observation was also beneficial in that variable needed to read '$catID' (with single quotes) got this working. Cheers! – Dave Zest Oct 09 '18 at 03:57

Filtering a CRUD table using php

1 Answers1