Thanks to everyone for the feedback. I have modified the script to fix the NOW function. 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 2:11 PM To: [EMAIL PROTECTED]; NIPP, SCOTT V (SBCSI) Subject: RE: Strange matching problem... You really need to read 'perldoc DBI' (at a command prompt) and pay attention to how placeholders are used. See below. As Dave Murray suggested, the Programming the Perl DBI book would be a good investment. >>> "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> 01/22/04 02:52PM >>> 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"; HM@@ I don't think(?) you can use placeholders with the "do" command, but I'm really not sure. I think you have to put the "?" placeholders in the "prepare", and then do an "execute" and it's in the execute where you specify the values for those placeholders, like this: $sth = $dbh->prepare(qq{ INSERT INTO acct_db VALUES(?,?,?,?,?,?,NOW()) }) or print "Error with INSERT _prepare_: $DBI::errstr\n"; $sth->execute(undef,$key1,$uid,$gid,$gcos,$home,$shell) or print "Error with INSERT _execute_: $DBI::errstr\n"; See if that works. Hardy Merrill 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
