0

I have a main php page where the user inputs a date range to view a table. I am using a jquery datepicker and am passing the values from the datepicker to another php page via ajax where my SELECT statement occurs. The values pass successfully and I can echo them back to the original page in the "yyyy-mm-dd" format (which is what I want). Then, I need to run a SELECT statement pulling only rows out where the orderdate is between the datepicker values picked by the user. When I run my query without the WHERE clause, the query is successful, and my table displays data. When I had the Where clause, the table headers displayed, but the data wouldn't. How do I get the variables to work with the query without the risk of SQLi?

Here is my code pulling datepicker values from ajax (and an attempt to convert the string to a date format - I am new to this and wasn't sure if that was the problem):

    //Get date range.
    $revenuefromajax=$_POST['revenuefromtext'];
    $revenuetoajax=$_POST['revenuetotext'];

    $revenuefromstring = strtotime($revenuefromajax);
    $revenuetostring = strtotime($revenuetoajax);

    $revenuefrom=date("Y-m-d", $revenuefromstring);
    $revenueto=date("Y-m-d", $revenuetostring);

    echo $revenuefrom;
    echo $revenueto;

MySQL Query:

    $sql = "SELECT invoices.id, invoices.orderdate, invoices.stagestatus, FORMAT(TRIM(LEADING '$' FROM invoices.totalprice), 2) AS totalprice, clients.company, lineitems.invoiceid, FORMAT((lineitems.width * lineitems.height) /144, 2 ) AS sqft, lineitems.quantity AS qty, FORMAT((invoices.totalprice / ((lineitems.width * lineitems.height) /144)), 2) as avgsqftrevenue, FORMAT((TRIM(LEADING '$' FROM invoices.totalprice) / lineitems.quantity), 2) AS avgunitrevenue
    FROM clients
    INNER JOIN invoices ON clients.id = invoices.clientid
    INNER JOIN lineitems ON invoices.id = lineitems.invoiceid
    WHERE invoices.ordedate BETWEEN ('".$revenuefrom."') AND ('".$revenueto."')
    ORDER BY invoices.id DESC 
    LIMIT 10";


    $result = $conn->query($sql);



    echo "<table id='revenueReportA' align='center' class='report_DT'>
    <tr>

      <th>Customer</th>
      <th>SG</th>
      <th>Revenue</th>
      <th>SQ FT</th>
      <th>AVG Revenue Per SQ FT</th>
      <th>Number of Units</th>
      <th>AVG Revenue Per Unit</th>
    </tr>";


    if ($result = $conn->query($sql)) {

    // fetch associative array 
      while ($row = $result->fetch_assoc()) {


  echo "<tr>";
  echo "<td>" . $row['company'] . "</td>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" ."$". $row['totalprice'] . "</td>";
  echo "<td>" . $row['sqft'] ."&nbsp;&nbsp;". "ft<sup>2</sup>". "</td>";
  echo "<td>" ."$". $row['avgsqftrevenue'] . "</td>";
  echo "<td>" . $row['qty'] . "</td>";
  echo "<td>" ."$". $row['avgunitrevenue'] . "</td>";
  echo "</tr>";
  } 

   echo "</table>";
rdimouro
  • 225
  • 1
  • 4
  • 17
  • done any basic debugging, like `echo $sql` to see what the query you're building looks like? if strtotime fails, it returns boolean `false`, which you then blindly insert into your query, producing `... WHERE orderdate BETWEEN ('') AND ('')`. – Marc B Oct 11 '16 at 19:21
  • 1
    Looks like you've misspelled the column name in the `WHERE` clause: `...WHERE invoices.ordedate...` – cOle2 Oct 11 '16 at 19:28
  • Well, turns out I don't need help with coding. I need help with spelling... Thank you cOle2! However, do either of you have suggestions for better ways to achieve the same results without concatenating the variable into the statement? I have read that doing so increases the risk for SQL injection? – rdimouro Oct 11 '16 at 19:35
  • 1
    your particular query is safe. you're not directly using user data to insert into the query, only what `strtotime/date` produces. But in general, do NOT build queries like this. use placeholders with prepared statements: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Marc B Oct 11 '16 at 19:39

0 Answers0