Solved

Perl DBI bind parameter problem

Posted on 2008-10-15
5
1,063 Views
Last Modified: 2012-05-05
I have a script that I'm using to change my database passwords for multiple databases.  Most of it works, but I can't seem to get the bind parameters on the $sth->execute statement to work.  Here is the code:

use DBI;

$oldpwd = $ARGV[0];
$newpwd = $ARGV[1];

$whichhost = `hostname -s`;
chop($whichhost);

if($whichhost =~ /^new-cleaner/) {
        open(P, "pdbases.txt") or die "Cannot open database hosts file\n";
}

while(<P>) {
        $dbase = $_;
        chop($dbase);

        $dbh = DBI->connect("dbi:Oracle:$dbase","gerdesk","$oldpwd")
                or die "Can't connect to Oracle database: $dbh->errstr\n";

        $sth = $dbh->prepare( 'alter user GERDESK identified by ? replace ?' );
        $sth->execute( $newpwd, $oldpwd ) or die "Can't execute SQL statement: $dbh->errstr\n";

        print "Successfully updated password on $dbase from $oldpwd to $newpwd\n";
}

if($whichhost =~ /^new-cleaner/) {
        close(P);
}

The $oldpwd goes in fine, and I connect to the database, but when I pass the $oldpwd as a bind parameter to the execute, I get this lovely error:

DBD::Oracle::st execute failed: ORA-01036: illegal variable name/number (DBD ERROR: OCIBindByName) [for Statement "alter user GERDESK identified by ? replace ?" with ParamValues: :p1='A11tyd00z!', :p2=undef] at changedbpasswd.pl line 23, <P> line 1.

So $oldpwd is undef in the execute.  Why?  
0
Comment
Question by:texasreddog
  • 3
5 Comments
 
LVL 14

Expert Comment

by:tomaugerdotcom
ID: 22722917
Do you need the oldpwd at all?

dbh->do("alter user GERDESK identified by ?", $newpwd) or die $DBI::errstr;
0
 
LVL 14

Accepted Solution

by:
tomaugerdotcom earned 500 total points
ID: 22722949
Alternatively, just for debugging purposes, have you tried to see whether this works without the bind variables, by just putting the variables into the prepare string?

$dbh->prepare("alter user GERDESK identified by $newpwd replacing $oldpwd");
0
 
LVL 10

Expert Comment

by:MadShiva
ID: 22723072
HI !



$oldpwd = $ARGV[0];
$newpwd = $ARGV[1];

$whichhost = `hostname -s`;
chop($whichhost);

if($whichhost =~ /^new-cleaner/) {
        open(P, "pdbases.txt") or die "Cannot open database hosts file\n";
}

while(<P>) {
        $dbase = $_;
        chop($dbase);

        $dbh = DBI->connect("dbi:Oracle:$dbase","gerdesk","$oldpwd")
                or die "Can't connect to Oracle database: $dbh->errstr\n";

        $sth = $dbh->prepare( 'alter user GERDESK identified by ?' );
        $sth->execute($newpwd) or die "Can't execute SQL statement: $dbh->errstr\n";

        print "Successfully updated password on $dbase from $oldpwd to $newpwd\n";
}

if($whichhost =~ /^new-cleaner/) {
        close(P);
}


Try this, sorry but i can't test now.


Best Regards
0
 
LVL 14

Expert Comment

by:tomaugerdotcom
ID: 22723175
Yeah, what I said.
0
 

Author Comment

by:texasreddog
ID: 22724166
well, the bind parameters don't work.  when I inserted the variables back into the sql query and just ran $sth->execute(), I didn't have a problem.  I'll just forget about the bind parameters for this script.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question