List:MySQL and PHP« Previous MessageNext Message »
From:<will Date:March 29 2007 8:34pm
Subject:Update- Strange results when Inserting Passwords into MYSQL Database
View as plain text  
It turns out that the last un & pw inserted into `live` from `staging`,  in my example
the 10th row, will automatically  allow access to the site while the first 9 un & pw
rows inserted into `live` will not allow access.

Oh, and I apologize for the last line being in All Caps.  I was told that's improper
etiquete..

Original Email below- 

Hello

Following is the process I am doing to insert usernames and passwords into a MYSQL
database.
The username and passwords are used to log into our website by people who have purchased
access.

1.  I generate 10 username and password combinations using a password generator
2.  I export the generated un & pw to a csv file on my local hard drive, called
infile.csv.
3.  I upload  infile.csv to our dedicated server hosted by an outside company.
4.  I manually execute a php file called Insert.php which Inserts the un & pw into
"staging" table in MYSQL.
Here is the code:
<?php

/**
 * Reads in the Diplodeck Username/Password file
 * Converts and adds data to match the columns in codes_staging
 * After the codes_staging table is reviewed it will be 
 * manaully inserted into the codes table.
 */

 
/* Open file */
 $infile = file("infile.csv");


 
/* Read in each record */ 
 foreach ($infile as $record) {
  list($password, $username) = explode(",", $record);

/* Create All the columns to insert a record into codes_staging */  
/* Replace - in username with z */
        $login = str_replace ("-", "z", $username);
    
/* Convert password to hash password */
  $password = md5($password);
/* Hardcode $type 24H, 3D, 10D, or 30D */
  $type = "30D";
/* Hardcode $serialno this is always equal to the last 4 digits of the password.  This
ensures no username/pw combos will ever be recreated */
  $serialno = "MNTK";
/* The remaining column variables are always the same.  No modifications are required for
them */
  $startdate = 0;
  $logged = 0;
  $ip = 0;
  $ipcounter = 0;
  $active = 1;
  $passhash = $password;
  $pass = " ";
  
  mysql_connect("localhost", "xxxxxxxx", "pwpwpwpwpw") or die(mysql_error());
  mysql_select_db("database_database") or die(mysql_error());
  
  
    mysql_query("INSERT INTO staging 
    (login, password, type, startdate, logged, serialno, ip, ipcounter, active, passhash,
pass)  
    VALUES
   
('".$login."','".$password."','".$type."','".$startdate."','".$logged."','".$serialno."',

     '".$ip."','".$ipcounter."','".$active."','".$passhash."','".$pass."' ) ")
     or die(mysql_error());
   
  mysql_close();
     
/* Test Check  uncomment code when testing. Comment out when running live */
  echo $login," ",$password," ",$type," ",$stardate," ",$logged," ",$serialno," ",$ip,"
",$ipcounter," "   ,$ipcounter," ",$active," ",$passhash," ",$pass,"<br>"; 
  
  
 }
 
 fclose($infile);
 

 
?>

5.  I verify the data is there and correct in the `staging` table.  It always is....
6.  I manually run the following command in the MYSQL SQL window to get the new un &
pw combinations into the "Live" table
INSERT INTO `live`
(SELECT * FROM 'staging`)

7. The new un & pw combinations should now allow access to our site....It doesn't.
What's strange is if I select the edit pencil next to the un & pw row in the `live`
table and NOT EDIT ANYTHING and then hit the GO button, the same un & pw that
wouldn't allow access, now does.  I didn't change anything!!

-Also if I use Insert.php to insert 1 un & pw at a time the un & pw will grant
access once it's been inserted into the `live` table.  

I plan on inserting thousands of rows at a time and it's not logical to physically edit
each row or run Insert.php thousands of time.

IF ANYONE KNOWS HOW TO RESOLVE THIS, IT WILL BE GREATLY APPRECIATED?
Thread
Update- Strange results when Inserting Passwords into MYSQL Databasewill29 Mar