Converting mysql_query to PDO

Question

I am trying to rewrite all my queries to a PDO format. Currently I am trying to rewrite this function, but I can't seem to get it to work.

mysql_query function

    function checkLogin() {

    $this->sQuery = "SELECT * FROM users 
          WHERE gebruikersnaam='" . mysql_real_escape_string($_POST['gebruikersnaam']) . "'
          AND wachtwoord = '" . sha1($_POST['wachtwoord']) . "'";

    $this->rResult = mysql_query($this->sQuery)
            or die("Er is iets misgegaan " . mysql_error());


    if (mysql_num_rows($this->rResult) == 1) {  // login name was found            
        $this->aRow = mysql_fetch_assoc($this->rResult);
        $_SESSION['gebruiker'] = $this->aRow['voornaam'];

        header("location: dashboard.php");
    }
}

And this is how far I've come with the PDO:

       function checkLoginPDO(){
    $connect = new PDO(host, username, password); // Database Connectie maken (De host, username & password zijn in de config.php aan te passen)
    $sql = "SELECT * FROM users 
          WHERE gebruikersnaam='" . mysql_real_escape_string($_POST['gebruikersnaam']) . "'
          AND wachtwoord = '" . sha1($_POST['wachtwoord']) . "'"; 
    $value = $connect->prepare($sql); //Een variabele aanmaken die de PDO vast houdt. Vervolgens word de code voorbereid door de prepare functie
    $value->execute(); 
    if(mysql_num_rows($value->fetch()) == 1){
        $_SESSION['gebruiker'] = $row['voornaam'];
        header("location: dashboard.php");
    }
}

What am I doing wrong/forgetting?

`mysql_real_escape_string()` should have been removed during your conversion process. — George, Feb 27 '13 at 09:47
I removed the real_escape_string function but the after loggin in, i still get a blank screen! — Michael Koelewijn, Feb 27 '13 at 09:49
Matter of fact I've just noticed more mysql_* functions. If you are converting to PDO then you shouldn't be seeing any mysql_*. Also, if you've made changes to your actual code, please update it here so you don't get any repeat answers. — George, Feb 27 '13 at 09:51
( ! ) Fatal error: Uncaught exception 'PDOException' with message 'invalid data source name' in C:\wamp\www\pvb\resources\functions.php on line 44 ( ! ) PDOException: invalid data source name in C:\wamp\www\pvb\resources\functions.php on line 44 Which is: $connect = new PDO(host, username, password); Strange.. because those are correctly defined. — Michael Koelewijn, Feb 27 '13 at 09:52
Read the manual, http://www.php.net/manual/en/pdo.construct.php — Srisa, Feb 27 '13 at 09:54
(because you're a dutch man, you can read [this great tut](http://phptuts.nl/view/27/) too...) — Wouter J, Feb 27 '13 at 09:56
I found it, thank god! my defined 'host' value was 'localhost'. It had to be mysql:host=localhost;dbname=dbname ofcourse! — Michael Koelewijn, Feb 27 '13 at 09:57

score 1 · Accepted Answer · edited Aug 17 '23 at 17:55

It should be something like this:

function checkLoginPDO(){
    $connect = new PDO(host, username, password); // Database Connectie maken (De host, username & password zijn in de config.php aan te passen)
    // define sql query string with special placeholders in the form of ?
    $sql = "SELECT * FROM users 
      WHERE gebruikersnaam=?
      AND wachtwoord =?";
    // prepare statement based on sql query string
    $statement = $connect->prepare($sql);
    // bind first question mark with value from $_POST, first question mark will be replaced with that value
    $statement->bindParam(1, $_POST['gebruikersnaam']);
    // do the same for second question mark
    $statement->bindParam(2, sha1($_POST['wachtwoord']));
    // execute this prepared statement with binded values
    $statement->execute();
    // fetch row from the result in the form of associated array
    if(($row = $statement->fetch(PDO::FETCH_ASSOC))){
        $_SESSION['gebruiker'] = $row['voornaam'];
        header("location: dashboard.php");
    }
    // free statement memory
    $statement = null;
}

Note: Code is not tested.

When working with PDO you should be using its way of dealing with queries and database. Using any of mysql_* functions is not an optimal way of doing this.

score 1 · Answer 2 · edited May 23 '17 at 10:26

First of all, determine which type of error handling you want. PDO defaults to PDO::ERRMODE_SILENT which means that you don't get any errors. I recommend you to use PDO::ERRMODE_EXCEPTION which means that you need to work with try { ... } catch() { ... } blocks around your code.
Secondly, when you are using PDO, you can't use mysql_* functions. So using mysql_real_escape_string is not correct. Furthermore, because you are using prepared statements you don't need any SQL injection protection at all. But you need to use param binding.
You also have some mysql_query around line 7...
PDO does not have a build in mysql_num_rows function. You should put a COUNT(*) statement in your query for that. See also this answer

score -1 · Answer 3 · answered Feb 27 '13 at 09:58

function checkLoginPDO(){
$connect = new PDO(host, username, password); // Database Connectie maken (De host, username & password zijn in de config.php aan te passen)
$sql = "SELECT * FROM users 
      WHERE gebruikersnaam=:gebruikersnaam
      AND wachtwoord = :wachtwoord"; 
$value = $connect->prepare($sql);
$value->bind(':gebruikersnaam',$_POST['gebruikersnaam']);
$value->bind(':wachtwoord',sha1($_POST['wachtwoord']));
$value->execute();
$data = $value->fetchAll();
if(count($data) > 0){
    $_SESSION['gebruiker'] = $data[0]['voornaam'];
    header("location: dashboard.php");
}

}

Converting mysql_query to PDO

3 Answers3