How to select MySQL Table using drop list in PHP

PHP
1

Hi Guys,

I am learning PHP and I am working on search filter from one Table in MySQL but I want to let it choose the table name from drop list which let me choose any table want to make filter on it.

I tried this code but didn’t work with me:

Filter.php:



<html>
 <body>
    <select name="mytableselect" >
    <option value="tablename1"> Table Name 2 </option>
    <option value="tablename2"> Table Name 2 </option>
    <option value="tablename3"> Table Name 3 </option>
    <option value="tablename4"> Table Name 4 </option>
    <option value="tablename5"> Table Name 5 </option>
    </select>
 
 
  <div class="container box">
   <h1 align="center">Search in Database</h1>
   <br />
   <div class="table-responsive">
    <br />
    <div class="row">
     <div class="input-daterange">
      <div class="col-md-4">
       <input type="text" name="start_date" id="start_date" class="form-control" />
      </div>
      <div class="col-md-4">
       <input type="text" name="end_date" id="end_date" class="form-control" />
      </div>      
     </div>
     <div class="col-md-4">
      <input type="button" name="search" id="search" value="Search" class="btn btn-info" />
     </div>
    </div>
    <br />
    <table id="order_data" class="table table-bordered table-striped">
     <thead>
      <tr>
       <th>ID</th>
       <th>Temp</th>
       <th>Time</th>
       <th>Date</th>
       <th>Clock</th>
      </tr>
     </thead>
    </table>
    
   </div>
  </div>
 </body>
</html>



<script type="text/javascript" language="javascript" >
$(document).ready(function(){
 
 $('.input-daterange').datepicker({
  todayBtn:'linked',
  format: "yyyy-mm-dd",
  autoclose: true
 });

 fetch_data('no');

 function fetch_data(is_date_search, start_date='', end_date='', mytableselect='')
 {
  var dataTable = $('#order_data').DataTable({
   "processing" : true,
   "serverSide" : true,
   "order" : [],
   "ajax" : {
    url:"fetch.php",
    type:"POST",
    data:{
     is_date_search:is_date_search, start_date:start_date, end_date:end_date, mytableselect:mytableselect
    }
   }
  });
 }

 $('#search').click(function(){
  var start_date = $('#start_date').val();
  var end_date = $('#end_date').val();
  var mytableselect = $('#mytableselect').val();

  if(start_date != '' && end_date !='')
  {
   $('#order_data').DataTable().destroy();
   fetch_data('yes', start_date, end_date, mytableselect);
   
  }
  else
  {
   alert("Both Date is Required");
  }
 }); 
 
});
</script>

Fetch.php:


<?php

//fetch.php
$DATABASE_HOST = 'localhost';
$DATABASE_USER = 'test';
$DATABASE_PASS = '1234';
$DATABASE_NAME = 'test';
// Try and connect using the info above.
$connect = mysqli_connect($DATABASE_HOST, $DATABASE_USER, $DATABASE_PASS, $DATABASE_NAME);
if (!$connect){

	die("Connection Failed: ". mysqli_connect_error());

}

if(isset($_POST['search'])){
	
	
  $mytableselect = $_POST['mytableselect'];

$query="SELECT * FROM $mytableselect ";

$columns = array('id', 'tempr', 'time2', 'Date', 'Clock');

$query = "SELECT * FROM $mytableselect WHERE ";

}
if($_POST["is_date_search"] == "yes")
{
 $query .= 'Date BETWEEN "'.$_POST["start_date"].'" AND "'.$_POST["end_date"].'" AND ';
}

if(isset($_POST["search"]["value"]))
{
 $query .= '
  (id LIKE "%'.$_POST["search"]["value"].'%" 
  OR tempr LIKE "%'.$_POST["search"]["value"].'%" 
  OR time2 LIKE "%'.$_POST["search"]["value"].'%" 
  OR Date LIKE "%'.$_POST["search"]["value"].'%")
 ';
}

if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$columns[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' 
 ';
}
else
{
 $query .= 'ORDER BY id DESC ';
}

$query1 = '';

if($_POST["length"] != -1)
{
 $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));

$result = mysqli_query($connect, $query . $query1);

$data = array();

while($row = mysqli_fetch_array($result))
{
 $sub_array = array();
 $sub_array[] = $row["id"];
 $sub_array[] = $row["tempr"];
 $sub_array[] = $row["time2"];
 $sub_array[] = $row["Date"];
 $sub_array[] = $row["Clock"];
 $data[] = $sub_array;
}

function get_all_data($connect)
{
 $query = "SELECT * FROM $mytableselect";
 $result = mysqli_query($connect, $query);
 return mysqli_num_rows($result);
}

$output = array(
 "draw"    => intval($_POST["draw"]),
 "recordsTotal"  =>  get_all_data($connect),
 "recordsFiltered" => $number_filter_row,
 "data"    => $data
);

echo json_encode($output);

?>
2

Can you expand on “didn’t work”? Did you get any error messages? What was the query that you tried to execute when you displayed it for debugging? Was it valid, and does it execute in isolation in phpmyadmin or similar database admin tool? On the face of it, though the forum display might be confusing matters, there seems to be plenty of scope for missing spaces between clauses.

Does the PHP code execute if you call it from a test html page? If it does, that would point to the Javascript being at fault.

Does the PHP work when called from this page, but the results don’t go into the correct places?

I don’t know a lot about JS, but this combination doesn’t seem right to me:

<select name="mytableselect" >
 ...

var mytableselect = $('#mytableselect').val();
1 Like
3

The client-side code is not sending a ‘search’ element, so if(isset($_POST['search'])){ will never be true. Most of the server-side code is not being executed. There are also a number of other problems in both sides of the code.

Because AJAX adds an extra layer to what you are doing, making debugging harder, you need to get to the point where you can successfully design, write, test, and debug just the html search form and the corresponding php/sql code, then you can worry about adding AJAX to the mix.

For what you are trying to do, select which database table to use, you MUST validate that the selected table is only and exactly one of the permitted choices, in order to prevent sql injection that would allow a hacker to get a copy of any/all of your database tables.

You also need to use a prepared query when supplying external, unknown, dynamic data values to the query when it gets executed.

4

Not just external, you should do so for internal ones as well. If you change the source of data for a query at a later date, from an internal source to an external source but forget to convert to using a prepared statement for the query you’ll have opened up a security hole

5

not even SQL injection at that point. Send it the table/view “mysql.user” and have fun.

There are several problems in this PHP.

General lack of prepared statements;

Your query will fail when you fetch_data('no'), because at the end of the code the query would read:

SELECT * FROM WHERE ORDER BY id DESC LIMIT ,

Which is not a valid query.

It may fail before that because you use $_POST["is_date_search"] without checking for it’s existence first, which is a PHP E_FATAL (Index Undefined);

As stated above, you never send $_POST[‘search’] so the code itself will never execute,

Your javascript doesnt provide the option of sending an order, or a length, or any of the subkeys thereto;

Your fetch_array while loop can be simplified to removing the SELECT * and only selecting the fields you’re interested in:

SELECT id,tempr,time2,Date,Clock FROM....

followed by

$data[] = mysqli_fetch_array($result,MYSQLI_NUM);
6

True but in this particular case it is somewhat misleading.

As far as prepare statements are concerned, table and column names are not considered data. Using prepared statements will not help for table and column names. You always have to validate them outside of prepared statements.

7

A quick and dirty (not necessarily the best way) to validate the table name would be to do a SHOW TABLES query, then with the have PHP search the array of returned tables for an exact match for the selected table, giving an error if there’s not an exact match

8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.