[Dbix-class] Get generated SQL

[Matt Lawrence]

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

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;


Matt