PDO Unbuffered queries

Question 1

I'm trying to get into PDO details. So I coded this:

$cn = getConnection();

// get table sequence
$comando = "call p_generate_seq('bitacora')";
$id = getValue($cn, $comando);

//$comando = 'INSERT INTO dsa_bitacora (id, estado, fch_creacion) VALUES (?, ?, ?)';
$comando = 'INSERT INTO dsa_bitacora (id, estado, fch_creacion) VALUES (:id, :estado, :fch_creacion)';
$parametros = array (
    ':id'=> (int)$id,
    ':estado'=>1,
    ':fch_creacion'=>date('Y-m-d H:i:s')
);
execWithParameters($cn, $comando, $parametros);

my getValue function works fine, and I get the next sequence for the table. But when I get into execWithParameters, i get this exception:

PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. in D:Servidorxampp_1_7_1htdocsbitacorafunc_db.php on line 77

I tried to modify the connection attributes but it doesn't work.

These are my core db functions:

function getConnection() {
    try {
        $cn = new PDO("mysql:host=$host;dbname=$bd", $usuario, $clave, array(
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            ));

        $cn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
        return $cn;
    } catch (PDOException $e) {
        print "Error!: " . $e->getMessage() . "<br/>";
        die();
    }
}
function getValue($cn, $comando) {
    $resul = $cn->query($comando);
        if (!$resul) return null;
        while($res = $resul->fetch()) {
            $retorno = $res[0][0];
            break;
        }
        return $retorno;
}
function execWithParameters($cn, $comando, $parametros) {
    $q = $cn->prepare($comando);
    $q->execute($parametros);
    if ($q->errorInfo() != null) {
        $e = $q->errorInfo();
        echo $e[0].':'.$e[1].':'.$e[2];
    }
}

Somebody who can shed a light for this? PD. Please do not suggest doing autonumeric id, cause i am porting from another system.

Question 2

The issue is that mysql only allows for one outstanding cursor at a given time. By using the fetch() method and not consuming all the pending data, you are leaving a cursor open.

The recommended approach is to consume all the data using the fetchAll() method. An alternative is to use the closeCursor() method.

If you change this function, I think you will be happier:

<?php
function getValue($cn, $comando) {
    $resul = $cn->query($comando);
    if (!$resul) return null;
    foreach ($resul->fetchAll() as $res) {
            $retorno = $res[0];
            break;
    }
    return $retorno;
}
?>

Question 3

PDO does not escape the variables. The variables and the SQL command are transferred independently over the MySQL connection. And the SQL tokenizer (parser) never looks at the values. Values are just copied verbatim into the database storage without the possibility of ever causing any harm. That's why there is no need to marshall the data with prepared statements.

Note that this is mostly a speed advantage. With mysql_real_escape_string() you first marshall your variables in PHP, then send an inefficient SQL command to the server, which has to costly segregate the actual SQL command from the values again. That's why it's often said that the security advantage is only implicit, not the primary reason for using PDO.

If you concat the SQL command and don't actually use prepared statments (not good!), then yes, there still is an escape function for PDO: $pdo->quote($string)

Question 4

you need to fetch until a row fetch attempt fails. I know you may only have one row in the result set and think one fetch is enough, but its not.

you probably have other statements where you didn't fully "fetch until a fetch failed". Yes, i see that you fetch until the fetch failed for one of the statements.

also, see closecursor()

$sql = "select * from test.a limit 1";
$stmt = $dbh->prepare($sql);
$stmt->execute(array());
$row = $stmt->fetch();
$stmt->closeCursor();

or

$sql = "select * from test.a limit 1";
$stmt = $dbh->prepare($sql);
$stmt->execute(array());
list($row) = $stmt->fetchAll(); //tricky

Question 5

Oddly enough, the PHP packages provided by Ubuntu are not compiled with the Mysql native driver, but with the old libmysqlclient instead (tested on Ubuntu 13.10 with default packages):

<?php
echo $dbh->getAttribute(PDO::ATTR_CLIENT_VERSION); // prints "5.5.35", i.e MySQL version
// prints "mysqlnd (...)" when using mysqlnd

Your very test case ("Edit 4", with setAttribute(MYSQL_ATTR_USE_BUFFERED_QUERY, true)) works as expected with PHP 5.5.3 manually compiled with mysqlnd with:

./configure --with-pdo-mysql=mysqlnd # default driver since PHP v5.4

... but fails with:

bash> ./configure --with-pdo-mysql=/usr/bin/mysql_config

It quite odd that it fails only if the first statement is executed twice; this must be a bug in the libmysqlclient driver.

Both drivers fail as expected when MYSQL_ATTR_USE_BUFFERED_QUERY is false. Your Common Sense already demonstrated why this is expected behaviour, regardless of the number of rows in the result set.

Mike found out that the current workaround is installing the php5-mysqlnd package instead of the Canonical-recommended php5-mysql.

Question 6

Well, at second glance your question looks more complex to be answered with just one link

How does php pdo's prepared statements prevent sql injection?

How can prepared statements protect from SQL injection attacks?

What are other pros/cons of using PDO?

Most interesting question.
A greatest PDO disadvantage is: it is peddled and propagated a silver bullet, another idol to worship.
While without understanding it will do no good at all, like any other tool.
PDO has some key features like

Database abstraction. It's a myth, as it doesn't alter the SQL syntax itself. And you simply can't use mysql autoincremented ids with Postgre. Not to mention the fact that switching database drivers is not among frequent developer's decisions.
Placeholders support, implementing native prepared statements or emulating them. Good approach but very limited one. There are lack of necessary placeholder types, like identifier or SET placeholder.
a helper method to get all the records into array without writing a loop. Only one. When you need at least 4 to make your work sensible and less boring.

Does using PDO reduce efficiency?

Again, it is not PDO, but prepared statements that reduces efficiency. It depends on the network latency between the db server and your application but you may count it negligible for the most real world cases.

PDO Unbuffered queries

Answers

php,pdo