[Dbix-class] Get generated SQL

Matt Lawrence matt.lawrence at ymogen.net
Wed Mar 12 16:20:16 GMT 2008

Mark Lawrence wrote:
> On Wed Mar 12, 2008 at 03:37:33PM +0000, Matt Lawrence wrote:
>> Mark Lawrence wrote:
>>> On Wed Mar 12, 2008 at 12:30:14PM +0000, Matt Lawrence wrote:
>>> Yes, the escaping would make the data safe/suitable for the database,
>>> but that won't change the effects that binary data has on terminals.
>> AFAIK, escaping for the database will also make the data printable. 
>> Perhaps this is not true for every driver, though.
> ?? I would be surprised if that was the case for any driver. Escaping a
> binary blob of potentially all unprintable characters is going to, at a
> minimum, double the size of that data. And I would see no need for it,
> because while we are able to talk to databases using terminals, there is
> no requirement for the DB server end of the communication pipe to be
> restricted to printable characters.

I know it to be the case for DBD::Pg, bytea fields are required to be 
escaped in a printable way. Anyway, I take your point about limited use 
for debugging, see below.

>>>> I guess I could add a failsafe to trap unprintable values still present 
>>>> after quote() and replace them with dummy values as in your previous 
>>>> example.
>>>>   # Store quoted versions of the values
>>>>   my @bind_vals = map {
>>>>       $_ = $storage->dbh->quote($_->[1], shift @$datatypes);
>>>>       $_ = $storage->dbh->quote('*BINARY DATA*') if /[^[:print:]\n\t]/;
>>>>       $_;
>>>>   } @$bind;
>>> You don't need to care about newlines or tabs (in fact if you
>>> specifically search for that you might not find what you are looking
>>> for), you just want to know if it contains non-printable data:
>>>   $_ = $storage->dbh->quote('*BINARY DATA*') if /[^[:print:]]/;
>> Newlines and tabs won't mess up terminals and they are not matched by 
>> [:print:].
> Take another look at what you are doing above. In plain english you are
> looking for an unprintable character followed by a newline followed by a
> tab. What happens if your data is "???ABC\n\t" where ??? are the
> unprintable characters? Ie, the data is not terminated by non-printables.
> That won't match your regex. And the newline and tab are anyway not
> being replaced in that statement - you are setting the whole string to
> '*BINARY DATA*' (which btw probably doesn't need to be quoted by DBI).
Nope, the \n\t was inside the (negated) character class. It matches if 
the string contains any character which is not printable, a newline or a 

I take your point about the quoting, there's no point trying to make 
something keep it being valid SQL

How about this:

  my @bind_vals = map {
      my $data_type = shift @$datatypes;
      $_[1] =~ /[^[:print:]\n\t]/ ? '*BINARY DATA*' : $storage->dbh->quote($_[1], $data_type)
  } @$bind;


More information about the DBIx-Class mailing list