Using a Persistent Database Object Connection from Other Objects in PHP

[SamA74]

@OhMonty

I haven’t been able to find out what prepared statements actually do before substituting data with placeholders in a statement that prevents SQL-injection,

In post #27 I explained how it prevents injection by defining a clean query that cannot be altered or appended prior to execution.

@OhMonty

also read somewhere else on the PHP site that you still need to validate and sanitize your data before passing it on to a prepared statement anyway

I think “need to” should be “are advised to”. In my earlier post I used the phrase “belt & braces” when advising to do both, because you could just have one or the other and there is a good chance you won’t be caught with your pants down.
But with both, in the event that one or the other fails you, you have back up, a fail-safe or layers of security.
Also note that when you use prepared statements the data is escaped without you having to use an escape function, so very secure and you also have the opportunity to type cast data when binding parameters. So that may make the validation/sanitisation seem a bit redundant, but then, why wouldn’t you anyway?

[OhMonty]

@Lemon_Juice

The downside of prepared statements is that it’s more difficult to debug queries.

Yes, I am discovering this myself. I’m a bit surprised that the PDO or MySQLi classes don’t provide a way of getting the query that was actually run, like a PDO::get_last_query() or something like that. It would be incredibly helpful for debugging.

@Lemon_Juice

All in all, forget all the supposed performance benefits of prepared statements that people tend to mention. It is true that the server can cache the query plan to some extent but the benefit in today’s databases is extremely minimal

Do you mean that the benefits of prepared statements are extremely minimal in today’s databases? Or the cacheing?

@Lemon_Juice

BTW, in PDO you can use emulated prepared statements and then there are no additional round trips to the database server because the preparing is done at the PDO level

I’ve seen many people say to keep this option turned off in PDO because it’s buggy or for other reasons. Have you experienced any problems using emulated prepared statements?

Thanks for your helpful input!

[Lemon_Juice]

@OhMonty

Do you mean that the benefits of prepared statements are extremely minimal in today’s databases? Or the cacheing?

I mean the performance benefits are minimal and they can be observed only in very rare edge cases.

@OhMonty

I’ve seen many people say to keep this option turned off in PDO because it’s buggy or for other reasons. Have you experienced any problems using emulated prepared statements?

Ironically, I don’t know… because most of the time I don’t even know if they are turned on and I leave the defaults as they are. As far as I know, they are on for MySQL but I don’t know about Postgres. I’ll have to check it.

[ahundiak]

@Lemon_Juice

To be precise - now most of the time I use Doctrine DBAL as the database access class, which brings a few convenience methods and allows to use all the power of PDO as well.

@OhMonty - You should strongly consider installing the Doctrine DBAL. http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/index.html

It is a thin wrapper over PDO and among it’s many helper functions, it can log sql queries for you. I would argue that the Doctrine DBAL is the main reason to use PDO.

[SpacePhoenix]

@OhMonty

Yes, I am discovering this myself. I’m a bit surprised that the PDO or MySQLi classes don’t provide a way of getting the query that was actually run, like a PDO::get_last_query() or something like that. It would be incredibly helpful for debugging.

PDO has debugdumpparams() which will show the last prepared statement, but it doesn’t show the values that were bound but you could just echo the bound values for debugging on a local server

http://php.net/manual/en/pdostatement.debugdumpparams.php

[OhMonty]

Thanks! The debugDumpParams() function doesn’t show how the query may look after being merged with parameters. But I did find this useful function on Stack Overflow that does the combining to give you an idea of what the finished prepared query will look like, which is very helpful for debugging.

Getting raw SQL query string from PDO prepared statements

/**
 * Replaces any parameter placeholders in a query with the value of that
 * parameter. Useful for debugging. Assumes anonymous parameters from 
 * $params are are in the same order as specified in $query
 *
 * @param string $query The sql query with parameter placeholders
 * @param array $params The array of substitution parameters
 * @return string The interpolated query
 */
public function interpolateQuery($query, $params) {
    $keys = array();
    $values = $params;

    # build a regular expression for each parameter
    foreach ($params as $key => $value) {
        if (is_string($key)) {
            $keys[] = '/:'.$key.'/';
        } else {
            $keys[] = '/[?]/';
        }

        if (is_array($value))
            $values[$key] = implode(',', $value);

        if (is_null($value))
            $values[$key] = 'NULL';
    }
    // Walk the array to see if we can add single-quotes to strings
    array_walk($values, create_function('&$v, $k', 'if (!is_numeric($v) && $v!="NULL") $v = "\'".$v."\'";'));

    $query = preg_replace($keys, $values, $query, 1, $count);

    return $query;
}

I added this to my Database class and use it for logging the query combined with the parameters. Makes it a lot easier to debug and test out the query in the database.

[Lemon_Juice]

@OhMonty

I’ve seen many people say to keep this option turned off in PDO because it’s buggy or for other reasons. Have you experienced any problems using emulated prepared statements?

I’ve just tested it and PDO for Postgres has real prepared statements turned on by default so that’s what I’ve been using most of the time recently. The default for MySQL is quite the opposite and I haven’t come across any problems there either, so I don’t know what is buggy about PDO emulated prepares…