One major benefit of placeholders is exactly what you said. If you were
transferring data from one table to another, you can see another benefit:
<typed into body - not debugged - for concept only>
# we have two database handles open - $sourceh, and $targeth:
my $select = qq{select column1, column2 from $tablename where $where};
my $selecth = $sourceh->prepare($select) || die "Can't
prepare\n$select\n$DBI::errstr\n";
my $insert = qq{INSERT INTO SOMEOTHERTABLE (column1, column2) VALUES (?,
?)};
my $inserth = $targeth->prepare($insert) || die "Can't
prepare\n$insert\n$DBI::errstr\n";
$selecth->execute() || die "Can't execute\n$select\n$DBI::errstr\n";
my @vals;
$selecth->bind_columns(undef, \@vals[1,2]);
while ($selecth->fetch) {
$inserth->execute(@vals) || die "Can't execute\n
$insert\nWith values: @vals\n$DBI::errstr\n";
}
<end of scriptlet>
You only prepared once, but you can execute multiple times without
re-preparing - that's a major performance gain.
Another reason is if you are taking input from a user, and they enter:
O'Reilly
for their last name. Now if you have this:
my $insert = qq{INSERT INTO INFO (LastName) VALUES ('$lastname')};
(no placeholder), then the query to be passed to your DBMS will actually
read:
INSERT INTO INFO (LastName) VALUES ('O'Reilly')
and that will be a syntax error. Placeholders save you from all such
worries.
###################################
Now, about saving information in another file, yes, and I like to do this,
you can save information in another file, and use it in the script. I like
the INI format, but it is certainly not the only configuration file format.
You can store database names, or whatever in such files. Example:
I have a file somewhere named "myfile.ini". It contains this:
dbname=MyDB
userid=MyName
ipaddress=192.168.10.1
pw=foo
DSN=MyDSN
(I don't personally like to put a password in an ini file).
Now in your the perl script, I would load this information like this:
my %ini; #declare a hash
my $path = "C:/blah/";
open (INIFILE, "${path}MyFile.ini") || die "Can't open ini $!";
foreach (<INIFILE>) {
if (/^\s*(.+?)=(.+?)\s*$/) {
$ini{lc($1)} = $2;
}
}
# now, just for grins, print out what you have:
foreach (sort keys %ini) {
print "\$ini{$_} has a value of $ini{$_}\n";
}
I used the lc function to put every key name into lower case to avoid case
problems when I change something in a file. Now it's all in your %ini hash
for use in your program.
for example in a connection:
my $dbh - DBI->connect("DBI:ODBC:$ini{dsn}", $ini{userid}, $ini{pw}) ||
die....
Is that what you are looking for?
Steve H.
-----Original Message-----
From: Keith A. Calaman [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 04, 2002 5:25 PM
To: Steve Howard; [EMAIL PROTECTED]
Subject: RE: pop-up box JOIN() to mysql not populating dates
Thanks for the response and sorry for the delay we have had utility
problems. I did use your suggestions on the errors. I got it to work. Can
you comment on the use of placeholders? It looks like the major benefit is
portability, where I can put info from different forms into the same
queries. I am just getting started with PERL and programming but I have
learned a lot with the help of many sources including Mr. Howard and this
group. I am going to sit down and rethink everything in terms of variable
declarations which will streamline everything I think. How can I point to
values in another file to be used as values in certain scripts...for
example, the user and password information to be used to connect to the
database. This way if I change my password in the database I only have to
change it in one place instead of in all my scripts. This would be a neat
tool to streamline everything! Thanks again.
-----Original Message-----
From: Steve Howard [mailto:[EMAIL PROTECTED]]
Sent: Sunday, February 03, 2002 12:41 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: pop-up box JOIN() to mysql not populating dates
Correction to a syntax error I left. The insert query should be:
my $insert = qq{INSERT INTO News
(ntitle, ndate, nurl, nblurb, nsource)
VALUES(?, ?, ?, ?, ?)};
I pasted yours in, and edited, and forgot to delete the first double quote.
Just wanted to clear that up since I was commenting on your code :)
Steve H.
-----Original Message-----
From: Steve Howard [mailto:[EMAIL PROTECTED]]
Sent: Saturday, February 02, 2002 11:00 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: pop-up box JOIN() to mysql not populating dates
Keith, I can't tell much from what I'm seeing that should cause a problem. I
might suggest a couple of steps for tracking it down, then I'll offer you a
bit of advice on safely inserting data that in taken from user input.
First, you have said that the print from the year prints the date in the
format that MySQL wants it, have you tried printing the entire query,
copying that to your query tool, and seeing if MySQL takes the input as the
query is produced?
That might at least tell us if the problem is with how
the data is being seen by MySQL, or if it is elsewhere, and it will take all
the "should's" out of the troubleshooting that part.
When you are satisfied that the data can be inserted (maybe even before),
you need to be careful with the way you are handling the insert query -
especially if users are allowed to enter data. What if a user inputs a value
with an apostrophe? It will cause a failure the way you are doing this. To
safely handle user input, to be able to prepare a query and execute several
times with different data, or other reasons, you need to use placeholders
instead of putting your variables into your query. And one more thing, you
need to capture an error if it is produced - just like you have with
connection. To make your error messages more meaningful without the extra
typing, turn your entire query into a variable before you prepare. I would
handle your insert query like this:
</snip>
my $insert = qq{"INSERT INTO News
(ntitle, ndate, nurl, nblurb, nsource)
VALUES(?, ?, ?, ?, ?)};
my $inserth = $dbh->prepare($insert) || die "Can't
prepare\n$insert\n$DBI::errstr\n";
$inserth->execute($ntitle, $ndate, $nurl, $nblurb, $nsource) || die
"Can't execute\n$insert\nWith values:\n$ntitle, $ndate, $nurl, $nblurb,
$nsource
$DBI::errstr\n";
</snip>
Putting the error capturing like I have done will also tell us if there is a
problem inserting, if so exactly what the error is - no guesswork - and you
know what data you're dealing with.
As you see, the placeholders are not quoted when you prepare, and they are
arguments to $inserth->execute() in the same order as the query expects
them.
Hopefully with all that, you can narrow the error search and get to where
the problem is, and can write that so that a user can't screw you up by
putting in a value that will cause problems with the query.
If you get an error message, and want to go further, I'll be happy to see
what we can do.
Steve H.
-----Original Message-----
From: Keith A. Calaman [mailto:[EMAIL PROTECTED]]
Sent: Saturday, February 02, 2002 10:11 PM
To: [EMAIL PROTECTED]
Subject: pop-up box JOIN() to mysql not populating dates
I am resending this to the group because I haven't gotten a response nor
have I got it to work. I won't send it again but I thought someone who
could help may have missed it. I have pasted my code below. The problem I
am having is:
my $ndate = join('-', $nyear,$nmonth,$nday);
is printing back to my form the exact format that should be correct to
insert into mysql. However, when the INSERT runs the value does not get
inserted properly. $nyear,$nmonth,$nday are grabbed from values a user
selects via three pop-up select boxes. Anyone have any ideas? Also, I am
new to this so your comments on my code are welcomed and appreciated.
Thanks in advance.
#!/usr/bin/perl -w
use strict;
use diagnostics;
print "Content-Type: text/html\n\n";
use CGI;
use DBI;
#declare form variables
my $query = CGI->new();
my $ntitle = $query->param('ntitle');
my $nyear = $query->param('year');
my $nmonth = $query->param('month');
my $nday = $query->param('day');
my $nurl = $query->param('nurl');
my $nblurb = $query->param('nblurb');
my $nsource = $query->param('nsource');
my $tracker = $query->param('tracker');
my $hostname = 'www.host.com';
my $database = 'sd';
my $user = 'usr';
my $password = 'guess';
my $driver = 'mysql';
#convert form variables
my $ndate = join('-', $nyear,$nmonth,$nday);
#connect to database
my $dsn = "DBI:$driver:database=$database;host=$hostname";
my $dbh = DBI->connect($dsn, $user, $password) or die "Cant connect to
the DB:
$DBI::errstr\n";
#determine what to do
if ($tracker eq 0){
print <<EndOfHTML;
<html>
<body>
<FORM METHOD=POST ACTION=" ">
<INPUT TYPE="hidden" NAME="tracktype" VALUE="2">
<INPUT TYPE="hidden" NAME="tracker" VALUE="1">
<INPUT TYPE="hidden" NAME="ntitle" VALUE="$ntitle">
<INPUT TYPE="hidden" NAME="ndate" VALUE="$ndate">
<INPUT TYPE="hidden" NAME="nurl" VALUE="$nurl">
<INPUT TYPE="hidden" NAME="nblurb" VALUE="$nblurb">
<INPUT TYPE="hidden" NAME="nsource" VALUE="$nsource">
Title: $ntitle<br>
Date: $ndate<br>
URL: $nurl<br>
Blurb: $nblurb<br>
Source: $nsource<br>
<P><INPUT TYPE=submit>
</body>
</html>
EndOfHTML
}elsif($tracker eq 1){
my $sth = $dbh->prepare("INSERT INTO News
(ntitle, ndate, nurl, nblurb, nsource)
VALUES('$ntitle', '$ndate', '$nurl', '$nblurb', '$nsource')");
$sth->execute();
my $new = $dbh->prepare("select N_ID, NDate, NSource, NTitle, NBlurb, NURL
from News WHERE NApprove=0");
$new->execute();
my $news;
while (my @row = $new->fetchrow_array()) {
$news .= qq| <value="$row[0]"> $row[1] <i>$row[2]</i><br>
<a href="$row[5]"><b>"$row[3]"</b></a><br>$row[4]..<br><br>\n|;
}
print <<EndOfHTML;
<html>
<body>
$news
</body>
</html>
EndOfHTML
}elsif($tracker eq 2){
print("there!\n");
}else{
print("failure!\n");
}
$dbh->disconnect;
exit;
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
