Print MySQL query as executed in the server

[GhiaMar]

Hi! I'm developing a component and for logging reasons to debug some potential difficult issues I'm logging the SQL query executed at some points of the component process.

An example query could be:

$query = $this->db->getQuery(true)
     ->select(
        [
             $this->db->quoteName('token'),
         ]
    )
    ->from($this->db->quoteName('#__my_cool_component'))
    ->where($this->db->quoteName('id') . ' = :id')
    ->where($this->db->quoteName('state') . ' = 1')
    ->bind(':id', $id, ParameterType::INTEGER)
    ->setLimit($this->limit, $this->limitStart);

$this->db->setQuery($query);
$rows = $this->db->loadAssocList();

I want to log that query, but using $query->__toString() I get the query but without the actual values, I mean, I get the placeholders of the values, but no the values itself

SELECT `token`

FROM `#__my_cool_component`

WHERE `id` = :id AND `state` = 1 LIMIT  300, 100

In the query example, the ID value iterates from 1 to 5. ¿Can I get the exact query as executed in the MySQL server? with the :id placeholder replaced with the actual integer value?

Thanks!

[Kamalakanta Biswal]

assign in a variable  $storeQuery = $query->dump();   and echo taht variable.

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-gene...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/joomla-dev-general/9a4f06c3-08d0-4e78-902d-9214090ed407n%40googlegroups.com.

[GhiaMar]

Done, buit dump method got deprecated without replacement!

Anyway I got the same result with dump

<pre class="jdatabasequery">
SELECT `token`
FROM `cjpcfaro_my_cool_component`
WHERE `id` = :id AND `state` = 1 LIMIT 400, 100</pre>

No :id real value!

[Mathew Lenning]

I usually just use

echo $dbo->replacePrefix($query);

[GhiaMar]

Thanks but no luck! I haven't mention that I'm using J4...

I got this as result of $this->db->replacePrefix($query)

SELECT `token`
FROM `cjpcfaro_my_cool_component`
WHERE `id` = :id AND `state` = 1 LIMIT 100

[George Wilson]

You cannot view what you are after. With prepared statements the statements only get executed inside MySQL (or Postgres if your one of the 0.05% of users using it :) ) - they aren't actually merged together by Joomla at any point.

There's more info on this in the PHP Docs here https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

We chose to utilise prepared statements knowing this tradeoff because they offer amazing protection against SQL Injection attacks (as noted on that page) and decided on our side this was an acceptable tradeoff - in terms of we'd delegate more to the DB (with a minor performance loss in certain cases) against significantly improved security through the stack for implementors.

[GhiaMar]

Cooooool! thanks a lot! Now I know that I can get the SQL statement but no the vars, that's ok for me.