Solved

Perl DBI bind parameter problem

Posted on 2008-10-15
5
1,053 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now