1

I am using Mysqli to display all results on my web page. But now i want to add security so i want to use Mysqli prepared statements to fetch data but i am confused how i can achieve it.

My web page URL is like

www.example.com/list.php?type=Car&brand=All

At first i check value in brand field if it is All then i create a variable like

$brand="";

And if it is not All then

$brand="AND brand='$_GET[brand]'";

And my query becomes

SELECT * FROM Table WHERE type='$_GET[type]' $brand;

Its working perfectly for me without prepared statements as if brand value is 'All' It displays all results and if It is something else like 'Maruti' It displays cars with brand name Maruti that are stored in my Table.

Similarly i also have created a variable $where in which where statements are stored.

$where="where type='$_GET[type]' $brand AND status='a'";
    if(isset($_GET['sort'])){
        if(($_GET['sort'])=='ASC'){
            $where="where type='$_GET[type]' $brand AND status='a' ORDER BY price";
        }
        elseif(($_GET['sort'])=='DESC'){
            $where="where type='$_GET[type]' $brand AND status='a' ORDER BY price DESC";
        }
    }

If i don't use these variables '$brand' and '$where' then i will have to repeat it no of times. You can see so far i have used $brand in 4 queries.

I have tried

<?php

    $type='$_GET[type]';
    if($brand!='All'){
        $brand="AND brand= ?";
    }
    else{
        $brand="";
    }
    if ($stmt = $mysqli->prepare("SELECT * FROM Table WHERE type= ? $brand")) {

        /* bind parameters for markers */
        $stmt->bind_param("ss", $type, $brand);

        /* execute query */
        $stmt->execute();

        /* bind result variables */
        $res = $stmt->get_result();

        while ($row = mysqli_fetch_array($res)) {

?>
        <li><?php echo $row['name']; echo '&nbsp'; echo $row['type']; echo '&nbsp'; echo $row['brand']; ?></li>
<?php   
        }
        /* close statement */
        $stmt->close();
    }

    /* close connection */
    $mysqli->close();
?>

Now if brand value is 'All' then it causes error in bind_param statement because $brand is null in that. and if value is something right like 'Maruti' then it displays nothing...No error just empty screen.

Is it not possible to do it in this way or i am doing something wrong in coding, please help me out ??? Thanks in advance...

Ashish
  • 303
  • 5
  • 22
  • 1
    `WHERE type= ? $brand` that won't work, you need an additional placeholder `?` – Funk Forty Niner Feb 09 '17 at 14:36
  • @Fred-ii- Can you please tell me how ?? – Ashish Feb 09 '17 at 14:38
  • 2
    well for one thing `$type='$_GET[type]';` that needs to read as `$type=$_GET['type'];` --- and your `if($brand!='All')` I believe you (may) want to remove the negation `!` character here and replaced with a `==` instead `if($brand=='All')` – Funk Forty Niner Feb 09 '17 at 14:46
  • However, `$stmt->bind_param("ss", $type, $brand);` may fail when trying to do that ^. TBH, I've never used this type of code before so it's a bit confusing for me. You may need to add an additional conditional statement to bind with. I'm thinking outloud here of course. – Funk Forty Niner Feb 09 '17 at 14:48
  • @Fred-ii- Thanks for your efforts...i have gotten idea of using variables like this on this site and the idea is working absolutely for me...now when i want to add security to mysql queries...i am failed to do so...still hope i could help from there... – Ashish Feb 09 '17 at 14:57
  • This might be useful: https://stackoverflow.com/a/52323556/2943403 – mickmackusa Mar 09 '19 at 04:35
  • You can use the splat operator on an array of data in the bind call. The array will conditionally contain `type` and `brand` or only `type`. https://stackoverflow.com/a/71718174/2943403 Your conditional construction of the sql string can remain the same. – mickmackusa Apr 03 '22 at 05:29

0 Answers0