[Dbix-class] Get generated SQL

[Mark Lawrence]

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:
> >  
> >>Mark Lawrence wrote:
> >>    
> >>>You don't deal with binary (non-printable) data. Assuming this is method
> >>>is for debugging purposes its usefulness is limited to pure text and
> >>>won't work for mixed text/binary queries.
> >>> 
> >>>      
> >>Specifying the data type for the bind columns should allow the driver to 
> >>properly escape binary data where appropriate, albeit non-automatically.
> >>    
> >
> >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 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).

Mark.
-- 
Mark Lawrence