Pdo Connection Test

PDO Connection Test

you need to set the error mode when connection to the database:

try{
    $dbh = new pdo( 'mysql:host=127.0.0.1:3308;dbname=axpdb',
                    'admin',
                    '1234',
                    array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
    die(json_encode(array('outcome' => true)));
}
catch(PDOException $ex){
    die(json_encode(array('outcome' => false, 'message' => 'Unable to connect')));
}

for more infos see the following links:

Using MySQL with PDO

Errors and error handling

How can I check PDO connection?

It depends on what you set for PDO::ATTR_ERRMODE. In your case, it's set as PDO::ERRMODE_EXCEPTION, so an error will throw an Exception.

$dbh_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

This error mode however is not applied when you connect (i.e., when __constructor() is called in PDO), since a PDOException is always thrown:

PDO::__construct() will always throw a PDOException if the connection fails regardless of which PDO::ATTR_ERRMODE is currently set. Uncaught Exceptions are fatal.

You can read about the different modes here.

PDO test if connected

In a single click from this question, in the PDO tag wiki lies the exact how-to:

$dsn = "mysql:host=localhost;dbname=test;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$pdo = new PDO($dsn,'root','', $opt);

As well as a warning

DO NOT use try..catch operator just to handle an error message.

Uncaught exception already excellent for this purpose, as it will treat PDO errors just the same way as other PHP errors - so, you can define the behavior using site-wide settings.
A custom exception handler could be added later, but not required. Especially for new users, it is recommended to use unhandled exceptions, as they are extremely informative, helpful and secure.
More info...

Global pdo connection not reachable in a PHPUnit Test

Ok, after many months I understood that how PHPunit works. It won't read that include_once for the database if it's outside of the method. I moved it inside of the function and also I changed the phpUnit environment for a remote one, on the same server of the database, so the connection is allowed.

After those two changes my test is working:
https://www.screencast.com/t/O2ccmcxt2

PDO: How to check if connection is active, for real?

The MySQL protocol supports a special command COM_PING for this purpose, and the C API has a call mysql_ping() to send it. This tests if the connection is active.

If the connection was created with MYSQL_OPT_RECONNECT, automatically connects (https://dev.mysql.com/doc/refman/5.6/en/auto-reconnect.html).

Unfortunately, neither of these features are supported if you use the current version of PDO. You can only submit SQL query strings, not special commands. And PDO now uses the mysqlnd driver, which has its advantages but doesn't support the reconnecting option. So the issue is moot anyway.

I don't know of any more elegant solution than trying to issue a "dummy" query like SELECT 1, catch the exception, and if you get error code 2006 (server has gone away), then reconnect.

You can create a singleton class to hold your db connection, and test for a live connection every time the application code calls getConnection(). Here's an example I tested:

class DB
{
    protected static $pdo = null;

    public static function getConnection() {
        // initialize $pdo on first call
        if (self::$pdo == null) {
            self::init();
        }

        // now we should have a $pdo, whether it was initialized on this call or a previous one
        // but it could have experienced a disconnection
        try {
            echo "Testing connection...\n";
            $old_errlevel = error_reporting(0);
            self::$pdo->query("SELECT 1");
        } catch (PDOException $e) {
            echo "Connection failed, reinitializing...\n";
            self::init();
        }
        error_reporting($old_errlevel);

        return self::$pdo;
    }

    protected static function init() {
        try {
            echo "Opening new connection...\n";
            self::$pdo = new PDO('mysql:host=huey;dbname=test', 'root', 'root');
            self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            die($e->getMessage());
        }
    }
}

Use it like this:

echo "Query for 2:\n";
$pdo = DB::getConnection();
echo $pdo->query("SELECT 2")->fetchColumn() . "\n";

echo "\nSleeping 10 seconds...\n";
sleep(10); /* meanwhile I use another window to KILL the connection */
echo "\n";

echo "Query for 3:\n";
$pdo = DB::getConnection();
echo $pdo->query("SELECT 3")->fetchColumn() . "\n";

Output:

Query for 2:
Opening new connection...
Testing connection...
2

Sleeping 10 seconds...

Query for 3:
Testing connection...
Connection failed, reinitializing...
Opening new connection...
3

You can see that it detects that the connection failed, and reinitializes.

I need a confirmation of a successful PDO Connection

If there are any connection errors, a PDOException object will be thrown.

try {
    $pdo = new PDO($dsn, $dbu, $dbp, $opt);
} catch (PDOException $e) {
    // bad connection
}

To verify that data such as the host name, the database name and the login credentials are correct, you must force the user to not leave those data empty. In fact, it is entirely possible to establish a connection with just a host, that's because mysql allows the creation of accounts with blank username (empty string) that matches any username.

Also, in order to avoid parameter injection (example, a database name called foo;port=123) in the dsn string, you should at least check the presence of a semicolon or a NULL byte in the connection options (as host e database name).

Example

/**
 * Return a PDO object if the connection options are correct, and if the connection is established. False otherwhise.
 */
function test_mysql_connection($host, $dbname, $username, $password = null) {
    if (empty($host) || empty($dbname) || empty($username)) {
        // those parameters MUST NOT be empty
        throw new InvalidArgumentException('Host, database name and username are required.')
    }

    try {
        $pdo = new PDO(build_mysql_dsn_safely($host, $dbname), $username, $password);
        return $pdo;
    } catch (PDOException $e) {
        // bad connection
        return false;
    }
}

/**
 * See how is it parsed here: https://github.com/php/php-src/blob/71c19800258ee3a9548af9a5e64ab0a62d1b1d8e/ext/pdo/pdo.c#L207
 */
function build_mysql_dsn_safely($host, $dbname = null, $charset = null, $port = null)
{
    static $bad_chars = array(';', '=', "\0");

    $vars = array_filter(array(
        'host' => $host,
        'dbname' => $dbname,
        'charset' => $charset,
        'port' => $port,
    ));

    foreach($vars as $param => $data) {
        foreach ($bad_chars as $bad_char) {
            if (strpos($data, $bad_char) !== false) {
                throw new InvalidArgumentException(sprintf(
                    'Connection options "%s" contains an invalid value.', $param
                ));
            }
        }
    }

    return 'mysql:'.implode(';', array_map(function($optkey, $optval) {
        return $optkey.'='.$optval;
    }, array_keys($vars), $vars));
}

if($pdo = test_mysql_connection($_POST['dbh'], $_POST['dbu'], $_POST['dbn'], $_POST['dbp'])) {
    // connection established
}

Testing coverage with PHPUnit and PDO

This is exactly what PHPUnit's test doubles are for. And kudos for designing your classes in an object oriented way, so it's easy to inject mocks into them.

<?php

use PHPUnit\Framework\TestCase;

class DBAccessTest extends TestCase
{
    public function test_something_goes_wrong()
    {
        // Create test doubles.
        $stmtMock = $this->createMock(\PDOStatement::class);
        $pdoMock = $this->createMock(\PDO::class);

        // Configure the stubs.
        $stmtMock->method('execute')
                ->willReturn(false);
        $pdoMock->method('prepare')
                ->willReturn($stmtMock);

        // Inject the mock.
        $test = new DBAccess($pdoMock);

        // Assert.
        $this->assertFalse($test->select());
    }
}

---

Related Topics