Pulling Data from database using PDO

PHP
1

Dear All,
I am trying to pull data from the database using PDO, is the code below safe from an attack or do I need to bind anything? If I need to bind anything, please help me write the correct code because all the videos that I bought on e-commerce used mysqli and it is the procedural way without validating anything in the name of keeping things simple.

    function productsInProductCategories() { 
    global $connection;   
   
    if(isset($_GET['p_cat'])){

        $p_cat_id = $_GET['p_cat'];

        $get_p_cat = "SELECT * FROM product_categories WHERE p_cat_id='$p_cat_id'";

        $run_p_cat = $connection->query($get_p_cat);

        $row_p_cat = $run_p_cat->fetch();

        $p_cat_title = $row_p_cat['p_cat_title'];

        $p_cat_desc = $row_p_cat['p_cat_desc'];

        $get_products = "SELECT * FROM products WHERE p_cat_id='$p_cat_id'";

        $run_products = $connection->query($get_products);

        $count = $run_products->rowCount();

        if($count==0){

            echo "

            <div class='box'>

            <h1> No Product Found In This Product Category </h1>

            </div>

            ";

        }else{

            echo "

            <div class='box'>

            <h1>$p_cat_title</h1>

            <p>$p_cat_desc</p>

            </div>

            ";

        }

        while($row_products = $run_products->fetch()){

            $pro_id = $row_products['product_id'];

            $pro_title = $row_products['product_title'];

            $pro_price = $row_products['product_price'];

            $pro_img1 = $row_products['product_img1'];

            echo "
                <div class='col-md-4 col-sm-6 single'>
                    <div class='product'>
                        <a href='details.php?pro_id=$pro_id'>
                        <img
                            src='admin_area/product_images/$pro_img1'
                            class='img-fluid'
                        />
                        </a>

                        <div class='text'>
                        <h3><a href='details.php?pro_id=$pro_id'>$pro_title</a></h3>

                        <p class='price'>$$pro_price</p>

                        <p class='buttons'>
                            <a href='details.php?pro_id=$pro_id' class='btn btn-outline-primary'>View details</a>

                            <a href='details.php?pro_id=$pro_id' class='btn btn-primary'>
                            <i class='fa fa-shopping-cart'></i> Add to cart
                            </a>
                        </p>
                        </div>
                    </div>
                </div>
            
            ";

        }


    }
}

Please, help me with the code that will work instead of just suggesting what to do, it will help me write the code faster and also make it more clearer.

2

First thing is you need to be using prepared statements.

1 Like
3

This is often quoted as a good source for learning PDO :

1 Like
4

Thanks for the link, I know how to insert data with prepare statement in PDO but what I don’t rely know is how to pull data from database using prepare statement, if anyone can help with the code above it will make it more clearer to me and and time safer, then I will continue from there in my project.

5

No, it’s not safe at all.
Here is the problem:-

The data from $_GET can be anything at all the user wants to put in it. It’s just a case of them writing the URL with a harmful value to the p_cat query string.

www.example.com?p_cat=some-nasty-sql-injection

And you submit that query to your database…

It’s safer to use a prefpared statement:-

        $p_cat_id = intval($_GET['p_cat']); // ensures the value in a integer

        $get_p_cat = "SELECT * FROM product_categories WHERE p_cat_id = ?"; // Use a ? placeholder for the variable value

        $run_p_cat = $connection->prepare($get_p_cat); // Sends the statement to the database

        $run_p_cat->execute([$p_cat_id]); // Execute the query, passing in the value

        $row_p_cat = $run_p_cat->fetch(); // Fetch the data

How does that help?
An injection attack can work by modifying the statement you make, possibly by ending it, then starting a whole new statement, all within the value passed in.
But when you prepare a statement, the DB knows what the whole structure of the query will look like from start to finish, excluding the user submitted values. So it’s all there hard-coded, free of tampering. In this case it’s a single select query. There is no second query that drops a table or fetches passwords from the user table, it just does what you wanted it to and nothing more.
Only after then, you pass in the user data. Because the structure of the whole statement is already defined, that user data will only ever be considered as a value to search for, it can’t be a termination of the initial query, followed by a second spurious query, because that’s not how the pre-defined, hard-coded, untampered statement was.