0

I am trying to make a program that asks for a user for their food allergies and what kind of category (Japanese, Chinese, etc) of food they want and then produce a list of restaurants that will accommodate them (displaying restaurant name). Users enter in their allergies by checkboxes they can select and select the category they want to eat by selecting a value in a dropdown. This is my code so far:

    <?php
    $con=mysqli_connect(root,user);
    // Check connection
    if (mysqli_connect_errno())
    {
        echo nl2br("Failed to connect to MySQL: " . mysqli_connect_error() . "\n"); 
    } else { 
        echo nl2br("Established Database Connection \n");
    }
    //escape variables for allergen
    $sanentry=implode(',',$_REQUEST["boxsize"]);
    //escape variables for ethnicites
    $category = mysqli_real_escape_string($con, $_POST['category']);
    var_dump($category);
    //sql select query
    $sql="SELECT r.restaurant_name 
            FROM restaurant as r, 
                 ethnicity as e, 
                 allergen as a 
            WHERE e.restaurant_id=a.restaurant_id 
            AND e.restaurant_id=r.restaurant_id 
            AND a.restaurant_id=r.restaurant_id 
            AND a.allergen LIKE '%".$value."%' 
            AND e.ethnicity LIKE '%".$category."%'";
    $split_allergy=explode(",", $sanentry);
    // var_dump($split_allergy);
    foreach ($split_allergy as $value){
       echo $value;
       $result=mysqli_query($con,$sql);
       $resultCheck=mysqli_num_rows($result);
       if ($resultCheck > 0) {
            while ($row=mysqli_fetch_assoc($result)){
                // echo $row['restaurant_name'] . "<br";
                print_r($row);
            }
       }
    }
    mysqli_close($con);
    $allergen=mysqli_real_escape_string($con, $sanentry);

?>

I put special characters because the allergens and categories were inserted with comma separated strings.The problem I am having is that the allergies are not being accounted for (when I select an allergy that is not associated with a restaurant that restaurant name is still being displayed). However when I change the $category value I get different results. My MYSQL query works when I run it in MYSQL, so it seems be a problem with the php. This leads me to believe something as wrong with my foreach loop (not properly inserting $value into the query when needed).

As a last note my echo $value is showing up correct (displays the correct allergens that were selected).

Does anyone see anything that I don't that could be causing this problem?

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Kiki
  • 63
  • 7
  • I'd recommend storing your allergies as individual rows, and then associating each user with allergies in a **many-to-many** relationship. This will require a [**bridging table**](https://en.wikipedia.org/wiki/Associative_entity), but will eliminate confusion and also the need for things like `explode(",", $sanentry);`. Also, please, use [**prepared statements**](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) to prevent [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) :) – Obsidian Age Mar 18 '18 at 23:33
  • Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) 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) – RiggsFolly Mar 18 '18 at 23:34
  • @RiggsFolly: How, exactly? The only user parameter spliced in the query string is passed through mysqli_real_escape_string(). $value is unset when initially referenced and unused within the loop. – symcbean Mar 18 '18 at 23:40
  • @symcbean That is not secure, try reading the links in my comment – RiggsFolly Mar 18 '18 at 23:41
  • No - you read them: the attack cited will not work for a quoted value. – symcbean Mar 18 '18 at 23:42

0 Answers0