0

I'm creating an advanced search function on my website to search through. I have 5 fields that can be used to search for the specific account (Surname, Forename, Username, Registration Date and Account Rank) and an option for all fields must match or 1 (or more) fields must match. The values entered by the user are posted to a PHP file via a Javascript. The PHP file then completes the search.

Currently, the "AND" search works as blank values will not affect the result of the search as the other values still must match. However, the "OR" does not as the empty search boxes will cause other records to show as they match the "LIKE %".$Variable."%" search when the variable is blank.

I need to figure out a way for the system to ignore empty boxes, however, I'm struggling to find a suitable way that would work without causing errors in the SQL code where "OR" is used in the wrong places. So any suggestions would be nice.

Thanks in advance, Tom

PHP

<?php
//Retrieves variables from Javascript.
$Surname = $_POST["Surname"];
$Forename = $_POST["Forename"];
$Username = $_POST["Username"];
$Joined = $_POST["Joined"];
$Rank = $_POST["Rank"];
$ANDOR = $_POST["ANDOR"];

$data = 0;

include "db/openlogindb.php";
if($DBError == true){
    $data = 3;
}
else{

    if($ANDOR == "AND"){
        $UserSearch = "SELECT username, surname, forename, joined, rank FROM users 
        WHERE surname LIKE '%".$Surname."%' 
        AND forename LIKE '%".$Forename."%'
        AND username LIKE '%".$Username."%'
        AND joined LIKE '%".$Joined."%'
        AND rank LIKE '%".$Rank."%'
        ORDER BY surname";
    }
    else if($ANDOR == "OR"){
        $UserSearch = "SELECT username, surname, forename, joined, rank FROM users 
        WHERE surname LIKE '%".$Surname."%' 
        OR forename LIKE '%".$Forename."%'
        OR username LIKE '%".$Username."%'
        OR joined LIKE '%".$Joined."%'
        OR rank LIKE '%".$Rank."%'
        ORDER BY surname";
    }
    else{
        $data = 2;
    }

    if($data == 0){
        $results = mysqli_query($conn, $UserSearch);

        if(mysqli_num_rows($results) == 0){
            $data = 1;
        }
        else{
            $data = '';

            while($row = mysqli_fetch_assoc($results)){ 
                $data .= '<tr><td>'.$row['surname'].'</td><td>'.$row['forename'].'</td><td>'.$row['username'].'</td><td>'.$row['joined'].'</td><td>'.$row['rank'].'</td><td><button type="button" class="btn btn-block btn-primary btn-xs" onClick="ChangePassOpen(\''.$row['username'].'\')">Change Password</button></td></tr>';
            }
        }
    }
}

include "db/closelogindb.php";

echo $data;
?>

HTML / Javascript http://thomas-smyth.co.uk/admin/accountlist.php

Thomas Smyth
  • 512
  • 3
  • 9
  • 36
  • 5
    You are pretty open to [SQL-Injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php).. – Naruto Nov 16 '16 at 13:17
  • Please see SQL security document. Query Preparation is much more secure than this kind of unescaped query – Bobot Nov 16 '16 at 13:18
  • Yeh, it's on my to do list. Infact I will sort it after I solve this. Though do you have any ideas considering I will still need to solve this issue even when I sort the SQL injection protection. – Thomas Smyth Nov 16 '16 at 13:26
  • 1
    You just want to compile your SQL checking each variable is not blank before you append it to the SQL. But as others have said, look up SQL Injection. – Shaun Parsons Nov 16 '16 at 13:28
  • 1
    can you use default that cannot be found? `$xxx = isset($_POST['xxx']) ? $_POST['xxx'] : 'NULL or 9999999999999'` – Dirk Nachbar Nov 16 '16 at 13:48
  • Brilliant idea! Doing that would prevent me having to over complicate it by finding some way of adding and removing OR statements depending on what conditions are required. – Thomas Smyth Nov 16 '16 at 13:58

2 Answers2

2
<?php
// Create the array to store the variables
$array = array();

//Retrieves variables from Javascript.
//Where $conn is your database connection
if (isset($_POST["Surname"])) $array['surname'] = mysqli_real_escape_string($conn, $_POST["Surname"]);
if (isset($_POST["Forename"])) $array['forename'] = mysqli_real_escape_string($conn, $_POST["Forename"]);
if (isset($_POST["Username"])) $array['username'] = mysqli_real_escape_string($conn, $_POST["Username"]);
if (isset($_POST["Joined"])) $array['joined'] = mysqli_real_escape_string($conn, $_POST["Joined"]);
if (isset($_POST["Rank"])) $array['rank'] = mysqli_real_escape_string($conn, $_POST["Rank"]);
if (isset($_POST["ANDOR"])) $ANDOR = mysqli_real_escape_string($conn, $_POST["ANDOR"]);

$data = 0;

include "db/openlogindb.php";
if($DBError == true){
    $data = 3;
}
else{

//Make a variable to check for the last key in the array
$last_key = end(array_keys($array));

if($ANDOR == 'AND'){
        $UserSearch = "SELECT ";
        foreach ($array as $key => $value)
                {
                    $UserSearch .= $key;
                    if ($last_key != $key) $UserSearch .= ', ';
                }
                $UserSearch .= ' FROM users WHERE ';

                foreach ($array as $key => $value)
                {
                    $UserSearch .= $key . ' LIKE %"' . $value . '"%';
                    if ($last_key != $key) $UserSearch .= ' AND ';
                }
        }
    else if($ANDOR == 'OR'){
        $UserSearch = "SELECT ";
        foreach ($array as $key => $value)
                {
                    $UserSearch .= $key;
                    if ($last_key != $key) $UserSearch .= ', ';
                }
                $UserSearch .= ' FROM users WHERE ';

                foreach ($array as $key => $value)
                {
                    $UserSearch .= $key . ' LIKE %"' . $value . '"%';
                    if ($last_key != $key) $UserSearch .= ' OR ';
                }
        }
    else{
        $data = 2;
    }

    if($data == 0){
        $results = mysqli_query($conn, $UserSearch);

        if(mysqli_num_rows($results) == 0){
            $data = 1;
        }
        else{
            $data = '';

            while($row = mysqli_fetch_assoc($results)){ 
                $data .= '<tr><td>'.$row['surname'].'</td><td>'.$row['forename'].'</td><td>'.$row['username'].'</td><td>'.$row['joined'].'</td><td>'.$row['rank'].'</td><td><button type="button" class="btn btn-block btn-primary btn-xs" onClick="ChangePassOpen(\''.$row['username'].'\')">Change Password</button></td></tr>';
            }
        }
    }
}

include "db/closelogindb.php";

echo $data;
?>
Jacey
  • 639
  • 6
  • 13
  • I've found a fix for what I required, which I'm about to post now. However, does what you have posted include protection for SQL injections? If it does it may be a far better solution that what I currently am testing. I'm just reading up on SQL injections now, though with little success, so I'm still a bit clueless about how to do it, etc. – Thomas Smyth Nov 16 '16 at 15:38
  • 1
    The mysqli_escape_string() function helps prevent SQL injection, assuming you are using a MySQL database. I'm not sure what data format "rank" is but you can make your inputs even more secure by typecasting them like so: – Jacey Nov 16 '16 at 15:48
  • 1
    `if (isset($_POST["Rank"])) (int) $array['rank'] = mysqli_real_escape_string($conn, $_POST["Rank"]);` – Jacey Nov 16 '16 at 15:48
  • 1
    Just to summarize, what my code does is check if a field was filled when the user submits the form. If the field is filled, its value goes into an array. This way, unfilled fields don't get assigned to variables, and don't get added to the database query. The foreach loop takes the fields that were actually filled and uses them to construct the query. If the form field was not filled, that field just isn't part of the query. – Jacey Nov 16 '16 at 15:58
0

This is probably far from perfect, however it does work. I intend to add SQL injection protection now once I read up on it.

<?php
//Retrieves variables from Javascript.
$Surname = $_POST["Surname"];
$Forename = $_POST["Forename"];
$Username = $_POST["Username"];
$Joined = $_POST["Joined"];
$Rank = $_POST["Rank"];
$ANDOR = $_POST["ANDOR"];

if($Surname == ""){
    $Surname = "xxxxxxxxxx";
}
if($Forename == ""){
    $Forename = "xxxxxxxxxx";
}
if($Username == ""){
    $Username = "xxxxxxxxxx";
}
if($Joined == ""){
    $Joined = "xxxxxxxxxx";
}
if($Rank == ""){
    $Rank = "xxxxxxxxxx";
}

$data = 0;

include "db/openlogindb.php";
if($DBError == true){
    $data = 3;
}
else{

    if($ANDOR == "AND"){
        $UserSearch = "SELECT username, surname, forename, joined, rank FROM users 
        WHERE surname LIKE '%".$Surname."%' 
        AND forename LIKE '%".$Forename."%'
        AND username LIKE '%".$Username."%'
        AND joined LIKE '%".$Joined."%'
        AND rank LIKE '%".$Rank."%'
        ORDER BY surname";
    }
    else if($ANDOR == "OR"){
        $UserSearch = "SELECT username, surname, forename, joined, rank FROM users 
        WHERE surname LIKE '%".$Surname."%' 
        OR forename LIKE '%".$Forename."%'
        OR username LIKE '%".$Username."%'
        OR joined LIKE '%".$Joined."%'
        OR rank LIKE '%".$Rank."%'
        ORDER BY surname";
    }
    else{
        $data = 2;
    }

    if($data == 0){
        $results = mysqli_query($conn, $UserSearch);

        if(mysqli_num_rows($results) == 0){
            $data = 1;
        }
        else{
            $data = '';

            while($row = mysqli_fetch_assoc($results)){ 
                $data .= '<tr><td>'.$row['surname'].'</td><td>'.$row['forename'].'</td><td>'.$row['username'].'</td><td>'.$row['joined'].'</td><td>'.$row['rank'].'</td><td><button type="button" class="btn btn-block btn-primary btn-xs" onClick="ChangePassOpen(\''.$row['username'].'\')">Change Password</button></td></tr>';
            }
        }
    }
}

include "db/closelogindb.php";

echo $data;
?>
Thomas Smyth
  • 512
  • 3
  • 9
  • 36