-1

When I search null or space values, all the results from database come to my html table. I wanted to prevent users to search null values or space values.

I tried this post, but now helped me, How to prevent a database search from running on an empty string?

Here is the backend script-

<?php

//fetch.php

$connect = new PDO("mysql:host=localhost;dbname=searchv3", "root", "");

$output = '';

$query = '';

$data = [];

if(isset($_POST["query"]))
{
 $search = str_replace(",", "|", $_POST["query"]);
 $query = "
 SELECT * FROM number_list
 WHERE IMSI REGEXP '".$search."' 
 OR Mobile_no REGEXP '".$search."' 
 OR Backup_date REGEXP '".$search."' 
 OR Sr REGEXP '".$search."' 
 

 ";
}
else
{
 $query = "
 SELECT * FROM number_list order by Sr DESC LIMIT 50;
 ";
}

$statement = $connect->prepare($query);
$statement->execute();

while($row = $statement->fetch(PDO::FETCH_ASSOC))
{
 $data[] = $row;
 
}

echo json_encode($data);
$connect = null;

?>

I will share front-end jav script as well-

<script>
$(document).ready(function(){
 load_data();
function load_data(query)
 {
  $.ajax({
   url:"fetch_numberlist.php",
   method:"POST",
   data:{query:query},
   dataType:"json",
   success:function(data)
   {
    $('#total_records').text(data.length);
    var html = '';
    if(data.length > 0)
        
    {
     for(var count = 0; count < data.length; count++)
     {
      html += '<tr>';
      html += '<td>'+data[count].Sr+'</td>';
      html += '<td>'+data[count].IMSI+'</td>';
      html += '<td>'+data[count].Mobile_no+'</td>';
      html += '<td>'+data[count].Backup_date+'</td>';
     }
    }
    else
    {
            html = '<tr><td colspan="4">No Data Found</td></tr>';
    }
    $('tbody').html(html);
   }
  })
 }

 $('#search').click(function(){
  var query = $('#tags').val();
  load_data(query);
 });
 })
</script>
Akina
  • 39,301
  • 5
  • 14
  • 25
souttawther
  • 23
  • 7
  • Check for NULL/space in PHP code and concatenate conditional expression to the query text only when the check gives false. – Akina Aug 28 '20 at 04:41
  • I recommend you to do it in both sides, the client and the server. In the client, you will have to check if the query string is valid before making the server request. With this you will avoid unnecessary requests (better bandwidth, reduce costs, ...) Also, as the client code can be manipulated, you will also have to do it in the server side. – Victor Molina Aug 28 '20 at 04:56
  • **Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Aug 28 '20 at 11:19

1 Answers1

-1
...

$data = [];

// `??` operator returns value if isset needed post element, else use empty string
// trim - remove start and ending spaces from string. 
//      So it return empty string if user input only space
$post_query = trim($_POST["query"] ?? '');

if($post_query)
{
    $search = str_replace(",", "|", $post_query);

...
Olexandr Kravchenko
  • 32
  • 3