Programming corner wildboy85: mysql php login and password authentication

Hello,

2016-03-07 Noticed that is the page reload, the session variables are reset. I must initiate session on page reload. Code will change soon to include a basic code inside main loop.

2015-08-09 completly changed the mysql database and data creation to use a function and dynamic arrays

My last post about login and password in PHP was really short

So i added some MYSQL

Mysql is a database engine
In this program we will initialise variables at the start (before login)
Then we will create a database (siteweb01) if it does not exist
Then we will create a table (users01) if it does not exist
Then we will create columns (fields) for this table

Important: the column name will all have the same name as the arrays that contain the logins, passwords, and fullname (and securitylevel)

One column will be "id" wich name will never change, is a primary key, and is autoincrement
We never update the field "id"
An "id" field is mandatory for a table (in theory)

One column will become a primary key column, and will contain the loginname (we do not want the same loginname for 2 persons)

I also added a session variable just in case someone refresh the page. This will prevent the login from being posted twice for no reason.

Be aware that some browsers remember logins and passwords and switch password automatically if there is more than one in memory and if a password fail.

Also, the array containing the loginnames etc. is accessed using dynamic code
(dynamic code = executing some php code inside a variable with eval)
This is because i did not want my array to be only 2 dimensionnals array with numbers
First dimension would have been the users (1 dimension for each user)
Second dimension would have been: loginname, fullname, password etc. (0, 1, 2 etc.)
I did not want "fullname" to be array dimension 0
I wanted my array to have nice names

Debugmode can be put to 0 if you dont want all the trash text in the web page

This code is meant as a model, so error trapping is everywhere.

Requirements:
So the requirement for this is still WAMP, wich include a apache web server and mysql service

If your wamp icon is yellow, only the web part may be active, not the mysql service
You must install and activate the mysql service (in the wamp icon menu)

Create this file with notepad.exe
Save this in c:\wamp\www

---------------------- index.php ---------------------------------
<!DOCTYPE html>

<html:html>
<html:body>

<html>
<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /><div>

<title>Login and password in PHP</title>

</head>

<body>

<?php
// debug mode is ON, lots of echo will display message about working order of the page
$debugmode = 1;
session_start();

header("Cache-Control: private, max-age=10800, pre-check=10800");

if(!isset($_COOKIE["PHPSESSID"]))
{
// first load of page
// initialize session variables
$_SESSION['loginok'] = 0;
$_SESSION['securitylevel'] = 0;
$_SESSION['username'] = "";
$_SESSION['fullname'] = "Guest";
$_SESSION['logindone'] = 0;

if($debugmode==1) {echo "SESSION variables initialized ";};

// second load of page or more
if($debugmode==1) {echo "Not first load, no global variables initializing ";};
//if($debugmode==1) {echo "Session logindone: " . $_SESSION['logindone']." ";};
};

if(!isset($userstot))
{
// initialize program variables (in this sub, not public to functions or sub, not shared to any other program
if($debugmode==1) {echo "Variables initialized ";};

// mysql database
$mysqldbname = "siteweb01";
// mysql users table

$mysqltablename = "users01";

// used to read user and authenticate
$mysqluserstable = "users01";

// mysql fields to create in table
// struct equivalent to be able to pass one array as parameter to create the sql table with the data
$i = 0;
$mysqlfields[$i]['fieldname'] = "username";
$mysqlfields[$i]['type'] = "VARCHAR(50)";
$mysqlfields[$i]['primarykey'] = 1; // 1 = this will be a primary key column, 0 = not
$mysqlfields[$i]['autoincrement'] = 0;
$i++;
$mysqlfields[$i]['fieldname'] = "password";
$mysqlfields[$i]['type'] = "VARCHAR(50)";
$mysqlfields[$i]['primarykey'] = 0; // 1 = this will be a primary key column, 0 = not
$mysqlfields[$i]['autoincrement'] = 0;
$i++;
$mysqlfields[$i]['fieldname'] = "fullname";
$mysqlfields[$i]['type'] = "VARCHAR(255)";
$mysqlfields[$i]['primarykey'] = 0; // 1 = this will be a primary key column, 0 = not
$mysqlfields[$i]['autoincrement'] = 0;
$i++;
$mysqlfields[$i]['fieldname'] = "securitylevel";
$mysqlfields[$i]['type'] = "VARCHAR(50)";
$mysqlfields[$i]['primarykey'] = 0; // 1 = this will be a primary key column, 0 = not
$mysqlfields[$i]['autoincrement'] = 0;
$i++;
$mysqlfields[$i]['fieldname'] = "id"; // there must ALWAYS be an ID column (primary key automatically, name in lowercase)
$mysqlfields[$i]['type'] = ""; // type does not matter as this is altered to be a primary key
$mysqlfields[$i]['primarykey'] = 1; // 1 = this will be a primary key column, 0 = not
$mysqlfields[$i]['autoincrement'] = 1; // a id column is always autoincrement

// struct
$i = 0;
$mysqldatas[$i]['username'] = "admin";
$mysqldatas[$i]['password'] = "admin";
$mysqldatas[$i]['fullname'] = "Serge Fournier";
$mysqldatas[$i]['securitylevel'] = 100;
$i++;
$mysqldatas[$i]['username'] = "admin2";
$mysqldatas[$i]['password'] = "admin2";
$mysqldatas[$i]['fullname'] = "Serge Fournier 2";
$mysqldatas[$i]['securitylevel'] = 50;

$userstot = count($mysqldatas); // total users would be 2

// create a mysql database with the login array

// connect to mysql server locally @ = no php error if it fail
$con01 = @mysqli_connect("localhost","root","","");
if(mysqli_connect_error()==null)
{
// call mysql db, table, field creation and insertion or update if primary key exist
$dummy = makemysql($con01, $mysqldbname, $mysqltablename, $mysqlfields, $mysqldatas);
}
else
{
echo "ERROR mysqli_connect: " . mysqli_connect_error()." ";
unset($con01);
};

// end of creation of database, creation of table, creation of columns(fields), insertion/update of all line from an array for users table

// now, this mysql table was initially from an array
// but now we will be able, later, to add users from the web page itself in the mysql database (new inscriptions)

$errorlogin = "";

}

if(isset($_POST['submitlogin']) and $_SESSION['logindone'] == 0)
{
//if(empty($_POST['username'])){$_SESSION['loginok'] = 0;};
//if(empty($_POST['password'])){$_SESSION['loginok'] = 0;};

$usernameresult = trim($_POST['username']);
$passwordresult = trim($_POST['password']);
$usercnt = 0;
if($debugmode==1) {echo "Total number of users: $userstot ";};

// MYSQL validation of login and password (in database we created with the array before we got here)
if(isset($con01))
{
if (mysqli_select_db($con01, $mysqldbname))
{
if ($result01 = mysqli_query($con01,"SELECT * FROM $mysqluserstable"))
{
// connect, db, and query are ok
while ($row = $result01->fetch_array())
{
// list all login and passwords in the sql database
// echo ' line: '.$row["username"].' '.$row["password"].' '.$row["securitylevel"].' ';
if($usernameresult == $row["username"])
{
if($debugmode==1) {echo "Username valid, cheking password... ";};
if($passwordresult == $row["password"])
{
$_SESSION['loginok'] = 1;
$_SESSION['fullname'] = $row["fullname"];
$_SESSION['username'] = $row["username"];
$_SESSION['securitylevel'] = $row["securitylevel"];
// if($debugmode==1) {echo "session value (1 is login and password ok, 0 is bad something): " . $_SESSION['loginok']." ";};
$_SESSION['logindone'] = 1;
if($debugmode==1) {echo "Session logindone: " . $_SESSION['logindone']." Session loginok: ".$_SESSION['loginok']." ";};
};
}; //if($usernameresult == $row["username"])
}; // while

mysqli_free_result($result01);
}
else
{
// query sucess
echo "ERROR mysql query returned an error ";
echo("ERROR mysqli_query: ".mysqli_errno($con01)." ".mysqli_error($con01)." ");
}; //mysqli_query
}
else
{
echo "ERROR mysql database selection did not work ";
echo("ERROR mysqli_select_db: " . mysqli_errno($con01)." ".mysqli_error($con01)." ");
}; // mysqli_select_db

}
else
{
echo "ERROR mysql connection does not exist: con01 ";
}; // mysqli_connect

/* // array validation of login and password
foreach($username as $usernameloop)
{
if($usernameresult == $usernameloop)
{
if($debugmode==1) {echo "Username valid, cheking password... ";};
if($passwordresult == $password[$usercnt])
{
$_SESSION['loginok'] = 1;
$_SESSION['fullname'] = $fullname[$usercnt];
$_SESSION['username'] = $username[$usercnt];
$_SESSION['securitylevel'] = $securitylevel[$usercnt];
if($debugmode==1) {echo "session value (1 is login and password ok, 0 is bad something): " . $_SESSION['loginok']." ";};
$_SESSION['logindone'] = 1;
if($debugmode==1) {echo "Session logindone (submitlogin): " . $_SESSION['logindone']." ";};
};
};
$usercnt = $usercnt + 1;
};
*/
// is login and password ok? if not set an error message for next page reload
if($_SESSION['loginok'] == 1)
{
$errorlogin = "";
}
else
{
$errorlogin = "ERROR Wrong login or password. This is case sensitive. Chek your caps lock state";
};
};

if(isset($_POST['submitlogoff']))
{
$_SESSION['loginok'] = 0;
$_SESSION['logindone'] = 0;

// close mysql connection
if(isset($con01))
{
mysqli_close($con01);
unset($con01);
};

if($debugmode==1) {echo "Session logindone (submitlogoff): " . $_SESSION['logindone']." ";};
};

if($_SESSION['loginok'] == 1)
{
// second load of page (this is a view refresh)

// main program ///////////////////////////////////////////////

echo "<h1>Main program</h1> ";

echo " Welcome ".$_SESSION['fullname']." ";
echo " Your username is: ".$_SESSION['username']." ";
echo " Your security level is: ".$_SESSION['securitylevel']." ";

// mysql connexion (for verification of list of users and password)
if(isset($con01))
{
if (mysqli_select_db($con01, $mysqldbname))
{

// the row will be in an array
// we will build query dynamically with all rows
// so we can use the same array to write to database

if ($result01 = mysqli_query($con01,"SELECT * FROM $mysqluserstable"))
{
// connect, db, and query are ok
while ($row = $result01->fetch_array())
{
// list all login and passwords in the sql database

// activate if you want to verify something
// echo ' line: '.$row["username"].' '.$row["password"].' '.$row["securitylevel"].' ';
};
mysqli_free_result($result01);
}
else
{
// query sucess
echo "ERROR mysql query returned an error ";
echo("ERROR mysqli_query: " . mysqli_errno($con01));
}; //mysqli_query
}
else
{
echo "ERROR mysql database selection did not work ";
echo("ERROR mysqli_select_db: " . mysqli_errno($con01)." ");
}; // mysqli_select_db

}
else
{
echo "ERROR mysql connection does not exist: con01 ";
}; // mysqli_connect

// logoff button
$html = "";
$html.=" <form id='logoff' action='{$_SERVER['PHP_SELF']}' method='post' accept-charset='UTF-8'>";
$html.="<fieldset>";
$html.="<legend>Logoff</legend>";
$html.="<input type='submit' name='submitlogoff' value='Logoff' />";
$html.="</fieldset>";
$html.="</form>";

echo $html;
ob_flush(); // empty any web browser buffer so text display immediatly
flush();

}
else
{
// login form, username and password box
if($debugmode==1) {echo "Session loginok value: " . $_SESSION['loginok'] . " (0 = you are not logged in) ";};

$html="<form id='login' action='{$_SERVER['PHP_SELF']}' method='post' accept-charset='UTF-8'>";
$html.="<fieldset>";
$html.="<legend>Login</legend>";
$html.="<label for='username' >UserName*:</label>";
$html.="<input type='text' name='username' id='username' maxlength='50' />";
$html.="<label for='password' >Password*:</label>";
$html.="<input type='password' name='password' id='password' maxlength='50' />";
$html.="<input type='submit' name='submitlogin' value='Login' /> $errorlogin";
$html.="</fieldset>";
$html.="</form>";

echo $html;
ob_flush(); // empty any web browser buffer so text display immediatly
flush();
};

///////////////////////////////////////////////////////////////////////////////////

function makemysql($con01, $mysqldbname, $mysqltablename, $mysqlfields, $mysqldatas)
{
// this function will create a mysqldatabase and upinsert data in it
// insert if data do not exist
// update if data primary key exist (there must be a primary key field, like "username"

// reference
// $mysqlfields[$i]['fieldname'] = "fullname";
// $mysqlfields[$i]['type'] = "VARCHAR(255)";
// $mysqlfields[$i]['primarykey'] = 0; // 1 = this will be a primary key column, 0 = not
// $mysqlfields[$i]['autoincrement'] = 0;

global $debugmode;

if($debugmode==1) {echo " Function to create mysql database, table, fields, upinsert data ";};

// does database exist?
if (!mysqli_select_db($con01, $mysqldbname))
{
echo("Creating database: $mysqldbname ");
$query = "CREATE DATABASE $mysqldbname";
mysqli_query($con01,$query);
$query = "ALTER DATABASE `$mysqldbname` CHARACTER SET utf8 COLLATE utf8_general_ci";
mysqli_query($con01,$query);
}
else
{
// database already exist
if($debugmode==1) {echo "MYSQL database $mysqldbname already exist ";};
};

// selecting database
if (mysqli_select_db($con01, $mysqldbname))
{
// database exist

// checking if table exist
$query ="SELECT * FROM $mysqltablename limit 1";
if (!$result01 = mysqli_query($con01, $query))
{
if($debugmode==1) {echo "ERROR mysql table does not exist or no answer: $query ";};
// creating table
$query = "CREATE TABLE `$mysqldbname`.`$mysqltablename`( `".$mysqlfields[0]['fieldname']."` ".$mysqlfields[0]['type'].")";
if($debugmode==1) {echo "MYSQL creating table: $query ";};
$result01 = mysqli_query($con01,$query);
}
else
{
if($debugmode==1) {echo "MYSQL Table responded: $query ";};
};
$query ="SELECT * FROM $mysqltablename limit 1";
if ($result01 = mysqli_query($con01,$query))
{
$columnscnt = 0;
foreach($mysqlfields as $mysqlfield)
{
//$columnname = $mysqlfield['fieldname'];

if (!$result01 = mysqli_query($con01,"SELECT ".$mysqlfield['fieldname']." FROM $mysqltablename limit 1"))
{
if($debugmode==1) {echo "MYSQL Column does not exist: ".$mysqlfield['fieldname']." ";};
// creating column
$query = "ALTER TABLE `$mysqldbname`.`$mysqltablename` ADD COLUMN `".$mysqlfield['fieldname']."` ".$mysqlfield['type'];
// NULL AFTER 'fullname'"

// ID
if($mysqlfield['fieldname'] == "id")
{
$query = "ALTER TABLE `$mysqldbname`.`$mysqltablename` ADD COLUMN `id` INT NULL AUTO_INCREMENT, ADD KEY(`id`);";
};

if (!$result01 = mysqli_query($con01,$query))
{
echo "ERROR mysql altering table, inserting column: ".$mysqlfield['fieldname']." ";
echo "ERROR mysql altering table, inserting column: $query ";
}
else
{
if($debugmode==1) {echo "MYSQL Column created: $query ";};
};
}
else
{
if($debugmode==1) {echo "MYSQL Column ok: ".$mysqlfield['fieldname']." ";};
};
$columnscnt = $columnscnt + 1;
};

// add primary key for some columns
$columnscnt = 0;
foreach($mysqlfields as $mysqlfield)
{
//if($debugmode==1) {echo ($mysqlprimarykey[$columnscnt] == 1 and $columnname !== "id")." ";};

// add primary key if this column require it. NOTE: if this is added after column wa created, it wont execute because column already exist
if($mysqlfield['primarykey'] == 1 and $mysqlfield['fieldname'] !== "id") // Id column is exempted as it is created as non null autoincrement already
{
$query = "ALTER TABLE `$mysqldbname`.`$mysqltablename` CHANGE `".$mysqlfield['fieldname']."` `".$mysqlfield['fieldname']."` ".$mysqlfield['type']." ";
$query.="CHARSET utf8 COLLATE utf8_general_ci NOT NULL, ADD PRIMARY KEY (`".$mysqlfield['fieldname']."`);";
if (!$result01 = mysqli_query($con01,$query))
{
if($debugmode==1) {echo "ERROR MYSQL column defined as primary key: $query ";};
if($debugmode==1) {echo "ERROR ".mysqli_errno($con01)." ".mysqli_error($con01)." ";};
// Multiple primary key defined (when we redo the same primary key)
}
else
{
if($debugmode==1) {echo "MYSQL column defined as primary key: ".$mysqlfield['fieldname']." ";};
};
};
$columnscnt = $columnscnt + 1;
};

// verification if creation of a column worked
$columnscnt = 0;
$fatalerror = 0;
foreach($mysqlfields as $mysqlfield)
{
if (!$result01 = mysqli_query($con01,"SELECT ".$mysqlfield['fieldname']." FROM $mysqltablename limit 1"))
{
if($debugmode==1) {echo "ERROR MYSQL Column does not exist after we tried to create it: ".$mysqlfield['fieldname']." ";};
$fatalerror = $fatalerror + 1;
};
$columnscnt = $columnscnt + 1;
}

if($fatalerror == 0)
{
if($debugmode==1) {echo "MYSQL Column existence verified. missing: $fatalerror ";};
// all columns exist (we did not check their type tough)
if($debugmode==1) {echo "MYSQL all columns ok, database ok, table ok ";};

///////////////////////////////////////////////////////////
// insert stuff (array) in database/table if it does not exist (merge or upinsert)
///////////////////////////////////////////////////////////

// upinsert all elements from array in the database
$line = 0;
foreach($mysqldatas as $mysqldata)
{
$querypart1 ="insert into `$mysqltablename` (";
$querypart2 =") values (";
$querypart3 =") ON DUPLICATE KEY UPDATE ";

$columnscnt = 0;
$columnmax = count($mysqlfields);

if($debugmode==1) {echo "Inserting a line with ".$columnmax." columns. Line: ".$line." ";};

foreach($mysqlfields as $mysqlfield)
{
// DO NOT INCLUDE ID IN a merge (also called upinsert or insert on duplicate key update in mysql)
if ($mysqlfield['fieldname']!= "id")
{
$querypart1.="`".$mysqlfield['fieldname']."`";

// instead of having only number array
// i use eval to get the name of the array that contain each column value we want
// the name of the column in mysql is the same name as the array that contain the value we want

// test string (to see what i do with all the \ to be able to use " inside the eval
$test="";
//$test = "\$querypart2.=$mysqldata[$mysqlfield['fieldname']];
//if($debugmode==1) {echo($columnscnt." ".$test." ");};
//if($debugmode==1) {echo("data: ".$mysqldata[$mysqlfield['fieldname']]." ");};

// dynamic code. all this code is replaced by the eval
//if($columnscnt==0) {$querypart2.="'".$username[$line]."'";};
//if($columnscnt==1) {$querypart2.="'".$password[$line]."'";};
//if($columnscnt==2) {$querypart2.="'".$fullname[$line]."'";};
//if($columnscnt==3) {$querypart2.=$securitylevel[$line];};
//eval("\$querypart2.=\"'\".$".$columnname."[\$line].\"'\";");
$querypart2.="'".$mysqldata[$mysqlfield['fieldname']]."'";

// the value mysql need when the insert become an update
// when the line we want to insert have a primary key identical to what we try to insert
// in this case, username is defined as a primary key in mysql when we create the column in precedent lines
$querypart3.=$mysqlfield['fieldname']."=values(".$mysqlfield['fieldname'].")";

// id=LAST_INSERT_ID(id)

if($columnscnt!=$columnmax-1)
{
if($mysqlfields[$columnscnt + 1]['fieldname'] != "id")
{
$querypart1.=",";
$querypart2.=",";
$querypart3.=",";
};
};
}; // ($columnname != "id")
$columnscnt = $columnscnt + 1;
}; // for each columnname
if($debugmode==1) {echo "MYSQL insert on duplicate key update: ".$querypart1.$querypart2.$querypart3." ";};
// ON DUPLICATE UPDATE b = VALUES(b), c = VALUES(c)

$query = $querypart1.$querypart2.$querypart3;
//"INSERT INTO `$mysqldbname`.`$mysqltablename` (`username`, `password`, `securitylevel`, `fullname`) VALUES ('admin', 'admin', '100', 'Serge Fournier');";
if ($result01 = mysqli_query($con01,$query))
{
if($debugmode==1) {echo "MYSQL insert on duplicate key update success ";};
}
else
{
if($debugmode==1) {echo "ERROR MYSQL insert on duplicate key update ";};
};
$line++;
}; // for $line
}
else
{
echo "ERROR mysql $fatalerror column is missing after we tried to create all columns, at verification of it's existence ";

}; // if fatalerror
}
else
{
echo "ERROR mysql table does not exist after we tried to create it: $mysqltablename ";
}; //if ($result01 = mysqli_query($con01,$query))

}
else
{

echo "ERROR mysql we tried to create database ";
echo "ERROR mysql after trying to create database, mysqli_select_db did not work ";
echo("ERROR mysqli_select_db: " . mysqli_errno($con01)." ");
}; //if (mysqli_select_db($con01, $mysqldbname))
if($debugmode==1) {echo " ";};
};

?>

</html>
</body>
</html:body>
</html:html>

Programming corner wildboy85

Sunday, August 9, 2015

mysql php login and password authentication

No comments:

Post a Comment