Get Query Back from Pdo Prepared Statement

Get query back from PDO prepared statement

Try $statement->queryString.

Getting raw SQL query string from PDO prepared statements

I assume you mean that you want the final SQL query, with parameter values interpolated into it. I understand that this would be useful for debugging, but it is not the way prepared statements work. Parameters are not combined with a prepared statement on the client-side, so PDO should never have access to the query string combined with its parameters.

The SQL statement is sent to the database server when you do prepare(), and the parameters are sent separately when you do execute(). MySQL's general query log does show the final SQL with values interpolated after you execute(). Below is an excerpt from my general query log. I ran the queries from the mysql CLI, not from PDO, but the principle is the same.

081016 16:51:28 2 Query       prepare s1 from 'select * from foo where i = ?'
                2 Prepare     [2] select * from foo where i = ?
081016 16:51:39 2 Query       set @a =1
081016 16:51:47 2 Query       execute s1 using @a
                2 Execute     [2] select * from foo where i = 1

You can also get what you want if you set the PDO attribute PDO::ATTR_EMULATE_PREPARES. In this mode, PDO interpolate parameters into the SQL query and sends the whole query when you execute(). This is not a true prepared query. You will circumvent the benefits of prepared queries by interpolating variables into the SQL string before execute().

---

Re comment from @afilina:

No, the textual SQL query is not combined with the parameters during execution. So there's nothing for PDO to show you.

Internally, if you use PDO::ATTR_EMULATE_PREPARES, PDO makes a copy of the SQL query and interpolates parameter values into it before doing the prepare and execute. But PDO does not expose this modified SQL query.

The PDOStatement object has a property $queryString, but this is set only in the constructor for the PDOStatement, and it's not updated when the query is rewritten with parameters.

It would be a reasonable feature request for PDO to ask them to expose the rewritten query. But even that wouldn't give you the "complete" query unless you use PDO::ATTR_EMULATE_PREPARES.

This is why I show the workaround above of using the MySQL server's general query log, because in this case even a prepared query with parameter placeholders is rewritten on the server, with parameter values backfilled into the query string. But this is only done during logging, not during query execution.

In PHP with PDO, how to check the final SQL parametrized query?

So I think I'll finally answer my own question in order to have a full solution for the record. But have to thank Ben James and Kailash Badu which provided the clues for this.

Short Answer

As mentioned by Ben James: NO.

The full SQL query does not exist on the PHP side, because the query-with-tokens and the parameters are sent separately to the database.
Only on the database side the full query exists.

Even trying to create a function to replace tokens on the PHP side would not guarantee the replacement process is the same as the SQL one (tricky stuff like token-type, bindValue vs bindParam, ...)

Workaround

This is where I elaborate on Kailash Badu's answer.
By logging all SQL queries, we can see what is really run on the server.
With mySQL, this can be done by updating the my.cnf (or my.ini in my case with Wamp server), and adding a line like:

log=[REPLACE_BY_PATH]/[REPLACE_BY_FILE_NAME]

Just do not run this in production!!!

PDO Prepared Statements - Viewing Query Results

fetchall docs

Returns multiple rows back in an array: array(row_1, row_2).

The rows them self are array too.

So you try to access a row that does not exists. So you should use fetch or a foreach

Fetch:

$results = $stmt->fetch(PDO::FETCH_ASSOC);

Foreach:

foreach($results as $result){ 
  echo $result['field_1'];
}

How to fetch results from a PDO prepare statement

Your result variable (besides being overwritten) is not what you think it is. It's a PDO statement.

Try this instead:

$stmt = $this->con->prepare("select * from ".$table_name." where username = :user_email");

$stmt->execute([
    ':user_email' => $user_email,
]);

if (false !== ($row = $stmt->fetchColumn()))
{
    return $row;
}

However, this will only return the first column of the first row. Instead you probably want:

 return $stmt->fetchAll(PDO::FETCH_ASSOC);

I changed $result to $stmt, because it's not a result. It's a Statement Object.

Original issues

In your original code (see below) you are overwriting it with the return from execute which is a Boolean.

// Old code (don't use this)
$result = $result->execute([
    ':user_email' => $user_email,
]);

//$result = TRUE|FALSE

if ($result->fetchColumn() !== false)
{
    return $result;
}

And then you try to call a method of the Boolean, which, well, won't work. But the problems are deeper than just that. Let’s assume you don’t overwrite it.

// Old code (don't use this)
$result->execute([
    ':user_email' => $user_email,
]);

//$result = PDOStatment object.

if ($result->fetchColumn() !== false)
{
    return $result;
}

Now the result is still your PDOStatement, which is good, but as I said, you're not saving the fetched data. This time you return the PDOStatement object. Which is not what you want.

Then further, as I stated earlier, if you do save it and return it, it's still probably not what you are after. Because fetchColumn() accesses only one row at a time and only one column.

But I have no way to know what you want. Maybe that is what you want? In that case your query is less than ideal. Maybe you just want to see if a user exists with a given email? In that case I would use this query.

$result = $this->con->prepare("SELECT id FROM ".$table_name." WHERE username = :user_email");

$result->execute([
    ':user_email' => $user_email,
]);
// There isn't any need to check it (see below)
return $result->fetchColumn();

PDOStatement::fetchColumn() returns a single column from the next row of a result set or FALSE if there are no more rows.

I can also tell by your stuff, that your database setup is probably wrong. That is, if you really need a dynamic table $table. The reason I can say this is you should not be duplicating any user data (or any data really, this is called normalization), and having the table dynamic implies that the email may exist separately in two (or more) tables.

If that is not the case then don't make it dynamic. Why is this an issue? Well, think what happens if a user changes their "email" now, because it exists in two tables (potentially). You'll have to update it in both places. But it's worse than that as it overcomplicates anything you do with the emails.

Without seeing the schema for your tables, I can only speculate on that, and how to fix it. But generally you would use a foreign key and associate the user record to that. Then using a JOIN you can access the email without duplication.

That said, there are a few cases where this may be acceptable, but I have no way to know if that is true in your case. Just a quick example would be a separate table for users and administrators (basically two-user systems).

Security

The last thing is be very very careful with this:

"select * from ".$table_name." where username = :user_email"

The issue here is it's open to SQL injection. Anytime you concatenate a variable into SQL, you open the door for injection attacks. Well, you may say I'm passing in a canned string account. Which is OK, but there is no validation at the point of failure. So maybe in five months you reuse this code and forget that you never validated the table name. Maybe not, but the fact remains that if user data could get into that argument, you have no protection whatsoever against injection on the table name. The possibility for it is there.

Something as simple as this:

  public function verify_user($table_name,$user_email){
     $allowed = ['account','users'];
     if(!in_array($table_name, $allowed )) throw new Exception('Invalid table name');
 }

See now it's virtually impossible to inject something into the table name. Further because it's in the same method (at the point of failure), you will never lose that protection. It's very easy to be in a rush latter and copy a piece of code change a few things and .... well you know.

Just my two cents.

UPDATE

So even if the chance is small that user input could get into $table you can not guarantee it 100%, because from within verify_user you have no way to know where the data came from, but you are trusting on faith that it's not user input. When it comes to SQL injection, you can't say well this is OK, because I will only call this method a certain way. It has to be 100% injection proof or as close as is humanly possible.

Why is this important, you ask? Imagine this.

   $userprofileobj->verify_user('account --',$_SESSION['user_email']);

Those two little --s are like // in PHP, but for SQL, they comment out the rest of the line in SQL so your query becomes this.

"select * from account -- where username = :user_email"

Or (essentially)

"select * from account"

So we just modified what your query does. Now thankfully it's not really possible to run two queries at once in PDO. You can do it (with some work) in MySqli. But because of security reasons, they have mostly done away with this ability. The reason is this (or worse like creating database users).

  $userprofileobj->verify_user('account; DROP TABLE account --',$_SESSION['user_email']);

Which, if you could do two queries, would do this:

 SELECT * FROM account
 DROP TABLE account

In any case this is dangerous stuff and is to be avoided at all costs. Being too lazy (and I am a lazy programmer, so don't take that wrong) to put a table name in is not an answer you want to give after your database has been compromised and you have exposed user data to a third party. It's just not an option.

All this does:

if(!in_array($table_name, ['table1', 'table2', ...])) throw new Exception('Invalid table name');

Is throw an error if "needle" $table_name is not in "haystack" - a canned list of table names. So if I do this (using our example above):

if(!in_array('account --', ['table1', 'table2', ...])) throw new Exception('Invalid table name');

It won't find account -- in our list of table1 and table2 and will blow up, thus preventing the injection attack.

PHP/PDO: use simple prepared statement with query return/affected rows?

It should be the same as any other statement:

$stmt = $dbh->prepare("DELETE FROM fruit WHERE colour = ?");
$stmt->execute(array('red'));
$count = $stmt->rowCount();

The PDO Statement rowCount() should be what you are looking to do.

EDIT

Fixed by adding the ->rowCount() which will return the row count. ->execute in a statement will return a bool, true or false whether the query errored out or not. Of course all of this information is readily available at the PDO Statement Manual

Retrieve (or simulate) full query from PDO prepared statement

I believe this is mentioned in the original question that was reference in this one. However
there is actually supposed to be a method for retrieving this data.

PDOStatement::debugDumpParams

However it isn't currently working as documented. There is a bug report and patch submitted for it here http://bugs.php.net/bug.php?id=52384 in case anyone is interested in voting on it. Until it's fixed it seems like you are left to use query logging or setting a custom statement class using the PDO::ATTR_STATEMENT_CLASS attribute.

PDO Prepared Statement not returning results

Here's a rewrite of your code. I use arrays to build up the nested AND and OR conditions, using implode to combine them, rather than sequences of string concatenation. I give each placeholder a different name, using the array index. I changed all your BETWEEN clauses to = because BETWEEN requires two parameters, a beginning and end of the range.

if (isset($_POST['platform'], $_POST['bandwidth'], $_POST['price'])){

    $platform = $_POST['platform'];
    $bandwidth = $_POST['bandwidth'];
    $price = $_POST['price'];
    $query = "SELECT * FROM hosts";

    $ands = array();
    $bind_array = array();

    $ors = array();
    foreach($platform as $i => $platform) {
        $ors[] = "platform LIKE CONCAT('%', :platform{$i}, '%')";
        $bind_array[":platform{$i}"] = $platform;
    }   
    if ($ors) {
        $ands[] = "(" . implode(" OR ", $ors) . ")";
    }

    $ors = array();
    foreach($bandwidth as $i => $bandwidth) {
        $ors[] = "bandwidth = :bandwidth{$i}";
        $bind_array[":bandwidth{$i}"] = $bandwidth;
    }   
    if ($ors) {
        $ands[] = "(" . implode(" OR ", $ors) . ")";
    }

    $ors = array();
    foreach($price as $i => $price) {
        $ors[] = "price = :price{$i}";
        $bind_array[":price{$i}"] = $price;
    }

    if ($ors) {
        $ands[] = "(" . implode(" OR ", $ors) . ")";
    }
    if ($ands) {
        $query .= " WHERE " . implode(" AND ", $ands);
    }

    $conn =  new PDO('mysql:host=127.0.0.1; dbname=test', 'root', '');
    $stmt = $conn->prepare($query);

    $stmt->execute($bind_array);

    foreach ($stmt as $row) {
        print_r($row); 
    }
}

UPDATE:

If you have two parameters price1[] and price2[] for a range, change the price loop to:

    foreach($price1 as $i => $price) {
        $ors[] = "price BETWEEN :pricelow{$i} AND :pricehigh{$i}";
        $bind_array[":pricelow{$i}"] = $price;
        $bind_array[":pricehigh{$i}"] = $price2[$i];
    }

php pdo query fails to return result despite prepared statement being correct

So after much debugging and many different coding attempts I eventually retrieved teh required result.

I tried numerous attempts to use binParam but the returned result would always be a null string.

I had changed to mysql procedure to output the required filename so the mysql command SELECT @filename would provide the desired result.

I then retrieved this value with the following:

   /**** Query - export file name ****/
   $sql = 'call sp_CDRbyCustomer( \'' . $structure_name . '\', \'' . $cug_uid . '\', DATE(DATE_SUB(NOW(), INTERVAL ' . $num_weeks . ' WEEK)), DATE(NOW()),  @filename )';

    $stmnt = $dbConn->prepare($sql);
    debug($stmnt);
    $stmnt->execute();
    $stmnt->closeCursor();
    $filename_result = $dbConn->query("select @filename")->fetch();
    $filename = $filename_result['@filename'];          

I'm not sure if this is the best way to do this but it does produce the result I desire :-)

---

Related Topics