List:MySQL and PHP« Previous MessageNext Message »
From:<will Date:March 29 2007 6:33pm
Subject:Strange results when Inserting Passwords into MYSQL Database
View as plain text  
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
Strange results when Inserting Passwords into MYSQL Databasewill29 Mar