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.
Get query back from PDO prepared statement
Try $statement->queryString.
Getting a PDO query string with bound parameters without executing it
In short: no. See Getting raw SQL query string from PDO prepared statements
If you want to just emulate it, try:
echo preg_replace('?', $username, $result->queryString);
PDO sends raw query to MySQL while Mysqli sends prepared query, both produce the same result
Your PDO is configured to emulate prepared queries, whereas mysqli is using true prepared queries.
The prepared query binds the string ''1''
as an integer parameter value. PHP coerces it to an integer using something like intval()
. Any string with non-numeric leading characters is interpreted as 0 by PHP, so the parameter value sent after prepare is the value 0.
The fake prepared query uses string interpolation (instead of binding) to add the string ''1''
into the SQL query before MySQL parses it. But the result is similar, because SQL also treats a string with non-numeric leading characters in an integer context as the value 0.
The only difference is what ends up in the general query log when the parameter is bound before prepare versus after prepare.
You can also make PDO use real prepared queries, so it should act just like mysqli in this case:
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
PS: This may demonstrate a good reason why it's customary to start id values at 1 instead of 0.
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.
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 queryString with binded data
PDOStatement->debugDumpParams is what you want. You may need to use output buffering though as the results are echoed out.
return query of pdo statements
This is quite complicated task and one of drawbacks of using prepared statements.
Yet API doesn't offer such a useful feature out of the box. So, the only way to get raw SQL is to parse placeholders manually. There are some home-brewed solutions in this answer
Related Topics
How to Remove Duplicate Values from an Array in PHP
Parse Query String into an Array
File_Get_Contents(): Ssl Operation Failed With Code 1, Failed to Enable Crypto
How to Get Parameters from a Url String
PHP Syntax For Dereferencing Function Result
How to Access This Object Property With an Illegal Name
PHP Function to Generate V4 Uuid
Only Variables Should Be Passed by Reference
How to Display HTML Tags as Plain Text
Should I Manually Check For Errors When Calling "MySQLi_Stmt_Prepare"
Is There a Code Obfuscator For PHP
Angularjs Http Post to PHP and Undefined
Use an Array in a MySQLi Prepared Statement: 'Where .. In(..)' Query
Replace Keys in an Array Based on Another Lookup/Mapping Array