Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Using Pdo For Database Access (Beginner)

connect to database using PDO PDO Database Connection Secured Exceptions mysqli

  • Please log in to reply
8 replies to this topic

#1 papabear

papabear

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 472 posts
  • Location:DarkSide

Posted 12 April 2012 - 03:46 PM

This is my first tutorial in this forum :) In here I will be explaining the use of PDO and why you must use it..?

This is a beginner's look in to PDO. For a more in depth look at some of the powerful features PDO provides, such as class extension and transactions, please look at PDO: Connections and Abstraction.

Many of the you learned on how to communicate into the database using the MYSQL or maybe the MYSQLi extensions. Since the release of PHP 5.1 they put up a new function that you can use to connect and manipulate your database and for ME it's the best way they call it PHP Data Object (PDO) it provide methods for prepared statements and working from objects will make you more productive.


You can also use PDO to connect to various databases ...

Database Support

- PDO_FIREBIRD ( Firebird/Interbase 6 )
- PDO_INFORMIX ( IBM INFORMIX DYNAMIC SERVER )
- PDO_DBLIB ( FreeTDS / MICROSOFT SQL SERVER / Sybase )
- PDO_IBM ( IBM DB2 )
- PDO_MYSQL ( MySQL 3.x / 4.x / 5.x )
- PDO_ODBC ( ODBC v3 )
- PDO_OCI ( Oracle Call Interface )
- PDO_PGSQL ( PostgreSQL )
- PDO_SqLite ( SQLITE 3 and SQLITE 2 )
- PDO_4D ( 4D )

NOTE : All of this drivers are not necessarily available on your system; PDO provides function to check what are the available databases into your system.


print_r(PDO::getAvailableDrivers());

Connecting into your database

Since many of you are using MYSQL for database , my example below is for MYSQL database connection only.


<?php
	   //define some contstant
	   define( "DB_DSN", "mssql:host=localhost;dbname=databaseName" );
	   define( "DB_USERNAME", "databaseUsername" );
	   define( "DB_PASSWORD", "databasePassword" );


	  //connect
	  try {

		  $con = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD ); //our new PDO Object

	   } catch (PDOException $e) {
			  echo $e->getMessage(); //catch and show the error
	   }

?>

Brief Explanation of the code above

I declare some constant to handle our database type, and database connection string. We are going to use those constants all over the tutorial.

If you can see I use try/catch, always do wrap your pdo operations because this is a BEST PRACTICE to catch the error message and display it for debugging purposes

connection - I created a new PDO object and assigned it into the variable $con short for Connection, we will be using this variable throughout the tutorial.

To close the connection you can use

$con = null;

Assigning NULL into our connection object will close the connection.


To know more connection strings for different databases you can always go here http://www.php.net/m...pdo.drivers.php

Handling Exceptions

Now that you know on how to use try/catch to catch the Exceptions we will now Handle what message or error it will display. You can force PDO into one of three Error Modes using the following.

$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );


NOTE: Never forget to wrap your PDO operations with try/catch :)


SILENT
This is the default error mode. Using this method is like MYSQL and MYSQLi used to show errors. Using the other two error modes are more ideal than the default.

WARNING
This will display a default PHP warning and will allow the program to continue. It's good for debugging purposes.

EXCEPTION
This is the most secured mode where it fires an exception and hide the data that might help someone to exploit your system.

Here's an example code for using the error modes

try {
	 $con = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORLD ); //oooopss I mistyped PASSWORD into PASSWORLD..
	 $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

	 $con->prepare( "SELECT * FROM Users" );
} catch ( PDOException $e ) {
	echo "I'm sorry there is a problem with your operation..";
	file_put_contents( 'dbErrors.txt', $e->getMessage(), FILE_APPEND );
}

If you've noticed I intentionally typed WORLD instead of WORD . The Exception will send the Error message into a file named "dbErrors.txt" and display a friendly message to the user instead of showing your full error.

INSERT AND UPDATE

In this section we will learn on how to Insert and Update database records.

Here is an example of a basic insert

$stmt = $con->prepare( "Insert Into users ( username ) Values ( 'papabear' )" );
$stmt->execute();

How easy is that? :)

Here's an example for update

$stmt = $con->prepare( "UPDATE users SET username = 'papabear'" );
$stmt->execute();

The examples above are called "Prepared Statements"

Prepared Statements - is a precompiled SQL statements that can be executed multiple times by sending just the data to the server. It has the added advantage of automatically making the data used in the placeholders safe from SQL injection attacks.



Example of placeholders

Named Placeholders

$stmt = $con->prepare( "UPDATE users SET username = :user" );
$stmt->bindParam(":user", 'Papabear');


This method of placehold is what I've always use for code readability purposes :)

Unnamed Placeholders


$stmt = $con->prepare( "Insert Into users ( firstname, lastname ) Values ( ?, ? )" );
$stmt->bindParam(1, 'Alleo');
$stmt->bindParam(2, 'Indong');


I used this method if I'm lazy to type named placeholders :)


NOTE: $stmt for STATEMENT HANDLE


placeholders are just like variables that contain values.. and those values can be found on the bindParam method.


SELECTING DATA
You can obtain the data using ->fetch(), a method of your statement handle. There are 8 modes on how do you want to FETCH your method. You can always tell PDO what fetching method you want.


  • PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set
  • PDO::FETCH_BOTH (default): returns an array indexed by both column name and 0-indexed column number as returned in your result set
  • 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 with the PDOStatement::bindColumn() method
  • PDO::FETCH_CLASS: returns a new instance of the requested class, mapping the columns of the result set to named properties in the class. If fetch_style includes PDO::FETCH_CLASSTYPE (e.g. PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE) then the name of the class is determined from a value of the first column.
  • PDO::FETCH_INTO: updates an existing instance of the requested class, mapping the columns of the result set to named properties in the class
  • PDO::FETCH_LAZY: combines PDO::FETCH_BOTH and PDO::FETCH_OBJ, creating the object variable names as they are accessed
  • PDO::FETCH_NUM: returns an array indexed by column number as returned in your result set, starting at column 0
  • PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names returned in your result set



In order to set the fetch mode the following code is used.

$stmt->setFetchMode(PDO::FETCH_ASSOC);

here is a basic tutorial using the FETCH ASSOC MODE

$stmt = $con->prepare("SELECT firstname, lastname FROM users WHERE firstname = :name");
$stmt->bindParam(":name", 'Alleo');

$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->execute();

	while( $row = $stmt->fetch()) {
		   echo $row['lastname'] . ", " . $row['firstname'] . "\n";
	}


Tutorial Ended..
Thank you for reading my simple tutorial about PDO. You can pm me if you encounter some trouble :)


Here's a more advance tutorial about PDO by Alexander -> Advance PDO
  • 3
Life has no CTRL+Z
Never Forget To HIT "LIKE" If I Helped

#2 YumadBro

YumadBro

    CC Regular

  • Member
  • PipPipPip
  • 42 posts

Posted 21 November 2012 - 03:03 AM

Thanks!
  • 1

#3 papabear

papabear

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 472 posts
  • Location:DarkSide

Posted 21 November 2012 - 03:11 AM

Thanks!


Have fun :D
  • 1
Life has no CTRL+Z
Never Forget To HIT "LIKE" If I Helped

#4 YumadBro

YumadBro

    CC Regular

  • Member
  • PipPipPip
  • 42 posts

Posted 21 November 2012 - 03:30 AM

Have fun :D

Is it ok when i pm you sometimes if i need some help? I mean i'm a noob at php and i'm working on a project :S I need to create a login and a registration for my website. It has to be PDO :P Will this tutorial help me with that?
  • 0

#5 papabear

papabear

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 472 posts
  • Location:DarkSide

Posted 25 April 2013 - 01:55 AM

Is it ok when i pm you sometimes if i need some help? I mean i'm a noob at php and i'm working on a project :S I need to create a login and a registration for my website. It has to be PDO :P Will this tutorial help me with that?

 

yes, you can pm me anytime  :biggrin:


  • 0
Life has no CTRL+Z
Never Forget To HIT "LIKE" If I Helped

#6 JasonKnight

JasonKnight

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 312 posts
  • Location:Keene, NH
  • Programming Language:C, C++, JavaScript, Delphi/Object Pascal, Pascal, Assembly, Others

Posted 26 April 2013 - 10:18 AM

You know, I keep seeing people do this and I really have to ask WHY?!?!? WHY would you put the connection info including UN and PW into superglobal scope with DEFINE?!? That's like "security, what's that" should you JHVH forbid end up with a code elevation.

I know turdpress does it, doesn't make it good practice.

Though I know I'm overly paranoid on that one, it's programming 101 in interpreted/scripting languages, never put anything security related into global scope, and that's basically what DEFINE does.

Admittedly, I go overboard wrapping it in a function that returns an array, using debug_backtrace to only allow my 'one index to rule them all' and only one function in that index.php be allowed to call it...

But really... DEFINE? REALLY???

Should probably also cover array parsing since it's usually more convenient than bindparam, though LIMIT can be a pain in the rump with it unless you force prepare emulation off.

Edited by JasonKnight, 26 April 2013 - 10:19 AM.

  • 1
The only thing about Dreamweaver that can be considered professional grade tools are the people promoting it's use.

#7 papabear

papabear

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 472 posts
  • Location:DarkSide

Posted 29 April 2013 - 03:29 PM

defines are constants where no one can change it, It's fixed and there will be no accident of changing its value :) you can exclude your configuration file from searchengines as well as throw people away from it using the htaccess, there's a lot of security techniques that you can use, you can also encrypt this using an encrypter.


  • 0
Life has no CTRL+Z
Never Forget To HIT "LIKE" If I Helped

#8 JasonKnight

JasonKnight

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 312 posts
  • Location:Keene, NH
  • Programming Language:C, C++, JavaScript, Delphi/Object Pascal, Pascal, Assembly, Others

Posted 29 April 2013 - 09:25 PM

... and if you have even the slightest code elevation ANYWHERE in the code, they've got the UN/PW/HOST data ...

What ever happened to the good old 'security related data should always be local in scope in a scripting language'?

Setting it global in scope and unable to be unset/discarded? Have we learned NOTHING from 90%+ of Wordpress 2.x exploits? NeverNoSanity? WP winning the Pwnie?

Edited by JasonKnight, 29 April 2013 - 09:27 PM.

  • 1
The only thing about Dreamweaver that can be considered professional grade tools are the people promoting it's use.

#9 Komplexia

Komplexia

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts
  • Location:Sweden
  • Learning:PHP, JavaScript

Posted 17 August 2013 - 06:09 PM

Hi and thanks for this great tutorial!

 

I began to learn PDO today and after several hours I finally came across your tutorial that is simple enough for me to understand and with code that really work. There is only one tiny problem with the code.

This doesn't work:
$stmt->bindParam(":name", 'the_desired_name');

I get this error message: Fatal error: Cannot pass parameter 2 by reference...

I solved it like this:
    $name = 'the_desired_name';
    $stmt->bindParam(":name", $name);
 

Thanks again

Suzanne


  • 0





Also tagged with one or more of these keywords: connect to database using PDO, PDO, Database, Connection, Secured, Exceptions, mysqli