How to Connect PHP and PostgreSQL

Connecting PHP and PostgreSQL is an efficient way to improve and expand the functionality of your PHP infrastructure. Working together, these two open source technologies make it quick and easy to access data sources, delivering excellent experiences for end users, developers, and other stakeholders.

In this blog, I give a broad overview about PostgreSQL and how it relates to PHP. I then provide a step-by-step guide for connecting PostgreSQL and PHP, including advice on how to avoid SQL injection. Finally, I provide an example by explaining how to configure ZendPHP and ZendHQ with a PostgreSQL database.

PHP and PostgreSQL: Overview

Learning how to connect PHP and PostgreSQL can be completed in a few easy steps, but you must first understand the basics. For instance, PostgreSQL works nearly the same with PHP as the standard MySQL package does. The primary difference will be using a different PHP extension to interact with your Postgres database.

What Is PostgreSQL?

PostgreSQL, or Postgres, is an open source relational database system akin to all other SQL variants.

Like the common MySQL, Postgres works in a very similar vein. However, there are a few notable differences. For instance, Postgres is an object relational database, which means it can store objects with properties, unlike some other SQL types. Additionally, PostgreSQL supports a wider array of index types than the standard MySQL engines do.

Can I Use PHP With PostgreSQL?

Yes, you can use PHP with PostgreSQL. PHP natively supports the Postgres database engine.

There are a suite of built-in PHP functions to interact with a Postgres database, and the more frequently used PDO package also supports PostgreSQL. All common PHP frameworks also offer support for PostgreSQL, making it an easy and well-established process to use PHP and Postgres together.

Why Connect Postgres and PHP?

Depending on your project, the choice of database engine might be yours, or it may be decided upon by other parties. Postgres is always a strong choice to use amongst the types of SQL. If you are familiar with using PHP and SQL engines, adapting to PHP and Postgres should be frictionless. If you are new to PHP, using PostgreSQL is rather intuitive and easy to learn.

How to Connect PHP With PostgreSQL

Connecting PHP with PostgreSQL can be completed by following a few simple steps, listed below. To follow along with this guide, you will need to establish a Postgres database (including a server, database, and an account with a username and password).

Step One: Enable the PDO Extension

After setting up your Postgres database, ensure you have the PDO extension for PHP. Looking in your php.ini file, check that the PDO Postgres driver is enabled. Add or uncomment the following line: ;extension=php_pdo_pgsql.dll

Step Two: Establish a Connection With the Postgres Database

The next step is to establish a connection with your Postgres database. Like a standard MySQL connection, your connection information will be about the same, with the only change being that you specify a different database type. An example PDO connection would be:

$dbInfo = sprintf("pgsql:host=%s;port=%d;dbname=%s;user=%s;password=%s",
                $params['host'],
                $params['port'],
               $params['database'],
                $params['user'],
               $params['password']);
 $pdo = new \PDO($ dbInfo);
 $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

Step Three: Test the Connection

The last step will be to test your connection to ensure it is responding properly. In a normal scenario, you would've included exception handling in the PDO object creation, which would catch most issues. Using the Postgres PDO connection will work exactly like PDO with MySQL. An example select would be:

$stmt = $this->pdo->prepare('SELECT id, color, name                                                                  FROM pets WHERE id = :id');
// bind value to the :id parameter
$stmt->bindValue(':id', $id);
// execute the statement
$stmt->execute();
// return the result set as an object to interact with
return $stmt->fetchObject();

Security Considerations: Postgres SQL Injection

As with any aspect of web application design or PHP implementation, security must be made a top priority. One way to keep your PHP and Postgres application secure and protected is to work against the possibility of SQL injection.

What Is SQL Injection?

A SQL injection is a widely known but devastating security breach. It involves altering the data used in query.

Consider the following SQL INSERT statement:

select * from dbtable where customer = $name;

Like many forms or searches, the variable might come from user input. In this case, $name will be coming from a web-form. Without proper validation of the data, a user could input the following for name:

Billy;truncate sales;

This would result in the following SQL statements:

select * from dbtable where customer = Billy; truncate sales;

Now, when this is executed, in addition to the select statement being executed, the records from the sales table would be deleted – an obvious attack that we want to prevent. How can we accomplish this?

How to Avoid SQL Injection in Postgres

One standard way to prevent SQL injection with PHP and Postgres is with prepared statements through tools like PDO. With a prepared statement, instead of sending a query with raw information to the database, we first tell the database the structure of the query that will be submitted.

However, proper handling of data can only go so far towards preventing SQL injection. Any front-end validation can be defeated by the nature of JavaScript, as it runs on the clients' computer, and they have the option to alter any rules in the form. As a result, strong back-end action is also required. In this case, we will be showing how to prepare your queries. 

Tools like PDO can protect your database from any bad data that does make its way through and add protection from unwanted actions. Thus, with our above example of a truncate table command being sent in, prepared statements would prevent this attack from succeeding.

It should be noted that all data should be treated with equal suspicion, regardless of source. Preparing statements isn't only for data directly from a user, as proper security involves always assuming data may be suspect. Therefore, it is good practice to use prepared statements for any database access, regardless of the data source.

An example of preparing a statement with PDO might be as follows:

$stmt= $db->prepare("INSERT into dbtable (name) value (:name)");
      $stmt->bindValue(‘name’, “Billy”);
$result = $stmt->execute();

In the above example, the name we received from the web form has been safely handled by PDO to ensure any harmful part of the string will not result in unwanted side effects.

Postgres and PHP Example: ZendHQ Database Configuration

ZendHQ, the must-have extension for ZendPHP runtimes, offers unparalleled observability and orchestration tooling to mission-critical PHP applications. Traditionally, ZendHQ has used SQLite as the default database engine. However, as a part of an expansion of database options, users can now use PostgreSQL (along with MariaDB) to establish a primary server with one or more replicas, making it easy to regain or migrate ZendHQ data in the event of a corrupted database, crash, or other disaster.

Configuring ZendHQ to use Postgres is not difficult, and can be completed using the steps below. Visit our full ZendHQ documentation for further details.

Optimize and Scale Modern PHP Apps

ZendHQ makes it easy for your team to monitor, inspect, optimize, automate, secure, and scale mission-critical PHP applications.

Discover ZendHQ  Talk to an Expert

Step One: Locate the ZendHQ Configuration File

The ZendHQ DB engines are configured in separate files that are included in the main ZendHQ configuration file using the include directive. For Postgres, the config file will default to:

${INSTALL_PREFIX}/etc/zendhqd_psql.ini

In this instance, ${INSTALL_PREFIX} is the directory where ZendHQ is installed, much like /opt/zend/zendphp on most of the Linux systems. 

Step Two: Change DB Engine

To change your DB engine, open the following config file:

${INSTALL_PREFIX}/etc/zendhqd.ini

You will want to comment out the current DB engine, and uncomment the line for Postgres. For example:

# The default SQLite database engine
                  ;include = zendhqd_sqlite.ini
                  # Postgres database engine
                  include = zendhqd_ psql.ini     

Step Three: Open the zendhqd_psgl.ini File

Next, you will need to open the zendhqd_psgl.ini file and add the valid connection configuration information, such as host, port number, database name, and such. 

Step Four: Test the New DB Connection

Next we will want to test our new DB connection. You will use the zendhqctl database test command to verify the database configuration before starting the ZendHQ daemon:

zendhqctl database test

Note that this will attempt to create a test table in the database by the name of "zendhqctl_test". If one already exists, it will be dropped, and all data will be lost.

Step Five: Run the ZendHQ Daemon to Create Tables

Lastly, you will want to run the ZendHQ daemon to create tables in the database. From the command line, it is possible to use the --init-only command-line argument to create tables and exit (replace ${INSTALL_PREFIX} with the actual directory name where ZendHQ is installed):

zendhqd -c ${INSTALL_PREFIX}/etc/zendhqd.ini --init-only

Final Thoughts

Using Postgres and PHP is a powerful and secure combination. Like other variants of SQL databases, PostgreSQL and PHP work very well together. Postgres in particular has a long history of support amongst the PHP community. 

When paired with ZendPHP runtimes and the ZendHQ extension, PostgreSQL can supply many exciting additional options to the default ZendPHP engine. Leveraging these features can greatly improve the efficiency and speed of your database.

Try ZendPHP + ZendHQ Free for 30 Days

ZendPHP, when combined with the ZendHQ extension, delivers the building blocks for modern, scalable, and secure web applications. Ready to see how they fit in your infrastructure?

Free Trial Details  Discover ZendPHP + ZendHQ

Additional Resources

• Announcements - What's New in ZendPHP + ZendHQ
• Case Study - AgVantage Software Modernizes Front-End System With ZendPHP on IBM i
• White Paper - Planning Your Next PHP Migration
• Blog - How to Connect PHP to MySQL Database
• Blog - PHP Debugging: Go Beyond Step Debuggers With ZendHQ
• Blog - How to Migrate Zend Server to ZendPHP