PHP PDO Tutorial for safe database queries - IN-DEPTH

[Wife]

Hello,

So, because I am already being hated by many people I decided to give you something a little bit more productive this time.

1. The Basics

What is PDO?

PDO stands for PHP Data Objects which simply is a object based database library. One of the biggest advantages of PDO apart from it being very secure and light weight (when used correctly, of course) is the fact that it is a abstract level library for database methods and works pretty much widely on every well known database server. The library consists from bunch of classes containing methods and functions for database queries.

Example of usage

$statement = $connection->prepare($query);$statement->execute();

In PDO, when you are passing variables to query, they are defined AFTER preparing the query, while/or before executing it.

Example of variables in PDO query

$query = 'SELECT * FROM users WHERE username = :username'; //We use 'placeholders' :username is a placeholder$statement = $connection->prepare($query);$statement->execute(array(':username' => $username));

So, this way the variable - which can be assigned from HTTP_REQUEST or via static methods, is never passed directly to the query and helps us prevent error based SQL injection.

Fetching data, data types

//Fetching data as an array$result = $statement->fetch(PDO::FETCH_ASSOC);print_r($result);//Alternative, for looping all the results found for statementforeach($statement as $row) {echo '$row['data'];} //So here the result of $statement->execute is assigned as $row and data can be accessed using $row['datafield']; $row['datafield2'];//etc, based on your database table names.//Fetching data as object (I have to include this as we are talking about DATA OBJECTS$result = $statement->fetch(PDO::FETCH_OBJ);echo $result->username;echo $result->othervalue;//Ok so this is the object oriented method, where fetch returns an object where property names are assigned from result.

It is also possible to return all the remaining values from data set by using fetchAll(); method. Usage defined above.

You can see a complete list of PHP datatypes online.

Establishing a database connection

//Okay, this should be pretty straight forward. The connection is handled inside try{} and catch(){} blocks.try {//Variables for username and password$username = 'db_user';$password = 'db_password';//This creates a new PDO object for variable $connection. Connection details are//mysql:host, dbname, $username, $password$connection = new PDO('mysql:host=localhost;dbname=database_name', $username, $password);//Let's set the attribute errormode to pdo error mode exception for our catch() block.$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);} catch (PDOException $error) {//getMessage() us any possible errors with connecting, die(); is for killing the connection.$error->getMessage();$connection->die();}

Part 2. Coming tomorrow, and then we will be actually creating something, a dynamic database SET query using USER_AGENT and then outputting the data. Edited October 9, 2016 by Facial

[Uhtred]

[Wife]

Thanks. Appreciate this.

[venetox]

Oooh, I've been meaning to learn how PDO actually works. Thanks for this, should be helpful.

[Wife]

You are very welcome. I just corrected a mistake where I was trying to call the getMessage function from connection variable, where I was supposed to get it from the PDOException variable error..

Thanks

[Abuse]

3/10 would not read again

 

On a serious note, please improve your formatting

[Wife]

3/10 would not read again

 

On a serious note, please improve your formatting

 

Formatting somehow messed up when correcting an error with phone.

[Prolax]

Does this include mysqli_real_escape_string() function?

[Wife]

Does this include mysqli_real_escape_string() function?

 

Uh? PDO wraps strings automaticly.