PHP, PDO,MYSQL binding syntax of query WHERE clause involving 3 tables

Question

I have constructed an SQL query to provide a summary of stock holdings, i.e. group all stock transactions for a given stock, using PHP, PDO, MySQL. The SQL works when tested PHPMyAdmin, ( by changing named parameters from :c_code to c.code, :exchange to "LSE", :p_portfolio_id to p.portfolio_id )

I am unsure how to construct the bindParam statement(s) in such a scenario, or if I must use a JOIN statement in the query. I am relatively new to thsi but learning fast, many thanks for any help Cheers Colin here is the code.

changed the code returns no errors but no records either ..

    <?php
include('db.php');
include('function.php');
$query = '';
$output = array();

$query .= "SELECT 
                t.user_id AS tuser_id,
                p.user_id AS puser_id,
                t.exchange AS exchange, 
                t.code AS code,
                c.name AS name, 
                p.name AS portfolio,
                CEILING(c.price * t.quantity / 100) AS value,
                DATE_FORMAT(t.trade_date,'%d%m%y') AS trade_date, 
                t.type AS type,
                SUM(t.quantity) AS quantity, 
                t.price AS price, 
                SUM(t.commission) AS commission,
                SUM(t.total_cost) AS total_cost
                FROM 
                transaction t, company c, portfolio p 
                WHERE
                t.code = :c_code
                AND
                t.exchange = :t_exchange
                AND
                t.portfolio_id = :p_portfolio_id 
                GROUP BY 
                t.code
                ORDER BY 
                t.code ";

if(isset($_POST["search"]["value"]))
{
 $query .= 'AND trade_date     LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR exchange         LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR code             LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR type             LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR quantity         LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR price            LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR commission       LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR total_cost       LIKE "%'.$_POST["search"]["value"].'%" ';
}
if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
 $query .= 'ORDER BY id DESC ';
}
if($_POST["length"] != -1)
{
 $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$exchange = "LSE";

$statement = $connection->prepare($query);
$statement->bindParam(':t_exchange', $exchange, PDO::PARAM_STR);
$statement->bindParam(':c_code', $_POST['company.code'], PDO::PARAM_STR);
$statement->bindParam(':p_portfolio_id', $_POST['portfolio.id'], PDO::PARAM_STR);

$statement->execute();
$result = $statement->fetchAll();
$data = array();
$filtered_rows = $statement->rowCount();
foreach($result as $row)
{
 $sub_array = array();
 $sub_array[] = $row["trade_date"];
 $sub_array[] = $row["exchange"];
 $sub_array[] = $row["code"];
 $sub_array[] = $row["type"];
 $sub_array[] = $row["quantity"];
 $sub_array[] = $row["price"];
 $sub_array[] = $row["commission"];
 $sub_array[] = $row["total_cost"];
 $sub_array[] = '<button type="button" name="update" id="'.$row["id"].'" class="btn btn-warning btn-xs update">Update</button>';
 $sub_array[] = '<button type="button" name="delete" id="'.$row["id"].'" class="btn btn-danger btn-xs delete">Delete</button>';
 $data[] = $sub_array;
}
$output = array(
 "draw"    => intval($_POST["draw"]),
 "recordsTotal"  =>  $filtered_rows,
 "recordsFiltered" => get_total_all_records(),
 "data"    => $data
);
echo json_encode($output);
?>

This SQL below run in PHPMyAdmin SQL works ok

SELECT 
                t.user_id AS tuser_id,
                p.user_id AS puser_id,
                t.exchange AS exchange, 
                t.code AS code,
                c.name AS name, 
                p.name AS portfolio,
                CEILING(c.price * t.quantity / 100) AS value,
                DATE_FORMAT(t.trade_date,'%d%m%y') AS trade_date, 
                t.type AS type,
                SUM(t.quantity) AS quantity, 
                t.price AS price, 
                SUM(t.commission) AS commission,
                SUM(t.total_cost) AS total_cost
                FROM 
                transaction t, company c, portfolio p 
                WHERE
                t.code = c.code
                AND
                t.exchange = "LSE"
                AND
                t.portfolio_id = p.id 
                GROUP BY 
                t.code
                ORDER BY 
                t.code

I am trying to integrate some of code recommended here by calling holdg_fetch.php from holdg_home.php to display the MySQL data in a table but I am getting an error "DataTables warning:table id=transaction_data - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1 " I can run holdg_fetch.php on its own successfully, no errors. I can run holdg_home.php calling trans_fetch.php successfuly, no errors.

Note: I will sort out the security and structure from your recommendations, once I fix this JSON error. Many Thanks in advance Colin

trans_fetch.php

    <?php
include('db.php');
include('function.php');
$query = '';
$output = array();
$query .= "
SELECT 
*,
DATE_FORMAT(trade_date,'%d%m%y') AS trade_date
FROM 
transaction ";

if(isset($_POST["search"]["value"]))
{
 $query .= 'WHERE trade_date    LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR exchange         LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR code             LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR type             LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR quantity         LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR price            LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR commission       LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR total_cost       LIKE "%'.$_POST["search"]["value"].'%" ';
}
if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
 $query .= 'ORDER BY id DESC ';
}
if($_POST["length"] != -1)
{
 $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$statement = $connection->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
$filtered_rows = $statement->rowCount();
foreach($result as $row)
{
 $sub_array = array();
 $sub_array[] = $row["trade_date"];
 $sub_array[] = $row["exchange"];
 $sub_array[] = $row["code"];
 $sub_array[] = $row["type"];
 $sub_array[] = $row["quantity"];
 $sub_array[] = $row["price"];
 $sub_array[] = $row["commission"];
 $sub_array[] = $row["total_cost"];
 $sub_array[] = '<button type="button" name="update" id="'.$row["id"].'" class="btn btn-warning btn-xs update">Update</button>';
 $sub_array[] = '<button type="button" name="delete" id="'.$row["id"].'" class="btn btn-danger btn-xs delete">Delete</button>';
 $data[] = $sub_array;
}
$output = array(
 "draw"    => intval($_POST["draw"]),
 "recordsTotal"  =>  $filtered_rows,
 "recordsFiltered" => get_total_all_records(),
 "data"    => $data
);
echo json_encode($output);
?>

holdg_home.php

<?php include 'db.php'; ?>

<?php include("header-nav2.php"); ?>
<main>        
 <div class="container box">

   <br />
   <div class="table-responsive">
    <br />
    <div align="right">
     <button type="button" id="add_button" data-toggle="modal" data-target="#transactionModal" class="btn btn-warning"><i class="fa fa-plus" aria-hidden="true"></i></button>
    </div>
    <br />
    <table id="transaction_data" class="table table-bordered table-striped">
     <thead class="blue-grey lighten-4">
      <tr>
       <th width="11%">TradeDate</th>
       <th width="11%">Exchange</th>
       <th width="11%">Code</th>
       <th width="11%">Type</th>
       <th width="11%">Quantity</th>
       <th width="11%">Price</th>
       <th width="11%">Commission</th>
       <th width="11%">TotalCost</th>
       <th width="6%">Edit</th>
       <th width="6%">Delete</th>
      </tr>
     </thead>
    </table>
   </div>
  </div>



<?php include("footer.php"); ?>
 </body>
</html>



    <script type="text/javascript" language="javascript" >
    $(document).ready(function(){
     $('#add_button').click(function(){
      $('#transaction_form')[0].reset();
      $('.modal-title').text("Add Transaction");
      $('#action').val("Add");
      $('#operation').val("Add");
     });

     var dataTable = $('#transaction_data').DataTable({
      "processing":true,
      "serverSide":true,
      "order":[],
      "ajax":{
       url:"holdg_fetch.php",
       type:"POST"
      },
      "columnDefs":[
       {//removes sort from columns given by targets, where 0 - remove Column 1 sort etc.
        "targets":[8, 9],
        "orderable":false,
       },
      ],

     });
...

holdg_fetch.php

  <?php
include('db.php');
include('function.php');
$query = '';
$output = array();
$query .= "
    SELECT 
t.user_id AS tuser_id,
    p.user_id AS puser_id,
    t.exchange AS exchange, 
    t.code AS code,
    c.name AS name, 
    p.name AS portfolio,
    CEILING(c.price * t.quantity / 100) AS value,
    DATE_FORMAT(t.trade_date,'%d%m%y') AS trade_date, 
    t.type AS type,
    SUM(t.quantity) AS quantity, 
    t.price AS price, 
    SUM(t.commission) AS commission,
    SUM(t.total_cost) AS total_cost
    FROM 
    transaction t
    inner join  company c on (t.code=c.code)
    inner join portfolio p  on (t.portfolio_id = p.id)
    GROUP BY
    t.code";

//    WHERE
//    1=1

//if(isset($_POST["search"]["value"]))
//{
// $query .= ' WHERE trade_date    LIKE "%'.$_POST["search"]["value"].'%" ';
// $query .= 'OR exchange         LIKE "%'.$_POST["search"]["value"].'%" ';
// $query .= 'OR code             LIKE "%'.$_POST["search"]["value"].'%" ';
// $query .= 'OR type             LIKE "%'.$_POST["search"]["value"].'%" ';
// $query .= 'OR quantity         LIKE "%'.$_POST["search"]["value"].'%" ';
// $query .= 'OR price            LIKE "%'.$_POST["search"]["value"].'%" ';
// $query .= 'OR commission       LIKE "%'.$_POST["search"]["value"].'%" ';
// $query .= 'OR total_cost       LIKE "%'.$_POST["search"]["value"].'%" ';
//}
//if(isset($_POST["order"]))
//{
// $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
//}
//else
//{
// $query .= 'ORDER BY id DESC ';
//}
//if($_POST["length"] != -1)
//{
// $query .= ' LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
//}

//$query .= ' GROUP BY t.code';

$statement = $connection->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
var_dump($result);

$data = array();
$filtered_rows = $statement->rowCount();
foreach($result as $row)
{
 $sub_array = array();
 $sub_array[] = $row["trade_date"];
 $sub_array[] = $row["exchange"];
 $sub_array[] = $row["code"];
 $sub_array[] = $row["type"];
 $sub_array[] = $row["quantity"];
 $sub_array[] = $row["price"];
 $sub_array[] = $row["commission"];
 $sub_array[] = $row["total_cost"];
// $sub_array[] = '<button type="button" name="update" id="'.$row["id"].'" class="btn btn-warning btn-xs update">Update</button>';
// $sub_array[] = '<button type="button" name="delete" id="'.$row["id"].'" class="btn btn-danger btn-xs delete">Delete</button>';
 $data[] = $sub_array;
}
$output = array(
// "draw"    => intval($_POST["draw"]),
 //"recordsTotal"  =>  $filtered_rows,
 //"recordsFiltered" => get_total_all_records(),
 "data"    => $data
);
echo json_encode($output);
?>

I have now fixed the JSON error, mismatch of data and selected columns, please ignore above error, now fixed

Answer 1

Looks to me like there is a problem with the dynamically generated SQL.

    GROUP BY 
    t.code
    ORDER BY 
    t.code AND trade_date  LIKE "%foo%" OR exchange   LIKE "%foo%"

It's likely MySQL will accept the syntax, and allow the query to be processed. But those conditions aren't predicates in the WHERE clause. That whole line following ORDER BY is going to be evaluated as a single boolean expression, which will return a 0, 1 or NULL for each row. And the ORDER BY is going to operate on the value of the boolean. (It's a clever trick, a convenient shortcut, but I suspect that's not what the code here is meaning to achieve.)

If we want to add conditions to the WHERE clause, those need to come before the GROUP BY. But in the WHERE clause, we can't reference aliases assigned in the SELECT list. (We can in a HAVING clause.)

Note that AND has a higher order of precedence than OR.

a AND b OR c is the same as (a AND b) OR c not a AND (b OR c)

---

Adding another ORDER BY to the query is definitely not valid SQL. A construct like this, with two ORDER BY clauses at the end of the statement

    GROUP BY 
    t.code
    ORDER BY 
    t.code ORDER BY whatever

is going to cause a SQL syntax error.

---

Q: if I must use a JOIN statement in the query ?

The query already specifies JOIN operations. The comma is valid syntax for a JOIN operation. This:

    FROM 
    transaction t, company c, portfolio p
    WHERE
    t.code = c.code
    AND
    t.exchange = 'LSE'
    AND
    t.portfolio_id = p.id

is the old-school way writing what we would nowadays write as:

    FROM transaction t
    JOIN company c
      ON c.code = t.code
    JOIN portfolio p 
      ON p.id = t.portfolio_id
   WHERE t.exchange = :t_exchange

Note that there is only one bind placeholder we need to bind a value to there. We can't provide identifiers (like column names) or other SQL syntax through a bind placeholder.

Given that our bind placeholder is an input, we can use bindValue in place of bindParam.

  $sth = $conn->prepare($sql);
  $sth->bindValue(':t_exchange', $exchange, PDO::PARAM_STR);

---

The other values being provided in the query should also be added as bind placeholders. Never incorporate potentially unsafe values into SQL text.

It looks to me (without a specification, I'm just guessing here) that we are intending to produce a query that looks like this:

    FROM transaction t
    JOIN company c
      ON c.code = t.code
    JOIN portfolio p 
      ON p.id = t.portfolio_id
   WHERE 1=1
     AND (  t.trade_date    LIKE CONCAT('%', :search_01 , '%')
         OR t.exchange      LIKE CONCAT('%', :search_02 , '%')
         OR t.code          LIKE CONCAT('%', :search_03 , '%')
         OR t.type          LIKE CONCAT('%', :search_04 , '%')
         )
   GROUP BY ...
   ORDER BY ... 

(I've used SQL standard single quotes around string literals. MySQL allows us to use double quotes in place of the single quotes, as long as sql_mode doesn't include ANSI_QUOTES.)

  $sth = $conn->prepare($sql);
  $sth->bindValue(':search_01', $_POST["search"]["value"], PDO::PARAM_STR);
  $sth->bindValue(':search_02', $_POST["search"]["value"], PDO::PARAM_STR);
  $sth->bindValue(':search_03', $_POST["search"]["value"], PDO::PARAM_STR);
  $sth->bindValue(':search_04', $_POST["search"]["value"], PDO::PARAM_STR);

(I'm going to assume here that $_POST["search"] is an array, and that we know what we're doing here, getting a single value from the array.)

If we are conditionally appending a LIMIT clause ...

 if( someconditition ) {
    $sql .= " LIMIT :nskip, :nrows";
 }

Then when we are binding the parameter values, we need to determine whether the LIMIT clause was added. Checking the same condition would be convenient (as long as we know that the condition will evaluate the same later, after we do the prepare.)

  if( somecondition ) {
    $sth->bindValue(':nskip', (int)$_POST["start"] , PDO::PARAM_INT);
    $sth->bindValue(':nrows', (int)$_POST["length"], PDO::PARAM_INT);
  }

---

NEVER incorporate potentially unsafe values into the text of a SQL statement.

Little Bobby Tables (Exploits of a Mom) https://xkcd.com/327/

OWASP SQL Injection https://www.owasp.org/index.php/SQL_Injection

Answer 2

There are some issues with this code:

• group by and order by in the query at the top of your code before adding more where clauses (in the if block) - this will error out.

• Constructing the like clauses as a string - you are open to SQL injection attack. You can bind like clauses as parameters:

  $query .= 'where trade_date like :trade_date';
  //then bind it
  $statement->bindParam( ':trade_date', '%' . $_POST['search']['value'] . 
  '%', PDO::PARAM_STR );
  

• It looks like you need parenthesis around the where clauses for the search term.

• Predefine the order by in code and use some sort of parameter to create your query - don't trust user input for this - you'll get hacked.

• Use explicit joins - I think I've gotten them correct in my code below.

• Construct the limit clause with bound parameters.

  $query .= "LIMIT :limitstart,:limitlength";
  $statement->bindValue(':limitstart', (int) trim( $_POST['start'] ), PDO::PARAM_INT );
  $statement->bindValue(':limitlength', (int) trim( $_POST['length'] ), PDO::PARAM_INT );
  

Try this code - debugged except for the execution of the query since I don't have your db.

<?php

include('db.php');
include('function.php');

$searchFields = array( 'exchange', 'code','type','quantity', 'price', 'commission','total_cost' );

$output = array();

// i'm guessing at the join for portfolio below.
// i'm guessing that the portfolioid where clause needs to be removed because it was probably used only for joins
// where 1=1 will be optimized out by the query optimizer

$query = "SELECT 
    t.user_id AS tuser_id,
    p.user_id AS puser_id,
    t.exchange AS exchange, 
    t.code AS code,
    c.name AS name, 
    p.name AS portfolio,
    CEILING(c.price * t.quantity / 100) AS value,
    DATE_FORMAT(t.trade_date,'%d%m%y') AS trade_date, 
    t.type AS type,
    SUM(t.quantity) AS quantity, 
    t.price AS price, 
    SUM(t.commission) AS commission,
    SUM(t.total_cost) AS total_cost
    FROM 
    transaction t
    inner join  company c on (t.code=c.code)
    inner join portfolio p  on (t.portfolio_id = p.id)
    WHERE
    1=1
";

$bindSearch = false;
$searchValue = '';
if( array_key_exists( 'search', $_POST ) && array_key_exists( 'value', $_POST['search'] ) && ! empty( $_POST['search']['value'] ) )
{
    $bindSearch = true;
    $searchValue = $_POST['search']['value'];
    // 1=1 will be optimized out
    $query .= "\tAND (\n\t\t1=1\n";
    foreach( $searchFields as $field )
    {
            $query .= "\t\tOR $field  LIKE : $field\n";
    }
    $query .= "\t)\n";
}


if( array_key_exists( 'order', $_POST ) && ! empty( $_POST['order'] ) )
{
    switch ( $_POST['order']['0']['column'] )
    {
    case 'code':
        $query .= 'ORDER BY code ';
        break;
    default:
        $query .= 'ORDER BY id DESC ';
        break;
    }
}

$bindLimit = false;
if( array_key_exists( 'length', $_POST ) && $_POST['length'] > 0 )
{
    $bindLimit = true;
    $query .= "\n\tLIMIT :limitstart,:limitlength";
}
?>

<form method="POST">
    search: <input type="text" name="search[value]"><br>
    order: <input type="text" name="order[0][column]"><br>
    Limit Start: <input type="text" name="start"><br>
    Limit Length: <input type="text" name="length"><br>
    <button type="submit">Submit</button>
</form>

<?
echo "<pre>$query";

$statement = $connection->prepare($query);

$exchange = "LSE";
$statement->bindParam( ':exchange', $exchange, PDO::PARAM_STR );

if( $bindSearch )
{
    foreach( $searchFields as $field )
    {
        $statement->bindParam( ':' . $field, $searchValue , PDO::PARAM_STR );
    }
}

if( $bindLimit )
{
    $statement->bindValue(':limitstart', (int) trim( $_POST['start'] ), PDO::PARAM_INT );
    $statement->bindValue(':limitlength', (int) trim( $_POST['length'] ), PDO::PARAM_INT );
}

$statement->execute();
$result = $statement->fetchAll();
print_r( $result );
var_dump( $result );

$data = array();
$filtered_rows = $statement->rowCount();


foreach($result as $row)
{
    $sub_array = array();
    $sub_array[] = $row["trade_date"];
    $sub_array[] = $row["exchange"];
    $sub_array[] = $row["code"];
    $sub_array[] = $row["type"];
    $sub_array[] = $row["quantity"];
    $sub_array[] = $row["price"];
    $sub_array[] = $row["commission"];
    $sub_array[] = $row["total_cost"];
    $sub_array[] = '<button type="button" name="update" id="'.$row["id"].'" class="btn btn-warning btn-xs update">Update</button>';
    $sub_array[] = '<button type="button" name="delete" id="'.$row["id"].'" class="btn btn-danger btn-xs delete">Delete</button>';
    $data[] = $sub_array;
}
$output = array(
 "draw" => intval($_POST["draw"]),
 "recordsTotal"  =>  $filtered_rows,
 "recordsFiltered" => get_total_all_records(),
 "data" => $data
);
echo json_encode($output);

?>

This generates the following query:

SELECT 
t.user_id AS tuser_id,
p.user_id AS puser_id,
t.exchange AS exchange, 
t.code AS code,
c.name AS name, 
p.name AS portfolio,
CEILING(c.price * t.quantity / 100) AS value,
DATE_FORMAT(t.trade_date,'%d%m%y') AS trade_date, 
t.type AS type,
SUM(t.quantity) AS quantity, 
t.price AS price, 
SUM(t.commission) AS commission,
SUM(t.total_cost) AS total_cost
FROM 
transaction t
inner join  company c on (t.code=c.code)
inner join portfolio p  on (t.portfolio_id = p.id)
WHERE
1=1
AND (
    1=1
    OR exchange  LIKE : exchange
    OR code  LIKE : code
    OR type  LIKE : type
    OR quantity  LIKE : quantity
    OR price  LIKE : price
    OR commission  LIKE : commission
    OR total_cost  LIKE : total_cost
)
ORDER BY id DESC 

Answer 3

this is too long you can create a procedure in it you have to call all your parameters through procedures . procedure is help full for that type of query and that will be also secure . And if you want to select only you have to create a view on it use sqlyog for it that will be helpfull for your joins you can create your join with the help of designs