Binding PDO Database Parameters With PHP

Binding PDO Database Parameters With PHP

The purpose of this tiny tutorial is to enforce how to bind parameters properly when using PDO for all of your database queries. For starters, as you have read, binding parameters to prepared statements puts potential SQL injections on the backburner. The process is rather simple. You create a query and use something like ‘:my_parameter’.

The parameter can be any variable. However, if you plan to use $_POST or $_SESSION variables, you must must set a variable equal to their value first. Thus, you plan to use a $_SESSION variable parameter like ‘$_SESSION[‘session_id’]’, you would write the code as shown below.

$my_session = $_SESSION['session_id']; 
$result->bindParam(':my_session', $my_session);

You cannot write it like the code below.

$result->bindParam(':my_session', $_SESSION['session_id']);

One more point to make is that you cannot bind parameters that will not be used. As you see below,  ‘:my_name’ is not used in the mySQL query.

$command = "SELECT DISTINCT id, url FROM urls WHERE enabled='1' AND user_id =:my_session";

    $result = $PDO->prepare($command);
    $result->bindParam(':my_session', $my_session)
    $result->bindParam(':my_name', $my_name);
    $result->execute();
}

Unlike the code above, the code shown below will work since the bindParam() is using all parameters that exist in the mySQL query.

$command = "SELECT DISTINCT id, url FROM urls WHERE enabled='1' AND user_id =:my_session";

    $result = $PDO->prepare($command);
    $result->bindParam(':my_session', $my_session)
    //$result->bindParam(':my_name', $my_name);
    $result->execute();
}

January 16, 2015 by kent