Mike wrote:
Hi,
I have a variable setup as $pid and i've tried to call this in the prepare part of the DBI module but i'm get an "Unknown column $pid" syntax error. Probably $pid was passed as a string instead of a variable inside the prepare (''). Anyone has an idea of how i can fix this? Thanks.
DBD::mysql::st execute failed: Unknown column '$pid' in 'where clause' at
createThesaurus.pl line 33,
-------------------------------------------------------
while ( <NEW> ) {
next if $. == 1; # exclude header
chomp;
my ($pid,$thesaurus_from,$thesaurus_to,$thesaurus_type) = split /\|/;
#Update override_exclude set th_flag="N" to indicate that thesaurus entries
#have been added to sierra2_thesaurus.xml
use DBI;
my $dbh = DBI->connect("dbi:mysql:endeca_tracking",$user,$password);
my $sth = $dbh->prepare('update override_exclude set th_flag="N" where pid=$pid') or
die "Couldn't prepare statement" .$dbh->errstr;
The prepare statement here is inside single quotes, inside of which Perl
doesn't interpolate variables (it does inside double quotes). But DBI
has a mechanism called "placeholders" that allow you to not worry about
quoting and interpolation of variables, documented under the section
"Placeholders and Bind Values" in the DBI documentation.
You can change your prepare statement to:
$dbh->prepare('update override_exclude set th_flag=? where pid=?')
(the question marks denote the placeholder)
$sth->execute;
And then change your execute to:
$sth->execute( "N", $pid );
to pass along the list of values meant to take the place of your
placeholders. This is generally safer than passing variables directly
to your prepare statement. For instance, if $pid held a nasty string
that terminated the original statement and then followed up with
something destructive, such as "5; truncate table_foo;", bad things
could happen. With placeholders you don't have to worry about that, as
the database driver fixes that up by quoting it properly (not to say you
shouldn't still validate input to a database!)
Hope that helps a bit,
-- Douglas
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
