Incorrect query results in PHP

Question

I'm trying to query a database containing information on animals, doing this by searching the table's records for either the animal, or animal category. For example, input could be "Lion" searched in the AnimalName field, or "Mammal" in the Category field. However, while my code accurately reads the value entered and stores it using $searchItem as desired, it does not receive a response from the database, even if the data entered matches a record. It will always return no result (stored in $result), regardless of what is searched.

Some help in finding out what is wrong with my code would be greatly appreciated, if any more information is needed please ask. Code run when data is input, note that the 'input_type' stores whether the search is being made based on AnimalName or Category, and 'searched_item' stores the string being searched for:

<?php

    /*The "animaldatabase" database is being accessed, with the "animal" table being queried.*/

    $searchType = $_GET['input_type'];
    $searchItem =  "'" . filter_var($_GET['searchedItem'], FILTER_SANITIZE_STRING) . "'"; 
    $host="localhost";
    $username="root";
    $pword="root";
    $database="animaldatabase";

    require("db_connection.php");
    if($searchType == "animal"){
        $sql="SELECT * FROM animaltable WHERE AnimalName = $searchItem";
    }
    if($searchType == "category"){
        $sql="SELECT * FROM animaltable WHERE Category = $searchItem";
    }

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

And the linked database connection code:

<?php 
//connect to the database using mysqli API
$mysqli = new mysqli($host, $username, $pword, $database);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" .
    $mysqli->connect_errno . ") "
    . $mysqli->connect_error;
    die;
} 
?>

Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). — John Conde, Apr 03 '17 at 20:19
You don't know what's wrong because you don't check for errors in your code. Never assume the code is always going to work flawlessly. Use [`mysqli_error()`](http://php.net/manual/en/mysqli.error.php) to get a detailed error message from the database. — John Conde, Apr 03 '17 at 20:19
In addition to that, using prepared statements will prevent strange oddities with injecting values into your query. Such as, if a name as an `'` in it, breaking your script. On that note, your script is likely not working because you aren't quoting the `$searchItem`. But *please* use prepared statements... — Siyual, Apr 03 '17 at 20:21
Did you look at the values of `$sql` in the debugger right before the call to `$mysqli->query()`? I think if you do you will see why this code can never work. — Hogan, Apr 03 '17 at 20:21

Incorrect query results in PHP

0 Answers0