Connecting mysql database using PDO in external PHP file

Question

I have an Index.php which has a form for fetching user details when that form is submitted it fires the data to a new program.php for validation in program.php I've linked db.php in which I've the connection to the database, code of db.php is given below:

<?php
    $link=mysql_connect('localhost', 'root', '') or die ("mysql_connect_error()");
    $dbselect=mysql_select_db('test',$link) or die ("Error while connecting the database");
?>

since using it this way sql injections are possible, so I tried changing it to code given below:

<?php
$hostname='localhost';
$username='root';
$password='';

try
{
    $dbh = new PDO("mysql:host=$hostname;dbname=test",$username,$password);

    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // <== add this line
    $dbh = null;
}
catch(PDOException $e)
{
    echo $e->getMessage();
}
?>

but I am getting an error when I connect submit the form. Inside my program.php I have called db.php by include "db.php";. Since I am new to PDO, I am not sure where am I going wrong.

Updated program.php code

<?php
if($_POST)
{
    include "link_db.php";

    if ($_POST[admin_sign_up])
    {
        $fname=$_POST[fname];
        $lname=$_POST[lname];   
        $id   =$_POST[id];
        $id_pass=$_POST[id_pass];
        $sql="insert into admin_database(fname, lname, id, id_pass) 
        value ('$fname','$lname','$id','$id_pass')";

        mysql_query($sql);

        $error=mysql_error();

        if(empty($error))
        {
            echo "<script>alert('Registration Successful...')</script>";
            header("Location:index.php",true);
        }
        else 
        {
            echo "Registration Failed...<br> Email Id already in use<br>";
            echo "<a href='failed.php'>Click to SignUp again</a>";
        }
    }

    if ($_POST[admin_login])
    {

        $id   =$_POST[id];
        $id_pass=$_POST[id_pass];

        $sql="select * from admin_database where id = '$id' and id_pass= '$id_pass'";
        $result=mysql_query($sql);
        echo mysql_error();
        $row=mysql_fetch_array($result);
        $rowcnt=mysql_num_rows($result);

        if($rowcnt==1)
        {
            session_start();
            $_SESSION['id']=$id;
            $_SESSION['fname']=$row['fname'];
            $_SESSION['lname']=$row['lname'];
            $_SESSION['varn']="Y";
            echo "Login Successfully....";
            header("Location:home.php",true);
        }
        else
        {
            $id   =$_POST[id];
            $id_pass=$_POST[id_pass];
            $sql="insert into adminfailure(id, id_pass, date_time) 
            value ('$id','$id_pass',NOW())";
            mysql_query($sql);
            $error=mysql_error();
            if(empty($error))
            {
                Echo "Invalid Login ID or Password....";
                header("Location:fail.php",true);
            }
            else
            {
                echo "incorrect details";
            }
        }
    }
    if ($_POST[logout])
    {
        header("location:destroy.php",true);
    }
}
?>

Updated Errors which I get

Notice: Use of undefined constant test_sign_up - assumed 'test_sign_up' in B:\XAMPP\htdocs\test\program.php on line 6

Notice: Undefined index: test_sign_up in B:\XAMPP\htdocs\test\program.php on line 6

Notice: Use of undefined constant test_login - assumed 'test_login' in B:\XAMPP\htdocs\test\program.php on line 32

Notice: Use of undefined constant id - assumed 'id' in B:\XAMPP\htdocs\test\program.php on line 35

Notice: Use of undefined constant id_pass - assumed 'id_pass' in B:\XAMPP\htdocs\test\program.php on line 36 No database selected Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in B:\XAMPP\htdocs\test\program.php on line 41

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in B:\XAMPP\htdocs\test\program.php on line 42

Notice: Use of undefined constant id - assumed 'id' in B:\XAMPP\htdocs\test\program.php on line 56

Notice: Use of undefined constant id_pass - assumed 'id_pass' in B:\XAMPP\htdocs\test\program.php on line 57 incorrect details Notice: Use of undefined constant logout - assumed 'logout' in B:\XAMPP\htdocs\test\program.php on line 73

Notice: Undefined index: logout in B:\XAMPP\htdocs\test\program.php on line 73

Answer 1

In your code, you first create a connection to the database, then you set it to null.
Whenever you try to access the $dbh object after that, it will be null.

$dbh = new PDO("mysql:host=$hostname;dbname=test",$username,$password);

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh = null;  // <= Right here.

Remove the $dbh = null; line, and you should be able to use the object as intended.

The $dbh object it not just a "link" as you do in your mysql_* code, but it is a object that you use to call the database, this is not the same object that you use in your mysql_* calls.
i.e., You can not use the earlier mysql_* code and just pass the pdo object into the call instead of the mysql link.
So the code will differ a bit from your earlier code.

Example:

// Earlier code using `mysql_* API`:
$sql="select * from admin_database where id = '$id' and id_pass= '$id_pass'";
$result=mysql_query($sql);
$row=mysql_fetch_array($result);


// Would look something like this using PDO:
$statement = $dbh->prepare('SELECT * FROM admin_database WHERE id =:id AND id_pass =:idpass');
// Here you can either use the bindParam method, or pass the params right into the execute call:
$statement->execute(array('id' => $id, 'idpass' => $id_pass);
$row = $statement->fetch(); 

I'd recommend reading up on PDO in the docs if you have issues with converting the code.

---

Further recommendations:

When you are including a file like this, one you only want to be included once per script run, its always a good idea to make sure that it is only included once. This can be done by using the include_once keyword instead of just include. Now, if you use include, this will include the script if possible, if it cant, it will keep run the script, and the script will crash when you try to use the varaiables set in the file.
Instead of using include in this case, I would recommend using the require (or rather require_once) keyword. Which will include the file, and if it cant, stop execution of the script and display an error message (if you have error reporting on).

Answer 2

You have to change not only db.php but ALL the queries over your code. And always use prepared statements to pass variables to queries. Otherwise PDO won't protect you from injections.

At the moment I am writing tutorial on PDO, it is still incomplete but can give you basics you may start from.