The technique outlined by Paul Boutros is vulnerable to TOCTOU (time of check, time of
use) race conditions. Hence, it is not very reliable unless you are running under a
sufficiently stringent isolation level such that if the row is not already there,
no-one can add it between the time when you look for it and when you add the missing
record (SERIALIZABLE in terms of the ISO SQL standard). A TOCTOU failure is not all
that likely, perhaps, but not impossible. Of course, the insert would probably fail,
but would there be an indication of that...
The first thing to do is to ensure that you look for errors -- the $dbh->do() method
returns an indication of whether it succeeded or not. OK - your code sort of does it,
calling surveyErrTrap, but it always assumes that all errors are due to duplicate
records, and this is not plausible in general. The other thing to do is to ensure
that both $dbh->{RaiseError} and $dbh->{PrintError} are turned off, so that DBI does
not do any error reporting for you, which allows you to provide your own
interpretation of the error. Don't forget that there can be many possible error
conditions other than just 'key is not unique', so you'll need to handle that with
your custom message, and so on. You can use $DBI::err and $DBI::errstr in general --
I assume your driver supports these. There are also per-handle error information
variables - $dbh->{err} and $dbh->{errstr} unless my memory has been blotted out by
the tedium of flying across the USA. Of course, to use those, you have to have the
handle available; the code in surveyErrTrap would have to use $DBI::err/errstr unless
you revise the calling code to pass in the $dbh too.
-------Original Message-------
From: Paul Boutros <[EMAIL PROTECTED]>
Sent: 01/20/03 09:28 AM
To: "Hughes, Andrew" <[EMAIL PROTECTED]>
Subject: Re: Error Handling
>
> Hi,
Before doing the insert statement run a SELECT query from the table
specifying the e-mail address in the WHERE condition.
i.e. use something like:
my $sql = "SELECT 1
FROM USERS
WHERE e-mail = $email";
my $sth = $dbh->prepare($sql);
$sth->execute();
my $val = $sth->fetchrow_array();
if ($val) {
# a row was returned therefore e-mail in DB
# output error-handling page
}
else {
# no row returned so do insert statement
# and write normal page
}
Hope this helps,
Paul
On Mon, 20 Jan 2003, Hughes, Andrew wrote:
> I am relatively new to this list and perl and DBI as well. The goal is
that
> before users submit a form submission to the database, I want to make
sure
> that someone with the same email address has not already submitted it.
The
> Email field is marked as UNIQUE() in the MySQL database table, so when
> someone trys to enter twice, there is an error and the record does not
get
> entered. However, I would like to display a custom page that nicely
> explains that we apprecitate their interest, but they have already been
> entered into the database. With this code, I recieve the Carp error in
the
> browser. When I turn off Carp, the script acts as if the entry was
added
> (no error displayed), but the record is not entered. Can anyone offer
> suggestions on how to make decisions based on a DBI error message ?
>
> The code that adds people to a MySQL database table using the following
> insert statement:
>
> #! /usr/bin/perl -T -w
> # survey.pl
>
> use strict;
> use lib qw(/home/ahughes/myLibrary);
> use CGI qw(:standard escape escapeHTML);
> use CGI::Carp qw(fatalsToBrowser);
> use DBI;
> use WebDB;
>
> ....
>
> sub insert_data {
>
> my $dbh = WebDB::connectSurvey ();
> my $sth;
>
> $dbh->do (qq{INSERT INTO nationalConsumerShoppingSurvey SET
> participantId = ?,
> participantDate = NOW(),
> participantFirstName = ?,
> participantLastName = ?,
> participantPhone = ?,
> participantPhoneExtension = ?,
> participantDivision = ?,
> participantEmail = ?
> },
>
> undef,
> undef,
> $participantFirstName,
> $participantLastName,
> $participantPhone,
> $participantPhoneExtension,
> $participantDivision,
> $participantEmail
> ) or surveyErrTrap("Cannot enter record because of duplicate email");
> return my $dbh;
>
> $dbh->disconnect ();
>
> }
>
> sub surveyErrTrap
> {
> my $error_message = shift(@_);
> die "$error_message\n ERROR: $DBI::err ($DBI::errstr)\n";
> }#end: err_trap
>
> .....
>
> ######################################
>
>
> Thanks,
> Andrew
>
>
