DBI

[Bigus]

Hi

I have a mySQL database with a table called "tickets" in it. There are 3
columns in the table: email | time | ticket.

I have 3 variables $email, $time and $ticket containing an email address,
Perl time and session ticket, respectively.

What I want to do is check if a row already exists in the database for the
email address contained in $email. If it does then perform an UPDATE, if it
doesn't INSERT a new row.

This is the basic code I have for this task:

======================
# check if row exists and update / insert as appropriate
@row = $db->selectrow_array("SELECT * FROM tickets WHERE email = '$email'");

if($row[0] =~ /\w+/){
$db->do("UPDATE tickets SET time = '$thetime', ticket = '$ticket'
WHERE email = '$email'");
}
else{
$db->do("INSERT INTO tickets (email,time,ticket) VALUES
('$email','$thetime','$ticket')");
}

# check for DB errors
if($db->errstr){
print $db->errstr;
}
========================

That works to a point, but it's not bullet-proof. ie: some email addresses
have, for example, a single quote in them like:

f.o'(e-mail address removed)

A quote causes the above to generate the following error:

You have an error in your SQL syntax near
'(e-mail address removed)','1065092739','618BA732FF2A739F6E')' at line 1.

Having looked at the DBI CPAN page I explored qq{..} and q{..}but can't see
to get them to work. That is, in the case of the INSERT row:

1) $db->do(qq{INSERT INTO tickets (email,time,ticket) VALUES
('$email','$thetime','$ticket')});
or
$db->do(qq{INSERT INTO tickets (email,time,ticket) VALUES
($email,$thetime,$ticket)});

comes up with the same ERROR as above.

q{..} doesn't apparently handle interpolated variables, so any ideas how I
can make this bullet proof, regardless of what characters $email might
contain in it?

Thanks

Bigus

 

[ko]

Bigus wrote:

[snip]

That works to a point, but it's not bullet-proof. ie: some email addresses
have, for example, a single quote in them like:

f.o'(e-mail address removed)

A quote causes the above to generate the following error:

You have an error in your SQL syntax near
'(e-mail address removed)','1065092739','618BA732FF2A739F6E')' at line 1.

Look into the placeholders and bind values section of the DBI docs.

HTH - keith

 

[Bigus]

Bigus wrote:

[snip]

That works to a point, but it's not bullet-proof. ie: some email addresses
have, for example, a single quote in them like:

f.o'(e-mail address removed)

A quote causes the above to generate the following error:

You have an error in your SQL syntax near
'(e-mail address removed)','1065092739','618BA732FF2A739F6E')' at line 1.

Look into the placeholders and bind values section of the DBI docs.

Thanks.. sorted with the $db->quote() command in this case. The placeholders
& binding thing looks like it might be useful for other parts of my script.

Regards
Bigus

 

[Tore Aursand]

# check if row exists and update / insert as appropriate
@row = $db->selectrow_array("SELECT * FROM tickets WHERE email = '$email'");

if($row[0] =~ /\w+/){
$db->do("UPDATE tickets SET time = '$thetime', ticket = '$ticket'
WHERE email = '$email'");
}
else{
$db->do("INSERT INTO tickets (email,time,ticket) VALUES
('$email','$thetime','$ticket')");
}

# check for DB errors
if($db->errstr){
print $db->errstr;
}

Problems occur when the data you insert into the SQL query contain single
quotes (or other non-escaped special characters).

The easiest way to deal with problems like this is to _always_ bind
variables into your SQL query. I recommend this way of doing it even when
you are 110% sure that the data you're about to insert into the SQL is
"clean";

my $stExists = $dhh->prepare('SELECT COUNT(*)
FROM tickets
WHERE email = ?');
$stExists->execute( $email );
my ( $exists ) = $stExists->fetchrow();
$stExists->finish();

if ( $exists ) {
my $stUpdate = $dbh->prepare('UPDATE tickets
SET time = ?,
ticket = ?
WHERE email = ?');
$stUpdate->execute( $thetime, $ticket, $email );
$stUpdate->finish();
}
else {
my $stInsert = $dbh->prepare('INSERT INTO tickets
VALUES (email, time, ticket)
(?, ?, ?)');
$stInsert->execute( $email, $thetime, $ticket );
$stInsert->finish();
}

Lookup 'perldoc DBI' for more information about bind'ing values into SQL
queries.

 

[Michael Budash]

Problems occur when the data you insert into the SQL query contain single
quotes (or other non-escaped special characters).

The easiest way to deal with problems like this is to _always_ bind
variables into your SQL query. I recommend this way of doing it even when
you are 110% sure that the data you're about to insert into the SQL is
"clean";

very good advice. also it prevents having to think about which method
you'll use - just _always_ use placeholders.

my $stExists = $dhh->prepare('SELECT COUNT(*)
FROM tickets
WHERE email = ?');
$stExists->execute( $email );
my ( $exists ) = $stExists->fetchrow();
$stExists->finish();

if ( $exists ) {
my $stUpdate = $dbh->prepare('UPDATE tickets
SET time = ?,
ticket = ?
WHERE email = ?');
$stUpdate->execute( $thetime, $ticket, $email );
$stUpdate->finish();
}
else {
my $stInsert = $dbh->prepare('INSERT INTO tickets
VALUES (email, time, ticket)
(?, ?, ?)');
$stInsert->execute( $email, $thetime, $ticket );
$stInsert->finish();
}

Lookup 'perldoc DBI' for more information about bind'ing values into SQL
queries.

also, the 'replace' query can be very useful:

http://www.mysql.com/doc/en/REPLACE.html

 

[Tore Aursand]

very good advice. also it prevents having to think about which method
you'll use - just _always_ use placeholders.

That's right. I _always_ bind nowadays. Don't need that quote() method
or anything. Just bind away!

also, the 'replace' query can be very useful:

Isn't that _very_ MySQL specific?

 

[Michael Budash]

That's right. I _always_ bind nowadays. Don't need that quote() method
or anything. Just bind away!


Isn't that _very_ MySQL specific?

not sure, quite possibly, but the o.p. did say:

 

[Tore Aursand]

not sure, quite possibly, but the o.p. did say:
[...]

No matter what the OP said; My general advice is to _always_ avoid using
proprietary solutions. At least _try_ to avoid using them. Some day you
might end up rewriting your whole application because some retard thinks
that Oracle is a better database then MySQL.

 

[Bigus]

very good advice. also it prevents having to think about which method
you'll use - just _always_ use placeholders.

Thanks, I shall heed that - although, for one-off DB commands, I think using
the $db->quote() method (when used with say a $db->do()) may require less
lines of code and save the need to open & "finish" other handles like
$sth->.

Regards

Bigus

 

[Eric J. Roode]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thanks, I shall heed that - although, for one-off DB commands, I think
using the $db->quote() method (when used with say a $db->do()) may
require less lines of code and save the need to open & "finish" other
handles like $sth->.

The $dbh->do() method accepts bind values.

- --
Eric
$_ = reverse sort $ /. r , qw p ekca lre uJ reh
ts p , map $ _. $ " , qw e p h tona e and print

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBP32jOWPeouIeTNHoEQIBeACfZgo/sdjSpSSyUsgHyF/3WY7qA5YAoKBA
Ol2BpJ9LZUNsZ3g7bNQITMtL
=Q31I
-----END PGP SIGNATURE-----

 

[pkent]

Problems occur when the data you insert into the SQL query contain single
quotes (or other non-escaped special characters).

The easiest way to deal with problems like this is to _always_ bind
variables into your SQL query. I recommend this way of doing it even when
you are 110% sure that the data you're about to insert into the SQL is
"clean";

And so do I. For a start you dispense with any mucking about trying to
ensure your data really really is clean. Also you can make wins in
efficiency and speed (but probably depending on your database engine and
exact requirements) if you use placeholders. E.g. here's a trivial
example; imagine I want to select all the customer codes where the
customer's region is 'southwest', and I also want to select them where
the region is 'north'.

# $dbh is a database handle

my $sth = $dbh->prepare('select cust_id
from customers
where region = ?');

$sth->execute('southwest');
# do stuff with data

$sth->execute('north');
# do stuff with data, but I used the same statement handle


That statement handle can be cached and used again and again, which is a
good thing. There's also the prepare_cached method.

P

 

[Tore Aursand]

Thanks, I shall heed that - although, for one-off DB commands, I think
using the $db->quote() method (when used with say a $db->do()) may
require less lines of code and save the need to open & "finish" other
handles like $sth->.

The do() method also supports bind;

$dbh->do( 'SELECT * FROM user WHERE user_id = ?', undef, $user_id );

Have a look at 'perldoc DBI' for more information.