PHP search bar to filter HTML table breaks on NULL

Question

I have a simple html table being populated by my mysql database, and a function that is creating a new query in php, that will then be loaded into the query which is used to populate the table as a means of filtering it down. The issue becomes that when there is a value of NULL in my database instead of just being an empty value, it breaks the statement.

Below is the value to generate the SQL which will populate the table after filtering:

<?php
  if(isset($_POST['search'])) {
      $valueToSearch = $_POST['valueToSearch'];

      $query = "SELECT * FROM PatientEventLog WHERE CONCAT(`patientEventLogID`, `patientEventID`, `patientID`, `eventCreatorID`, `eventEdited`, `antibiotic`, `waterBroke`, `vertex`, `cervixDilation`, `effacedPercent`, `station`, `membranes`, `analgesia`, `augment`, `notes`, `visible`) LIKE '%" . $valueToSearch ."%'";
      echo "value to search:   \n";
      echo $valueToSearch;
      echo "\n \n       above search resluts";
      echo $query;
      echo "below search results";
      $search_result = filterTable($query);

  } else {
    $query = "select * from PatientEventLog";
    $search_result = filterTable($query);

  }

  function filterTable($query) {
    $connect = mysqli_connect("localhost", "root", "GMd-eUJ-L6m-RxF", "lndappDB");
    $filter_results = mysqli_query($connect, $query);
    echo $filter_results;
    return $filter_results;

  }
?>

Why would this like statement not be picking up these NULL values?

Sidenote: this is vulnerable to SQL injection - https://stackoverflow.com/questions/601300/what-is-sql-injection — Elle H, Feb 08 '18 at 14:36
@DerekH I did understand that, I just wanted to get it working first. Also this is an admin site, so it will require proper credentials to even access this page, which is why I am less worried about it — Drew, Feb 08 '18 at 14:51

vpalade · Accepted Answer · 2018-02-08T14:48:44.723

Use COALESCE operator:

$query = "SELECT * FROM PatientEventLog 
  WHERE CONCAT(
  COALESCE(`patientEventLogID`,''), 
  COALESCE(`patientEventID`,''), 
  COALESCE(`patientID`,''), 
  COALESCE(`eventCreatorID`,''), 
  COALESCE(`eventEdited`,''), 
  COALESCE(`antibiotic`,''), 
  COALESCE(`waterBroke`,''), 
  COALESCE(`vertex`,''), 
  COALESCE(`cervixDilation`,''),
  COALESCE(`effacedPercent`,''), 
  COALESCE(`station`,''), 
  COALESCE(`membranes`,''), 
  COALESCE(`analgesia`,''), 
  COALESCE(`augment`,''), 
  COALESCE(`notes`,''), 
  COALESCE(`visible`,'')
) LIKE '%" . $valueToSearch ."%'";

But will be better to use $valueToSearch as parameter to avoid MySQL injection, here is an example: How can I prevent SQL injection in PHP?

PHP search bar to filter HTML table breaks on NULL

1 Answers1