3

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

1 Answers1

4

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.)

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I felt this is such an inconvenient design. A better design would allow to output the raw sql in say debug mode, then in real mode it's disabled completely. Is it possible to extend mojolicous to write a sub to output the raw sql by simply replacing the placeholder with the real parameters? –  Oct 31 '22 at 03:08
  • @John There is never a SQL with all the placeholders substituted. The SQL and the values are given to the SQL database, and the full query is only created over there. [It seems possible](https://serverfault.com/questions/814710/mysql-general-query-log-show-prepared-argument-values) to show the full query in the MySQL server log. – amon Oct 31 '22 at 04:02
  • 1
    @John, You might find queries where the '?' character occurs in strings or in comments or even in identifiers. You'll have to write a full-blown SQL parser in your sub to sort out the real parameter placeholders from other uses of the '?' character. – Bill Karwin Oct 31 '22 at 13:57