Develop Secure Applications with PHP PDO - Ultimate Guide

Develop Secure Applications with PHP PDO – Ultimate Guide

Develop Secure Applications with PHP PDO, Advantages of choosing PHP PDO is their support for prepared statements which offer important security features.

A prepared statement is a template for an SQL query that incorporates value from user input. The prepared statement contains a placeholder for each value that’s stored in a variable. This not only makes it easier to embed the variables in your PHP code, it also prevents SQL injection attacks, because PHP PDO automatically escapes quotes and other characters before executing the query.

PDO also supports the use of named placeholders and anonymous placeholders.

Placeholders can be used only for column values. They can’t be used for other parts of an SQL query, such as column names or operators.

Using a prepared statement for a single query involves two round trips to the database server. The first time to validate and optimize the SQL, the second to send the values to the placeholders.

1. Introduction to PHP PDO

In this step, you will learn about basic operations in PHP PDO that is connecting to the database with PDO and also writing first SQL query, displaying error messages efficiently in PHP PDO. you will learn these 3 topics in this step with PHP PDO.

1.1 Connecting to Database

In PHP PDO, we can connect to the database by creating new PDO object and we have to pass database type, hostname, database name, database username, database password.

Here I’ve created a DSN variable with database type, here I’m using the MySQL database. And the hostname is localhost, use your hostname here. Next value is database name.

I’m creating database object by instantiating PDO with dsn variable and database username, password.

Here is the code you can use. Save it as the connect.php file.

1.2 First Query in PHP PDO

Here in this step, you will learn how to write first select SQL query and print the fetched records with the print_r PHP function.

I’m using the database table form previous PHP CRUD application. You can use the same table form this article.

Here I’m using query method to execute this select SQL query. After that, I’m assigning to $rows.

This query method only works with foreach loop. I’m using while loop with $rows to get result array. This result array contains result as both numeric array and associative array.

As an example to echo the records, here I’m using an associative array and numeric array with first name and the last name with numeric pocket 2. After putting this code in foreach loop all the full names will be displayed in the browser.

1.3 Working with Errors in PHP PDO

Previously you ran simple SQL query if you change the DB name to something else. You won’t get the correct error message what is wrong with the code.

To get correct error messages, we should catch the errors for that I’ll use a try-catch block. We can catch the errors in two ways that are using the setAttribute method and with the errorInfo method.

Here you learn about both methods. Inside try block I’m using the require_once PHP function to load the connect.php file and after that setting error mode with the setAttribute method.

In catch method, I’m assigning the exception to $e, then catching the errors with exception getMessage method.

If error variable is set, You will see the error messages in browser.

The second method is using the erroInfo method to get the errors, and the actual errors are in the second pocket, so here I’m assigning pocket 2 value to error variable.

If error variable is set, You will see the error messages in browser.

You can use any one of these methods to get correct error messages.

2. Fetching Results with PDO

Here in this step, you will learn about fetching records from the database. You will learn about different methods available in PDO to fetch results from the database.

First of all, you will see about fetch method. After that fetch all method then fetch column method and getting the number of rows in result with PHP PDO. And also you will learn about PDO fetch styles to get the numeric array or associative array.

2.1 Fetching Results with Fetch Method

With fetch method in PDO, we can get single record result from the entire result set. To get multiple records with fetch method we should use a loop. While loop works with this fetch method.

Here you can see the example code to fetch the records with fetch method combined with while loop.

After printing the row you can see an array with numeric array and associative array. You can get an only numeric array or an associative array with PDO fetch styles, you will learn about these PDO fetch styles in the last step.

You can display the full name with associative array and the numeric array.

2.2 Fetching Results with FetchAll Method

FetchAll method gets all the results from the database based on the query. It is same as the fetch method but the difference is it returns an entire result set.

You can use the loop to go through each array. After printing the result, you will understand the difference between fetch and fetchAll methods.

2.3 Fetching Results with FetchColumn Method

The fetchColumn method is used to get only column based results. It only fetches one column result. If you are not passing any column number fetchColumn by default fetches the first column result in the database.

You should always pass numeric column number, not the column name. Here, I’m showing an example of fetchColumn with While loop to get all the email address from the table.

I’m displaying emails in the browser with echo PHP function.

2.4 Counting Number of Rows in PDO

To get the number of rows from the result set, you can use the rowCount method. This rowCount method works same like mysqli_num_rows PHP function.

Here is an example to get the number of rows from the result set.

3. Inserting & Deleting Records with PDO

Here in this step, you will learn about inserting and deleting records with PDO. You will also learn about getting last insert id with PDO.

Here I’m going to use two PDO methods that are query method and exec method to insert and delete queries and also you will learn about the difference between query method and exec method.

3.1 Inserting Records in PDO with Query Method

First of all, I’m inserting a new record with query method. And here I’m passing values directly in the SQL query. After executing query method, I’m using var_dump to print result.

Here is the code to insert a record in the database with PHP PDO.

After executing above code you will see the SQL query on the screen. To proceed to next steps we should know the query is successful or not. To get this kind of response I’ll use exec method instead of the query method.

3.2 Inserting Records in PDO with Exec Method

In the previous step, we are not getting the correct response to know whether the query is successful or not. For that, I’m using exec method.

With exec method, you can see the no of affected rows.

After loading the above code, you will see the number of affected records. With above code I’m inserting one record, you will see the output as 1.

3.3 Getting Last Insert ID after Query

After insert query to get the id of the record inserted, you can use the lastInsertID method.

Here is the code to get last insert id.

Use above code with insert query to display the last insert id.

3.4 Deleting Records with PDO

Here in this step, you will learn about delete query. Delete query is also same as select SQL query. Instead of the select query, you can use the delete SQL query.

With exec method, you will get no of affected rows.

After executing the above code, you will get the number of deleted rows.

3.5 When to use Query & Execute Method

The difference between query and execute method is query method can be used for select queries. And for non-select queries that is insert, update and delete to get the number of affected rows you can use execute method.

4. Intro to Prepared Statements : Binding Values

Prepared statements use placeholders for values that are coming from external sources such as an online form. You can bind values to placeholders using the bindParam or bindValue methods.

PDO has the option of using either named or anonymous parameters in prepared statements. We’ll begin by looking at named and anonymous parameters to bind values.

Alternatively, you can pass an array of values directly to the execute method. You will learn about them in next steps.

Using a prepared statement for a single query involves two round trips to the database server. The first time to validate and optimize the SQL, the second to send the values to the placeholders.

4.1 Different Methods of Binding Input Values

You can bind values to placeholders with these 2 methods bindParam or bindValue methods

the bindParam method only works with a variable, you can’t pass it an expression such as a calculation or a string concatenated to a variable.

This is because the value is evaluated Until the statement is executed. In other words, you’re binding the variable rather than it’s value.

bindValue binds the value immediately, so the value must be known. with the BindValue method we can set a field value to NULL.

4.2 Using Named Placeholders/Parameters

Here in this step, you will learn about using named placeholders. Here I’m using insert sql query, you can use it with the update and delete queries also.

First of all, I’m passing named placeholders in SQL query. That is :firstname, :lastname, :email, :gender, :age. After that, you should prepare the statement to take the values from external sources. Here I’m passing the values from URL.

Then binding the values with named placeholders. Here I’m using both methods bindValue and bindParam.

And the final step is execute method to execute the SQL query.

Here is the code for prepared statements with named placeholders.

After running the above code, you will get the output as boolean true or false. It’s more useful output than exec method.

4.3 Using Anonymous Placeholders/Parameters

Anonymous Placeholders is the opposite of named placeholders. Here we won’t use names, just we will use question marks as placeholders and numbers for assigning values to placeholders.

Everything is same as above code, for placeholders I’m using question marks.

After preparing the statement, binding values then execute.

Here also output will be same, that is boolean true or false.

4.4 Named Array Placeholders/Parameters

Named Array Placeholders as the name suggests, we will use named placeholders to prepare statement.

After that, we will bind values with the array, and this array will be passed to execute method.

Here is the example code for Named Array Placeholders.

Here also output will be same, that is boolean true or false.

4.5 Anonymous Array Placeholders/Parameters

In anonymous array placeholders we will use question marks as placeholders then we will prepare the statement. After that, we will pass the values as an array to execute method.

Here is the example code for anonymous array placeholders.

Here also output will be same, that is boolean true or false.

4.6 Binding Output Values

A prepared statement can also bind the output values of a result set to named variables using the bind column method. This avoids the need to use an associative array, simplifying embedding the results in HTML

We will use this bindColumn method after execute method. I’m using a simple select query with the while loop to loop through the results.

Here is an example code to bindColumn to named variables.

After executing the code, you will see the full names of the records with id and email.

5. Creating CRUD Application with PHP PDO

Now you know all the basics to create an application with PHP PDO. Here I’ve created a simple CRUD application with PHP PDO to show how you can use PDO in your applications.

Follow this step by step guide to develop a CRUD application with PHP PDO and make yourself comfortable with PHP PDO.

Complete Code of all the files

If you want to access all the practice files, you can get these files from here.

Conclusion

PHP PDO is more secure and prevents from SQL injections. You should start implementing PHP PDO in your applications if you are not already using it.

If you have any doubts let me know through the comment form below.

Vivek Vengala
 

Vivek Vengala is a Online Entrepreneur, Web Developer from Hyderabad India.

Click Here to Leave a Comment Below 0 comments

Leave a Reply: