Can mojolicious output the prepared sql query?

Question

Perl Mojolicious use query with placeholder to prevent SQL injection. But the problem is, sometimes I want to see what's the query look like. Is there a way to print the query with all placeholders replaced with the real values? I know I can do a replace by myself. But I have to do it every time I want to debug the SQL. It's so cumbersome. If mojolicious has a way like $c->mysql->output_last_sql(), it'll be amazing.

my $sql=q|
SELECT
    count(*) cnt
FROM
    my_table
WHERE
id= ?
    |;

# I know I can do below by myself
print q1|
SELECT
    count(*) cnt
FROM
    my_table
WHERE
id= $c->param('id')
    |;

my $query=$c->mysql->db->query($sql, $c->param('id'));

# how can I print the real SQL with all placeholders replaced?
print $query->hash()->{cnt};

I checked the document of Mojolicious but didn't find anything useful. https://docs.mojolicious.org/Mojo/Pg/Database

Answer 1

The advantage of query parameters is that they are not simply string-replacements into the SQL query. If they were, they would still cause a risk of SQL injection. The way parameters work is that they are never replaced in your query until after the query is prepared. Parsing occurs during the prepare step, so if parameter values are not combined with the query until after parsing, then there's no way for the values to cause mischief with the SQL syntax.

That means you can't get the SQL combined with its parameters in the client.

The only workaround is to use the query log on the MySQL Server. I give an example here: Getting raw SQL query string from PDO prepared statements. That's about PHP, not Perl, but it works the same regardless of which language you use.

(With exceptions only for client connectors that create fake prepared statements, and actually do interpolate parameters into the SQL string, then send it to the MySQL Server. For example, Python's connector does this by default, for example, but you can optionally make even Python use true prepared statements.)