Found nn messages in the processing-messages database

[Rick Cooper]

> -----Original Message-----
> From: mailscanner-bounces at lists.mailscanner.info 
> [mailto:mailscanner-bounces at lists.mailscanner.info] On Behalf 
> Of Julian Field
> Sent: Saturday, April 25, 2009 7:59 AM
> To: MailScanner discussion
> Subject: Re: Found nn messages in the processing-messages database
> 
> 
> 
> On 24/04/2009 12:13, Rick Cooper wrote:
> >
> >
> >    
> >> -----Original Message-----
> >> From: mailscanner-bounces at lists.mailscanner.info
> >> [mailto:mailscanner-bounces at lists.mailscanner.info] On Behalf
> >> Of Julian Field
> >> Sent: Friday, April 24, 2009 4:41 AM
> >> To: MailScanner discussion
> >> Subject: Re: Found nn messages in the processing-messages database
> >>
[...]
> >>>>
> >>>>
> >>>>          
> >>> Wouldn't
> >>>
> >>> 	my $sth = $MailScanner::ProcDBH->prepare(q{DELETE FROM
> >>>        
> >> processing
> >>      
> >>> WHERE (id='?')});
> >>> Then
> >>> 	$sth->execute($id);
> >>>
> >>> Work?
> >>>
> >>>        
> >> Yes, but it's slower as you aren't taking advantage of
> >> pre-preparing the
> >> statement and using placeholders to bind values to it.
> >>
> >>      
> > No, you misunderstand, you do the prepare in the same spot 
> as now but change
> > that line to
> >
> > my $sth = $MailScanner::ProcDBH->prepare(q{DELETE FROM 
> processing WHERE
> > (id='?')});
> >
> > You use the q{...} around the complete statement and change 
> $id=? To $id='?'
> > (this is actually talked about in the module documentation)
> >    
> That sounds wrong, but I'm prepared to believe you. Can you show me 
> where in the documentation it says to do that?

You have to drive down to the underlying DBI that the driver works with, and
you will see all kinds of examples that use the style listed above. Then all
of the sudden the author mentions

"The q{...} style quoting used in these examples avoids clashing with quotes
that may be used in the SQL statement. Use the double-quote like qq{...}
operator if you want to interpolate variables into the string. See "Quote
and Quote-like Operators" in perlop for more details."

Ordinarily when you use strings in an SQL statement you would use something
like F1='F2'. And if there is any doubt that a field might be a reserved
name them `F1` = 'F2'.  So pretty much every example in the parent module
uses the q{...} style which would make statements like
 my $sth = $MailScanner::ProcDBH->prepare(q{DELETE FROM processing WHERE
(id='?')});

Look more like an sql statement. I am just guessing at this as there is no
actual example of doing this. I personally never use perl for handling sql
related tasks I find the entire processes tedious and unnecessarily complex,
so I could well be wrong and I have no way to test it because I don't use
postfix either. Perhaps the SQLite driver or DBI module automatically quotes
the placeholder values but I see nothing in the docs to that effect either.
IN fact if they did it would mean you couldn't use placeholders to reference
tables,fields, etc. If they do not then either the statement or the value
should be quoted or cast as a string such as my $SqlId = "'$id'"; and
$sth->execute($SqlId);

The reason that I believe the problem is quoting is because in the version
that works

$MailScanner::ProcDBH->do("DELETE FROM processing WHERE (id='$id')");

The $id would be correctly quoted. I cannot believe that there is no way to
use a prepared statement that is not also correctly quoted. Either the
module/driver or you would have to do it.

Rick




--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.