Need help converting SQL query to Prepared Statement

Question

I am currently struggling to convert my SQL query statements to Prepared Statement. I hope I'll be able to receive some help.

There are 2 which I need to convert to Prepared Statement:

$query = "SELECT DISTINCT catename FROM catetable";
$queryStatement = mysqli_query($connection, $query);

<select name="bsearch" id="bsearch">
<?php while ($one_model= mysqli_fetch_assoc($queryStatement)  ) { ?> 
 <option value="<?php echo $one_model['catename']; ?>">
    <?php echo $one_model['catename']; ?>
 </option>

and also

        $filter = "";
        if(isset($_POST['bsearch']))
        {
            $mod_selected = $_POST['bsearch'];
            $filter = " WHERE catename ='$mod_selected'";
        }

        $sql = "SELECT a.name, a.picture, b.catename, a.description, a.price, a.id FROM bikeproduct a INNER JOIN catetable b ON a.cid = b.cateid". $filter;
        $bike_list = mysqli_query($connection,$sql);

How will I be able to change create a bind_param for the second code? It'll be nice to have some explanations too. Thank you.

Your first query doesn't need to be prepared - there's no variable content. What you do with the second query depends on what you put in `$filter` — Tangentially Perpendicular, Feb 10 '21 at 07:11
@TangentiallyPerpendicular Hi, for the first question, what if I want to echo it like this (in the updated section)? — Nicole, Feb 10 '21 at 09:37
you asked about SQL Injection. How you handle the data you retrieve is a completely different question. — Tangentially Perpendicular, Feb 10 '21 at 11:03
@Tangentially Perpendicular Ahh alright. Thank you very much. I'll take this as a learning point. — Nicole, Feb 10 '21 at 11:16

Strawberry · Answer 1 · 2021-02-10T09:36:53.207

$query = "
SELECT a.name
     , a.picture
     , a.description
     , a.price
     , a.id 
  FROM bikeproduct a 
  JOIN catetable b 
    ON a.cid = b.cateid
 WHERE b.catename = ?
";

$stmt = $pdo->prepare($query);
$stmt->execute($_POST['bsearch']);
$result = $stmt->fetch();

This assumes a connection script is provided. Typically this is a separate document held outside the htdocs environment, and which might look something like this:

<?php
--my_pdo_connection_statements.inc

$host = 'localhost';
$db   = 'your_database';
$user = 'user';
$pass = 'L0nG&complicated_passW0rd';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

?>

This is then included within your code by means of require() or include():

require('path/to/my_pdo_connection_statements.inc');

So $pdo is simply the object which manages the connection.

Hi, may I know if there is any other method that do not require pdo? Thank you — Nicole, Feb 10 '21 at 09:36

Need help converting SQL query to Prepared Statement

1 Answers1