• Feed RSS

PHP Database Access: Are You Doing It Correctly?

We've covered PHP's PDO API a couple of times here on Nettuts+, but, generally, those articles focused more on the theory, and less on the application. This article will fix that!
To put it plainly, if you're still using PHP's old mysql API to connect to your databases, read on!


What?

It's possible that, at this point, the only thought in your mind is, "What the heck is PDO?" Well, it's one of PHP's three available APIs for connecting to a MySQL database. "Three," you say? Yes; many folks don't know it, but there are three different APIs for connecting:
  • mysql
  • mysqli – MySQL Improved
  • pdo – PHP Data Objects
The traditional mysql API certainly gets the job done, and has become so popular largely due to the fact that it makes the process of retrieving some records from a database as easy as possible. For example:
/*
 * Anti-Pattern
 */

# Connect
mysql_connect('localhost', 'username', 'password') or die('Could not connect: ' . mysql_error());

# Choose a database
mysql_select_db('someDatabase') or die('Could not select database');

# Perform database query
$query = "SELECT * from someTable";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

# Filter through rows and echo desired information
while ($row = mysql_fetch_object($result)) {
    echo $row->name;
}
Yes, the code above is fairly simple, but it does come with its significant share of downsides.
  • Deprecated: Though it hasn't been officially deprecated – due to widespread use – in terms of best practice and education, it might as well be.
  • Escaping: The process of escaping user input is left to the developer – many of which don't understand or know how to sanitize the data.
  • Flexibility: The API isn't flexible; the code above is tailor-made for working with a MySQL database. What if you switch?
PDO, or PHP Data Objects, provides a more powerful API that doesn't care about the driver you use; it's database agnostic. Further, it offers the ability to use prepared statements, virtually eliminating any worry of SQL injection.

How?

When I was first learning about the PDO API, I must admit that it was slightly intimidating. This wasn't because the API was overly complicated (it's not) – it's just that the old myqsl API was so dang easy to use!
Don't worry, though; follow these simple steps, and you'll be up and running in no time.

Connect

So you already know the legacy way of connecting to a MySQL database:
# Connect
mysql_connect('localhost', 'username', 'password') or die('Could not connect: ' . mysql_error());
With PDO, we create a new instance of the class, and specify the driver, database name, username, and password – like so:
$conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
Don't let that long string confuse you; it's really very simple: we specify the name of the driver (mysql, in this case), followed by the required details (connection string) for connecting to it.
What's nice about this approach is that, if we instead wish to use a sqlite database, we simply update the DSN, or "Data Source Name," accordingly; we're not dependent upon MySQL in the way that we are when use functions, like mysql_connect.

Errors

But, what if there's an error, and we can't connect to the database? Well, let's wrap everything within a try/catch block:
try {
    $conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
That's better! Please note that, by default, the default error mode for PDO is PDO::ERRMODE_SILENT. With this setting left unchanged, you'll need to manually fetch errors, after performing a query.
echo $conn->errorCode();
echo $conn->errorInfo();
Instead, a better choice, during development, is to update this setting to PDO::ERRMODE_EXCEPTION, which will fire exceptions as they occur. This way, any uncaught exceptions will halt the script.
For reference, the available options are:
  • PDO::ERRMODE_SILENT
  • PDO::ERRMODE_WARNING
  • PDO::ERRMODE_EXCEPTION

Fetch

At this point, we've created a connection to the database; let's fetch some information from it. There's two core ways to accomplish this task: query and execute. We'll review both.

Query

/*
 * The Query Method
 * Anti-Pattern
 */

$name = 'Joe'; # user-supplied data

try {
    $conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $data = $conn->query('SELECT * FROM myTable WHERE name = ' . $conn->quote($name));

    foreach($data as $row) {
        print_r($row);
    }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
Though this works, notice that we're still manually escaping the user's data with the PDO::quote method. Think of this method as, more or less, the PDO equivalent to use mysql_real_escape_string; it will both escape and quote the string that you pass to it. In situations, when you're binding user-supplied data to a SQL query, it's strongly advised that you instead use prepared statements. That said, if your SQL queries are not dependent upon form data, the query method is a helpful choice, and makes the process of looping through the results as easy as a foreach statement.

Prepared Statements

/*
 * The Prepared Statements Method
 * Best Practice
 */

$id = 5;
try {
    $conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    

    $stmt = $conn->prepare('SELECT * FROM myTable WHERE id = :id');
    $stmt->execute(array('id' => $id));

    while($row = $stmt->fetch()) {
        print_r($row);
    }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
In this example, we're using the prepare method to, literally, prepare the query, before the user's data has been attached. With this technique, SQL injection is virtually impossible, because the data doesn't ever get inserted into the SQL query, itself. Notice that, instead, we use named parameters (:id) to specify placeholders.
Alternatively, you could use ? parameters, however, it makes for a less-readable experience. Stick with named parameters.
Next, we execute the query, while passing an array, which contains the data that should be bound to those placeholders.
$stmt->execute(array('id' => $id));
An alternate, but perfectly acceptable, approach would be to use the bindParam method, like so:
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();

Specifying the Ouput

After calling the execute method, there are a variety of different ways to receive the data: an array (the default), an object, etc. In the example above, the default response is used: PDO::FETCH_ASSOC; this can easily be overridden, though, if necessary:
while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
    print_r($row);
}
Now, we've specified that we want to interact with the result set in a more object-oriented fashion. Available choices include, but not limited to:
  • PDO::FETCH_ASSOC: Returns an array.
  • PDO::FETCH_BOTH: Returns an array, indexed by both column-name, and 0-indexed.
  • PDO::FETCH_BOUND: Returns TRUE and assigns the values of the columns in your result set to the PHP variables to which they were bound.
  • PDO::FETCH_CLASS: Returns a new instance of the specified class.
  • PDO::FETCH_OBJ: Returns an anonymous object, with property names that correspond to the columns.
One problem with the code above is that we aren't providing any feedback, if no results are returned. Let's fix that:
$stmt->execute(array('id' => $id));

# Get array containing all of the result rows
$result = $stmt->fetchAll();

# If one or more rows were returned...
if ( count($result) ) {
    foreach($result as $row) {
        print_r($row);
    }
} else {
    echo "No rows returned.";
}
At this point, our full code should look like so:
$id = 5;
  try {
    $conn = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
    $stmt = $conn->prepare('SELECT * FROM myTable WHERE id = :id');
    $stmt->execute(array('id' => $id));

    $result = $stmt->fetchAll();

    if ( count($result) ) {
      foreach($result as $row) {
        print_r($row);
      }
    } else {
      echo "No rows returned.";
    }
  } catch(PDOException $e) {
      echo 'ERROR: ' . $e->getMessage();
  }

Multiple Executions

The PDO extension becomes particularly powerful when executing the same SQL query multiple times, but with different parameters.
try {
  $conn = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  # Prepare the query ONCE
  $stmt = $conn->prepare('INSERT INTO someTable VALUES(:name)');
  $stmt->bindParam(':name', $name);

  # First insertion
  $name = 'Keith';
  $stmt->execute();

  # Second insertion
  $name = 'Steven';
  $stmt->execute();
} catch(PDOException $e) {
  echo $e->getMessage();
}
Once the query has been prepared, it can be executed multiple times, with different parameters. The code above will insert two rows into the database: one with a name of “Kevin,” and the other, “Steven.”

CRUD

Now that you have the basic process in place, let’s quickly review the various CRUD tasks. As you’ll find, the required code for each is virtually identical.

Create (Insert)

try {
  $pdo = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $stmt = $pdo->prepare('INSERT INTO someTable VALUES(:name)');
  $stmt->execute(array(
    ':name' => 'Justin Bieber'
  ));

  # Affected Rows?
  echo $stmt->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();

Update

$id = 5;
$name = "Joe the Plumber";

try {
  $pdo = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $stmt = $pdo->prepare('UPDATE someTable SET name = :name WHERE id = :id');
  $stmt->execute(array(
    ':id'   => $id,
    ':name' => $name
  ));

  echo $stmt->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}

Delete

$id = 5; // From a form or something similar

try {
  $pdo = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $stmt = $pdo->prepare('DELETE FROM someTable WHERE id = :id');
  $stmt->bindParam(':id', $id); // this time, we'll use the bindParam method
  $stmt->execute();

  echo $stmt->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}

Object Mapping

One of the neatest aspects of PDO (mysqli, as well) is that it gives us the ability to map the query results to a class instance, or object. Here’s an example:
class User {
  public $first_name;
  public $last_name;

  public function full_name()
  {
    return $this->first_name . ' ' . $this->last_name;
  }
}

try {
  $pdo = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $result = $pdo->query('SELECT * FROM someTable');

  # Map results to object
  $result->setFetchMode(PDO::FETCH_CLASS, 'User');

  while($user = $result->fetch()) {
    # Call our custom full_name method
    echo $user->full_name();
  }
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}

Closing Thoughts

Bottom line: if you’re still using that old mysql API for connecting to your databases, stop. Though it hasn’t yet been deprecated, in terms of education and documentation, it might as well be. Your code will be significantly more secure and streamlined if you adopt the PDO extension.
read more

How to Process Credit Cards with PayPal Payments Pro Using PHP

PayPal is one of the most popular payment processing platforms available today for many reasons. Its ease of use and its connection to the eBay platform are just the tip of the iceberg. While one of its most popular features is the ability to simply sign in to your PayPal account to submit payments, merchants using PayPal can also accept credit cards directly just like a traditional merchant account solution would provide.

PayPal calls this solution Payments Pro, and I’m going to show you exactly how you can process credit cards directly with PayPal’s API using their Payments Pro web service API’s.

Step 1: Setup the Directory Structure

The first thing I like to do with any project is to create a basic structure organized for the project.  In this case, our structure is very simple as our project will consist of only 2 files:
Directory Structure
 As you might have guessed, we’ll be storing our configuration information in config.php, and we’ll actually handle the processing code in process-credit-card.php.

Step 2: Setup the Config File

Our /includes/config.php file will house our values for the PayPal API information we need including the end-point URL, API version, and our API username, password, and signature that we’ll be using. 
// Set sandbox (test mode) to true/false.
$sandbox = TRUE;

// Set PayPal API version and credentials.
$api_version = '85.0';
$api_endpoint = $sandbox ? 'https://api-3t.sandbox.paypal.com/nvp' : 'https://api-3t.paypal.com/nvp';
$api_username = $sandbox ? 'SANDBOX_USERNAME_GOES_HERE' : 'LIVE_USERNAME_GOES_HERE';
$api_password = $sandbox ? 'SANDBOX_PASSWORD_GOES_HERE' : 'LIVE_PASSWORD_GOES_HERE';
$api_signature = $sandbox ? 'SANDBOX_SIGNATURE_GOES_HERE' : 'LIVE_SIGNATURE_GOES_HERE';
Reviewing the config.php code, you can see that first we set a variable for $sandbox.  For now, we’ll leave this to TRUE because we want to interact with PayPal’s sandbox (test) servers for development purposes.  You’ll need to remember to change this to FALSE when you’re ready to move your project to a live server.
Then, based on the value of $sandbox we’re setting values to other variables for our API information.  You’ll just want to fill in those placeholders with your own details accordingly.  Now we’re ready to build our credit card processing script.

Step 3: Create an API Request

Now we can begin to build our process-credit-card.php page.  The first thing we need to do here is include our config file.
// Include config file
require_once('includes/config.php');
Next, we need to build a name-value-pair string that includes all of the data we need to send PayPal in order to process this payment.  A name-value-pair string looks just like something you might see when passing data via URL parameters.  We just need to make sure our parameter names are in all caps.
PARAM1=value1&PARAM2=value2&PARAM3=value3…etc.
So, you might be thinking to yourself “How do I know what to use for my variable names in my string?”  The good news is PayPal provides very good documentation on this.  We can see all of the possible variables that we can pass PayPal including customer details, order item details, and credit card information.  Some of this information is required in order to process a payment, but many of the variables available are optional.  For demonstration purposes, we’ll keep this pretty simple and just pass the required information.
We’ll store all of our request parameters in an array so that we can loop through this array to easily generate our NVP string.  All requests require the following parameters by default:
  • METHOD – The name of the API call you’re making.
  • USER – The API username
  • PWD – The API password
  • SIGNATURE – The API signature
  • VERSION – The API version
Then you can refer to the PayPal documentation for any API request you’d like to make to see what other parameters should be included.  For the sake of this demonstration, our array will be built as follows.
// Store request params in an array
$request_params = array
     (
     'METHOD' => 'DoDirectPayment',
     'USER' => $api_username,
     'PWD' => $api_password,
     'SIGNATURE' => $api_signature,
     'VERSION' => $api_version,
     'PAYMENTACTION' => 'Sale',
     'IPADDRESS' => $_SERVER['REMOTE_ADDR'],
     'CREDITCARDTYPE' => 'MasterCard',
     'ACCT' => '5522340006063638',
     'EXPDATE' => '022013',
     'CVV2' => '456',
     'FIRSTNAME' => 'Tester',
     'LASTNAME' => 'Testerson',
     'STREET' => '707 W. Bay Drive',
     'CITY' => 'Largo',
     'STATE' => 'FL',
     'COUNTRYCODE' => 'US',
     'ZIP' => '33770',
     'AMT' => '100.00',
     'CURRENCYCODE' => 'USD',
     'DESC' => 'Testing Payments Pro'
     );
You’ll notice we’re using our config variables from config.php, and then I’m simply loading static data for the other values.  In a standard project, though, you’ll most likely be populating these values with form data, session data, or some other form of dynamic data.
Now we can simply loop through this array to generate our NVP string.
// Loop through $request_params array to generate the NVP string.
$nvp_string = '';
foreach($request_params as $var=>$val)
{
 $nvp_string .= '&'.$var.'='.urlencode($val);
}
The value of $nvp_string is now:
METHOD=DoDirectPayment&USER=sandbo*****e.com&PWD=12***74&SIGNATURE=AiKZ******6W18v&VERSION=85.0&PAYMENTACTION=Sale&IPADDRESS=72.135.111.9&CREDITCARDTYPE=MasterCard&ACCT=5522340006063638&EXPDATE=022013&CVV2=456&FIRSTNAME=Tester&LASTNAME=Testerson&STREET=707+W.+Bay+Drive&CITY=Largo&STATE=FL&COUNTRYCODE=US&ZIP=33770&AMT=100.00&CURRENCYCODE=USD&DESC=Testing+Payments+Pro
This string is what we’ll send to PayPal for our request.

Step 4: Send the HTTP Request to PayPal

Now that our NVP string is ready to go we need to send this to the PayPal server to be processed accordingly.  To do this, we’ll use PHP’s CURL methods.
// Send NVP string to PayPal and store response
$curl = curl_init();
  curl_setopt($curl, CURLOPT_VERBOSE, 1);
  curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, FALSE);
  curl_setopt($curl, CURLOPT_TIMEOUT, 30);
  curl_setopt($curl, CURLOPT_URL, $api_endpoint);
  curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
  curl_setopt($curl, CURLOPT_POSTFIELDS, $nvp_string);

$result = curl_exec($curl);
curl_close($curl);
Here you can see that we’ve setup CURL with a few simple options and we’re using our $api_endpoint and $nvp_string variables accordingly.
This data will be sent over to PayPal and we will receive the API response back in our $result variable so that we can see the result and send the user to a successful or failure page based on whether or not the call succeeded or not.

Step 5: Parse the API Response

The value that we get back in $result from the previous step will be an NVP string just like the one we generated and sent to PayPal.  When we run our current script we get a successful response back that looks like this:
TIMESTAMP=2012%2d04%2d16T07%3a59%3a36Z&CORRELATIONID=9eb40cd84a7d3&ACK=Success&VERSION=85%2e0&BUILD=2764190&AMT=100%2e00&CURRENCYCODE=USD&AVSCODE=X&CVV2MATCH=M&TRANSACTIONID=160896645A8111040
One very simple way to parse this result is to use PHP’s parse_str() function.  This will load all of the response data into PHP variables matching the names and values returned in the response.  For example, if we do the following:
// Parse the API response
  $nvp_response_array = parse_str($result);
  
We would end up with access to the following PHP variables:
  • $TIMESTAMP
  • $CORRELATIONID
  • $ACK
  • $VERSION
  • $BUILD
  • $AMT
  • $CURRENCYCODE
  • $AVSCODE
  • $CVV2MATCH
  • $TRANSACTIONID
We can then proceed to use these variables to present information back to our customer, populate values in email receipts we’d like to generate, update database information, or anything else we need to do once an order is completed.
The $ACK value is what will tell us whether or not the API call was successful or not.  Values for $ACK can be:
  • Success
  • SuccessWithWarning
  • Failure
  • FailureWithWarning
  •   You can simply redirect your user where they need to go and show them information based on this value. A failing API call will result in additional parameters that provide information about why the transaction failed.  If I run this test again with an invalid credit card number, for example, I get the following response back from PayPal:
    TIMESTAMP=2012%2d04%2d16T08%3a08%3a52Z&CORRELATIONID=590d41dbb31e0&ACK=Failure&VERSION=85%2e0&BUILD=2764190&L_ERRORCODE0=10527&L_SHORTMESSAGE0=Invalid%20Data&L_LONGMESSAGE0=This%20transaction%20cannot%20be%20processed%2e%20Please%20enter%20a%20valid%20credit%20card%20number%20and%20type%2e&L_SEVERITYCODE0=Error&AMT=100%2e00&CURRENCYCODE=USD
    Now, when we use parse_str() we end up with the following PHP variables available to us:
    • $TIMESTAMP
    • $CORRELATIONID
    • $ACK
    • $VERSION
    • $BUILD
    • $L_ERRORCODE0
    • $L_SHORTMESSAGE0
    • $L_LONGMESSAGE0
    • $L_SEVERITYCODE0
    • $AMT
    • $CURRENCYCODE
    In this case, $ACK shows a Failure so we know the call did not succeed and we can check the error parameters for more details about what went wrong.

    Additional Data Parsing Option

    While the previous method of parsing the response works just fine, I personally prefer to work with data arrays.  As such, I use the following function to convert the PayPal response into an array.
    // Function to convert NTP string to an array
    function NVPToArray($NVPString)
    {
     $proArray = array();
     while(strlen($NVPString))
     {
      // name
      $keypos= strpos($NVPString,'=');
      $keyval = substr($NVPString,0,$keypos);
      // value
      $valuepos = strpos($NVPString,'&') ? strpos($NVPString,'&'): strlen($NVPString);
      $valval = substr($NVPString,$keypos+1,$valuepos-$keypos-1);
      // decoding the respose
      $proArray[$keyval] = urldecode($valval);
      $NVPString = substr($NVPString,$valuepos+1,strlen($NVPString));
     }
     return $proArray;
    }
    
    This allows me to see all of the response data available by simply looking at the contents of the array: If I run my script again now I get the following result on screen:
    Array
    (
        [TIMESTAMP] => 2012-04-16T08:15:41Z
        [CORRELATIONID] => 9a652cbabfdd9
        [ACK] => Success
        [VERSION] => 85.0
        [BUILD] => 2764190
        [AMT] => 100.00
        [CURRENCYCODE] => USD
        [AVSCODE] => X
        [CVV2MATCH] => M
        [TRANSACTIONID] => 6VR832690S591564M
    )
    
    And If I were to cause an error again I get the following:
    Array
    (
        [TIMESTAMP] => 2012-04-16T08:18:46Z
        [CORRELATIONID] => 2db182b912a9
        [ACK] => Failure
        [VERSION] => 85.0
        [BUILD] => 2764190
        [L_ERRORCODE0] => 10527
        [L_SHORTMESSAGE0] => Invalid Data
        [L_LONGMESSAGE0] => This transaction cannot be processed. Please enter a valid credit card number and type.
        [L_SEVERITYCODE0] => Error
        [AMT] => 100.00
        [CURRENCYCODE] => USD
    )
    
    You can see this is a nice, easy to navigate result array that contains everything we might need to move the user through our application and update data sources as necessary.

    Conclusion

    As you can see, processing credit cards using PayPal Payments Pro is actually a very simple procedure.  It just involves a few standard steps for working with API web services, and a basic knowledge of working with array data can help as well. Good luck, and happy coding!
read more