Asked  7 Months ago    Answers:  5   Viewed   35 times

Is there a way to get the raw SQL string executed when calling PDOStatement::execute() on a prepared statement? For debugging purposes this would be extremely useful.

 Answers

67

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.

Wednesday, March 31, 2021
 
hnkk
answered 7 Months ago
24

Your ON DUPLICATE KEY syntax is not correct.

$stmt = $conn->prepare('INSERT INTO customer_info (user_id, fname, lname) VALUES(:user_id, :fname, :lname)
    ON DUPLICATE KEY UPDATE fname= :fname2, lname= :lname2');

$stmt->bindParam(':user_id', $user_id);  
$stmt->bindParam(':fname', $_POST['fname'], PDO::PARAM_STR);
$stmt->bindParam(':lname', $_POST['lname'], PDO::PARAM_STR);      
$stmt->bindParam(':fname2', $_POST['fname'], PDO::PARAM_STR);
$stmt->bindParam(':lname2', $_POST['lname'], PDO::PARAM_STR);      

You don't need to put the table name or SET in the ON DUPLICATE KEY clause, and you don't need a WHERE clause (it always updates the record with the duplicate key).

See http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

You also had a PHP syntax error: you split the query up into two strings.

UPDATE:

To bind multiple parameters:

function bindMultiple($stmt, $params, &$variable, $type) {
  foreach ($params as $param) {
    $stmt->bindParam($param, $variable, $type);
  }
}

Then call it:

bindMultiple($stmt, array(':fname', ':fname2'), $_POST['fname'], PDO::PARAM_STR);
Wednesday, March 31, 2021
 
Gil
answered 7 Months ago
Gil
40

Use PDO's errorinfo() function to find out why.

if( ! $sth = $db->query("SELECT * FROM titles ORDER BY RAND() LIMIT 1") ) {
  die(var_export($db->errorinfo(), TRUE));
}

Late Update

In the interest of making my old answers better, setting PDO to throw exceptions on error is far more manageable than checking every function return.

$dbh = new PDO($connstr, $user, $pwd);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Or, more concisely:

$dbh = new PDO($connstr, $user, $pwd, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
Wednesday, March 31, 2021
 
Juicy
answered 7 Months ago
63

Might not help, but why are you only binding 3 variables, when there are 4? I can't say that I have experience doing this in PHP, but in Perl and Oracle it would throw an error. I'd try binding the 2 SETs and the 1 WHERE, and removing the first assignment, and see if that works.

Wednesday, March 31, 2021
 
RenegadeAndy
answered 7 Months ago
90

You have to bind your variables like you do in your first code. The Version 1 and Version 2 codes are both INSECURE.

Friday, May 28, 2021
 
SilverHorn
answered 5 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share