mysql_ to pdo: did i do it right?

Question

I just switched a old mysql_* from a old tutorial to PDO and wanted to know if im doing it right. I did'nt get the mysql_* and PDO, are they drivers or just different variants to fetch data?

My code work as it should but im kinda sceptical that it work because im a beginner.

  <?php
  // New PDO variant   

  try {
     $user = "user";
     $pass = "";

     $pdo = new PDO('mysql:host=localhost;dbname=testdb', $user, $pass);

     //build query
     $age = $_GET['age'];
     $sex = $_GET['sex'];
     $wpm = $_GET['wpm'];

     $query = "SELECT * FROM ajax_example WHERE sex = '$sex'";

     if(is_numeric($age))
     $query .= " AND age <= $age";

     if(is_numeric($wpm))
     $query .= " AND wpm <= $wpm";

     $stmt = $pdo->prepare($query);

     $display_string = "<table>";
     $display_string .= "<tr>";
     $display_string .= "<th>Name</th>";
     $display_string .= "<th>Age</th>";
     $display_string .= "<th>Sex</th>";
     $display_string .= "<th>WPM</th>";
     $display_string .= "</tr>";

     $stmt->execute(array('name' => $name));

     foreach ($stmt as $row) {
        $display_string .= "<tr>";
        $display_string .= "<td>$row[name]</td>";
        $display_string .= "<td>$row[age]</td>";
        $display_string .= "<td>$row[sex]</td>";
        $display_string .= "<td>$row[wpm]</td>";
        $display_string .= "</tr>";
     }

     echo "Query: " . $query . "<br />";

     $display_string .= "</table>";
     echo $display_string;
     $dbh = null;

  } catch (PDOException $e) {
     print "Error!: " . $e->getMessage() . "<br/>";
     die();
  }
  ?>

i don't know.. must be a leftover from tutorial.. how can i improve it? — sinankarateke, Jun 29 '17 at 10:49
There are a fair chunk of issues here.. One of which is your SQL Queries can be SQL Injected. Look into binding your params. — Option, Jun 29 '17 at 11:34

score 0 · Answer 1 · answered Jun 29 '17 at 10:52

You should use preparedstatement and pass the parameters with ?, e.g.:

$sth = $dbh->prepare('SELECT * FROM ajax_example WHERE sex = ?');
$sth->execute(array('male'));

Query and parameters will (obviously) change depending on the values of $age and $wpm but using prepared statement and binding parameters will help prevent SQL Injection.

score 0 · Accepted Answer · answered Jun 29 '17 at 13:55

You'we almost correct, you just missed the point of prepare()

<?php
// New PDO variant   
try {
    $user = "user";
    $pass = "";

    $pdo = new PDO('mysql:host=localhost;dbname=testdb', $user, $pass);

    //build query
    $age = intval($_GET['age']);
    $sex = $_GET['sex'];
    $wpm = intval($_GET['wpm']);

    $query = "SELECT * FROM ajax_example WHERE sex = ? AND age <= ? AND wpm <= ?";
    $stmt  = $pdo->prepare($query);

    $stmt->execute(array($sex,$age,$wpm));

    $results = $stmt->fetchall();
    if (count($results > 0)) {
        echo "<table>";
        echo "<tr>";
        echo "<th>Name</th>";
        echo "<th>Age</th>";
        echo "<th>Sex</th>";
        echo "<th>WPM</th>";
        echo "</tr>";
        foreach ($results as $row) {
            echo "<tr>";
            echo "<td>" . $row['name'] . "</td>";
            echo "<td>" . $row['age'] . "</td>";
            echo "<td>" . $row['sex'] . "</td>";
            echo "<td>" . $row['wpm'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }else{

        echo "no results available";
    }
}
catch (PDOException $e) {
    echo "Error!: " . $e->getMessage() . "<br/>";

}
?>

mysql_ to pdo: did i do it right?

2 Answers2