OK... First of all, today, this afternoon, I am going to learn how
to use placeholders and get that fixed. I promise. :)
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.
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
