OK. I am working on converting this to use placeholders and the
"qq" quoting option. I am obviously very new to placeholders, so this is
probably a stupid question, but here goes. I execute the script and receive
the following error:
[EMAIL PROTECTED]:/home/sadmin/sn4265/perl> timex ./passwd2db.pl
Name "main::passwd" used only once: possible typo at ./passwd2db.pl line 25.
Password file for argon found. Now processing...
Undefined subroutine &main::NOW called at ./passwd2db.pl line 33, <FILE>
line 11.
The following is the section of code that includes the "NOW" on line
33. Obviously there is a problem with the way I am doing the placeholders
here.
$dbh->do(qq{
INSERT INTO acct_db VALUES(?,?,?,?,?,?,?)},
undef,$key1,$uid,$gid,$gcos,$home,$shell,NOW())
or print "Error updating database: ", $dbh->errstr, "\n";
Thanks again for the help.
Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED]
Web: http:\\ldsa.sbcld.sbc.com
-----Original Message-----
From: Hardy Merrill [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 22, 2004 12:46 PM
To: [EMAIL PROTECTED]; NIPP, SCOTT V (SBCSI)
Subject: RE: Strange matching problem...
This time HM### - I hate Groupwise! Can't properly quote anything.
>>> "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> 01/22/04 01:32PM >>>
OK... First of all, today, this afternoon, I am going to learn
how
to use placeholders and get that fixed. I promise. :)
HM### Your "non-placeholder" code is not wrong - it can just be done
better with placeholders :)
HM>> I don't see any matching logic in your code below, but here's a
general thought - instead of a string equality check ( if ($acct1 eq
$acct2) ), you could use a regular expression with the "i" (ignore
case
flag) something like ( if ($acct1 =~ /$acct2/i )
The matching logic is actually a part of the MySQL query. This
part
works great, but it was case sensitivity that was giving me the fits.
I
discovered that VARCHAR and CHAR data types in MySQL are not case
sensitive
by default. You have to set the column to have a BINARY column
modifier to
make these data types case sensitive. This seems to have fixed at
least
that problem.
HM### Sorry, I missed that the 1st time around. Here is one of your
selects:
my $test = $dbh->prepare("SELECT * FROM acct_db WHERE key1 =
'$key1'");
HM### you should be able to use something like this (I'm not sure
exactly what MySQL's syntax is for this):
my $test = $dbh->prepare(qq{
SELECT *
FROM acct_db
WHERE UPPER(key1) = UPPER('$key1')
});
or, which placeholders, like this:
my $sth = $dbh->prepare(qq{
SELECT *
FROM acct_db
WHERE UPPER(key1) = UPPER(?)
});
$sth->execute($key1);
the idea is to make sure the case on both sides is the same. MySQL may
also have some sort of regular expression matching where you can ignore
case, but I'm not familiar enough with it to know definitely.
HTH.
Hardy Merrill
Now, I'll get to work on the placeholders, and see about the
other
issue. If the BINARY column modifier doesn't fix this, hopefully I can
come
back with some more useful information. Thanks again.
Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED]
Web: http:\\ldsa.sbcld.sbc.com
-----Original Message-----
From: Hardy Merrill [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 22, 2004 12:22 PM
To: [EMAIL PROTECTED]; NIPP, SCOTT V (SBCSI)
Subject: Re: Strange matching problem...
My comments below as HM>>. I'll repeat one general suggestion made
previously - use placeholders :)
>>> "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> 01/22/04 01:05PM >>>
I am working on a script to gather data about Unix user
accounts.
This is going well so far and has gotten us some initial data such as
almost
27000 user accounts across about 80 servers. I have built into the
script
and database some checking to track changes basically capture these
changes
in a historical table. I am running into at least two problems I am
currently unable to explain...
The first problem appears to be a matching problem with case
sensitivity. There are several instances on a couple of systems that
have
an account twice, once with the account name in all upper case and the
other
in all lower case. I am not sure exactly what/why this is occurring.
Any
help on this would be appreciated.
HM>> I don't see any matching logic in your code below, but here's a
general thought - instead of a string equality check ( if ($acct1 eq
$acct2) ), you could use a regular expression with the "i" (ignore
case
flag) something like ( if ($acct1 =~ /$acct2/i )
The other problem is an account with the same name on two
servers
that is being captured as changed every time. This is strange as I am
simply running the script repeatedly on the same dataset. Any ideas
on
this
would be most appreciated also.
HM>> Can't help you with this one - not enough info.
HTH.
Hardy Merrill
Below is the relevant sections of code...
while ($entry = <FILE>) {
($name, $passwd, $uid, $gid, $gcos, $home, $shell) =
split(/:/,$entry);
if ($uid > 100) {
$key1 = "$name"."-"."$host";
my $test = $dbh->prepare("SELECT * FROM acct_db WHERE key1 =
'$key1'");
$test->execute ();
$rows = $test->rows;
#print "Return value: $rows\n";
if ($rows == 0) {
$dbh->do("INSERT INTO acct_db
VALUES('$key1','$uid','$gid','$gcos','$home','$shell',NOW())")
or print "Error updating database: ", $dbh->errstr, "\n";
print "Adding $key1 to password database. \n";
} elsif ($rows == 1) {
my $test1 = $dbh->prepare("SELECT * FROM acct_db WHERE key1
=
'$key1' AND (uid != '$uid' OR gid != '$gid' OR gcos != '$gcos' OR home
!=
'$home' OR shell != '$shell')");
$test1->execute();
@old = $test1->fetchrow_array ();
if ($old[0]) {
print "$key1 requires updating in database. Updating
entry
now.\n";
# Insert existing data into acct_hist.
$dbh->do("INSERT INTO acct_hist (key1, uid, gid, gcos,
home,
shell, ent_time, arc_time)
VALUES
('$old[0]','$old[1]','$old[2]','$old[3]','$old[4]','$old[5]','$old[6]',NOW()
)");
# Delete existing data from acct_db.
$dbh->do("DELETE FROM acct_db WHERE key1 = '$key1'");
# Insert new entry into acct_db.
$dbh->do("INSERT INTO acct_db
VALUES('$key1','$uid','$gid','$gcos','$home','$shell',NOW())")
or print "Error updating database: ", $dbh->errstr, "\n";
} else {
#print "$key1 is up to date in database. No update
necessary.\n";
}
} else {
print "Error. \n";
}
}
}
The basic functionality of this script is working great. Data
is
definitely making it into the database, and testing changes works as
intended. The problems mentioned above are what I am trying to
rectify
at
this point. Any other suggestions on improvements or better methods
for
doing some of this stuff are most welcome too. Thanks in advance.
Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED]
Web: http:\\ldsa.sbcld.sbc.com
