Access denied for user ''@'localhost' to database 'FltLogbook'

[HeedAV8]

I am new to PHP and MySQL, but not to coding in general.  I finally got brave and I am trying to move from a MS Access database to a server based MySQL database with a web enabled front end.  The goal of the database is to be online aviation logbook with the ability to enter/retrieve data with my iPhone.  My ultimate end state is to code my app for the phone.

 

I have exported all of my Access data to a MySQL database on server.

 

I've spent the last two weeks learning PHP and MySQL by writing small scripts and expanding as I learned.  I finally reached a point where I was ready to write the code for entering all of the data for a flight and inserting into the database.  I had mastered all of the sub-steps.

 

I built a form for entering the data.  The form querys the DB to populate some of the drop down menus.  All of that works fine and the drop menus are populated with information from the DB.

 

After I click on the "submit" button, the code throws an error.  I eventually learned how to capture and display the error.  I was getting the following: Access denied for user ''@'localhost' to database 'FltLogbook'.

 

Initially, I thought that maybe there was some sort of data type mismatch (I wasn't displaying the error initially, just a phrase stating the add failed) and I ended up commenting out the INSERT query and trying to insert just one piece (column) of data.  That didn't work.  Then I finally stumbled on the method to display the error.

 

After seeing the actual error statement, I thought that I was losing DB connectivity after pressing submit and executing the code or it may be a permission issue.  If I place the "INSERT" code up in the portion of the code where the form is built, I can successfully add a record.  So my username/password has the correct privileges since I can add a record using the code - just not where I want to do it.   

 

After googling, it looks like the code is passing a null value for user (and I assume password).  I have no idea why, but suspect it has something to do with the "POST" process after the submit.  Stumbling upon another function (connection_status), it passes a "0" for normal in both sections of the code.  I am truly stumped and have been this way for three days now!!  I have googled anonymous, the exact error phrase, etc.  None of it has helped me bridge this impasse.  I am sure, as in previous coding impasses, it will be quite obvious to someone.

 

Thanks for any help,

Heed

<?php

$conn = mysqli_connect("localhost", $_POST[user], $_POST[password], "FltLogbook");

if ($_POST[op] != "add") {
	//Form not shown, show it
	$display="<h1>Add Logbook Entry</h1>";
	$CnxSt = connection_status();
	$display .= "Connection Status: $CnxSt<br><br>";
	$display .= "
	<form method=\"post\" action=\"$_SERVER[PHP_SELF]\">
	<strong>Details:</strong>
	<table border=\"1\" width=\"520\">
	<tr>
		<td><input type=\"checkbox\" name=\"Sim\">Simulator</td>
		<td colspan=\"2\">SimType: <select name=\"SimType\">
		<option value=\"\">  NA </option>
		<option value=\"Miltary Visual\"> Military Visual </option>
		<option value=\"Military Non-Visual\"> Military Non-Visual </option>
		<option value=\"ATD\"> ATD </option>
		<option value=\"FTD\"> FTD </option>
		<option value=\"FFS\"> FFS </option></select></td>
	</tr>
	<tr>
		<td>Date: <input type=\"date\" name=\"FltDate\"></td>
		<td>Aircraft: <select name=\"Aircraft\">
		<option selected=\"selected\" value=\"26\">C-172M</option>";
		  		
		  $AcftQuery="SELECT Aircraft.AirKey,Aircraft.Aircraft, Aircraft.ME, Aircraft.Complex, Aircraft.HighPerf 
		  			  FROM Aircraft";
		  $AcftResult=mysqli_query($conn, $AcftQuery) or die ("Aircraft Query Failed");
		  While ($AcftRow=mysqli_fetch_array($AcftResult)) {
		    $AcftKey=$AcftRow['AirKey'];
		    $AcftAircraft=$AcftRow['Aircraft'];
		    $AcftME=$AcftRow['ME'];
		    $AcftComplex=$AcftRow['Complex'];
		    $AcftHP=$AcftRow['HighPerf'];
		    $display .= "<option value=\"$AcftKey\"> $AcftAircraft </option>";
		  }
		  
		$display .= "
		</select>
		</td>
		<td>Aircraft ID: <input type=\"text\" name=\"ID\" size=\"8\"></td>
	</tr>
	<tr>
		<td>From: <select name=\"From\">
		<option selected=\"selected\" value=\"PVG\">PVG</option>";
		
		  $ArfldQuery="SELECT AirfieldID FROM Airfields ORDER BY AirfieldID";
		  $ArfldResult=mysqli_query($conn, $ArfldQuery) or die ("Airfield Query Failed");
		  While ($ArfldRow=mysqli_fetch_array($ArfldResult)) {
		    $Arfld=$ArfldRow['AirfieldID'];
		    $display .= "<option value=\"$Arfld\"> $Arfld </option>";
		  }
		  
		$display .= "
		</select>
		</td>
		<td>To: <select name=\"To\">
		<option selected=\"selected\" value=\"PVG\">PVG</option>";

		  $ArfldQuery="SELECT AirfieldID FROM Airfields ORDER BY AirfieldID";
		  $ArfldResult=mysqli_query($conn, $ArfldQuery) or die ("Airfield Query Failed");
		  While ($ArfldRow=mysqli_fetch_array($ArfldResult)) {
  			$Arfld=$ArfldRow['AirfieldID'];
  			$display .= "<option value=\"$Arfld\"> $Arfld </option>";
		  }
  		  
		$display .= "
		</select>
		</td>
		<td>Leg: <select name=\"Leg\">
		  <option value=\"1\">1</option>
		  <option value=\"2\">2</option>
		  <option value=\"3\">3</option>
		  <option value=\"4\">4</option>
		  <option value=\"5\">5</option>
		  <option value=\"6\">6</option>
		  </select>
		</td>
	</tr>
	</table><br>
	<strong>Flight Time Info:</strong>
	<table border=\"1\" width=\"520\">
	<tr>
		<td>TPT: <input type=\"text\" name=\"TPT\" size=\"4\"></td>
		<td>PIC: <input type=\"text\" name=\"PIC\" size=\"4\"></td>
		<td>SIC: <input type=\"text\" name=\"SIC\" value=\"0.0\" size=\"4\"></td>
	</tr>
	<tr>
		<td>Solo: <input type=\"text\" name=\"Solo\" value=\"0.0\" size=\"4\"></td>
		<td colspan=\"2\">Dual Received: <input type=\"text\" name=\"Dual\" value=\"0.0\" size=\"4\"></td>
	</tr>
	<tr>
		<td>Cross Country: <input type=\"text\" name=\"XC\" value=\"0.0\" size=\"4\"></td>
		<td><input type=\"checkbox\" name=\"FARXCntry\" size=\"4\">FAR X-Cntry</td>
		<td><input type=\"checkbox\" name=\"ATPXCntry\" size=\"4\">ATP X-Cntry</td>
	</tr>
	<tr>
		<td>Single Engine: <input type=\"text\" name=\"SE\" size=\"4\"></td>
		<td>Multiengine: <input type=\"text\" name=\"ME\" size=\"4\"></td>
		<td>Night: <input type=\"text\" name=\"NT\" value=\"0.0\" size=\"4\"></td>
	</tr>
	<tr>
		<td>Complex: <input type=\"text\" name=\"Complex\" value=\"0.0\" size=\"4\"></td>
		<td colspan=\"2\">High Performance: <input type=\"text\" name=\"HP\" value=\"0.0\" size=\"4\"></td>
	</tr>
	<tr>
		<td>Instrument: <input type=\"text\" name=\"Inst\" value=\"0.0\" size=\"4\"></td>
		<td colspan=\"2\">Sim Instrument: <input type=\"text\" name=\"SimInst\" value=\"0.0\" size=\"4\"></td>
	</tr>
	<tr>
		<td>Instructor: <input type=\"text\" name=\"Instructor\" value=\"0.0\" size=\"4\"></td>
		<td>Pre-Post: <input type=\"text\" name=\"PrePost\" value=\"0.0\" size=\"4\"></td>
		<td>Ground: <input type=\"text\" name=\"Ground\" value=\"0.0\" size=\"4\"></td>
	</tr>
	</table><br>
	<strong>Actual Approaches:</strong>
	<table border=\"1\" width=\"520\">
	<tr>
		<td>Precision: <input type=\"text\" name=\"PrecAct\" value=\"0\" size=\"3\"></td>
		<td>Non-Precision: <input type=\"text\" name=\"NonPrecAct\" value=\"0\" size=\"3\"></td>
	</tr>
	</table><br>
	<strong>Simulated Approaches:</strong>
	<table border=\"1\" width=\"520\">
	<tr>
		<td>Precision: <input type=\"text\" name=\"PrecSim\" value=\"0\" size=\"3\"></td>
		<td>Non-Precision: <input type=\"text\" name=\"NonPrecSim\" value=\"0\" size=\"3\"></td>
	</tr>
	</table><br>
	<strong>Landings:</strong>
	<table border=\"1\" width=\"520\">
	<tr>
		<td>Day Landings: <input type=\"text\" name=\"DayLdg\" size=\"3\"></td>
		<td>Night Landings: <input type=\"text\" name=\"NtLdg\" size=\"3\"></td>
	</tr>
	</table><br>
	<strong>Remarks:</strong><br>
	<textarea name=\"Remarks\" rows=\"5\" cols=\"70\"></textarea>
	<input type=\"hidden\" name=\"op\" value=\"add\">
	<p><input type=\"submit\" name=\"submit\" value=\"Add Entry\"></p>
	</FORM>";
	
} else if ($_POST[op] == "add") {
	
	if ($_POST[Sim] == "") {
		$_POST[Sim] = "0";
	} else {
		$_POST[Sim] = "1";
	}
	
	if ($_POST[FARXCntry] == "") {
		$_POST[FARXCntry] = "0";
	} else {
		$_POST[FARXCntry] = "1";
	}
	
	if ($_POST[ATPXCntry] == "") {
		$_POST[ATPXCntry] = "0";
	} else {
		$_POST[ATPXCntry] = "1";
	}
	
//	$add_flt = "INSERT INTO FlightData (Date, MakeModel, AircraftID, Simulator, SimType, AfldFrom, AfldTo,
//				Leg, TPT, PIC, SIC, Solo, Dual, Night, XCntry, SE, ME, Complex, HighPerf, Inst_Act, Inst_Sim,
//				Instructor, PrePost, Ground, Prec_Act, Prec_Sim, NonPrec_Act, NonPrec_Sim, DCL, NCL, 
//				FAR_XCntry, ATP_XCntry, Remarks)
//				VALUES ('$_POST[FltDate]', '$_POST[Aircraft]', '$_POST[ID],$_POST[Sim]', '$_POST[SimType]', '$_POST[From]',
//						'$_POST[To]', '$_POST[Leg]', '$_POST[TPT]', '$_POST[PIC]', '$_POST[SIC]', '$_POST[Solo]', '$_POST[Dual]',
//						'$_POST[NT]', '$_POST[XC]', '$_POST[SE]', '$_POST[ME]', '$_POST[Complex]', '$_POST[HP]', '$_POST[Inst]',
//						'$_POST[SimInst]', '$_POST[Instructor]', '$_POST[PrePost]', '$_POST[Ground]', '$_POST[PrecAct]',
//						'$_POST[PrecSim]', '$_POST[NonPrecAct]', '$_POST[NonPreSim]', '$_POST[DayLdg]', '$_POST[NtLdg]',
//						'$_POST[FARXCntry]', '$_POST[ATPXCntry]', '$_POST[Remarks]')";

	$add_flt = "INSERT INTO FlightData (Date, MakeModel) VALUES ('$_POST[FltDate]', '$_POST[Aircraft]')";
	
	$result = mysqli_query($conn, $add_flt) or die('Error: '. mysqli_connect_error());
	
	$display = "<h1>Entry Added</h1>
	<p>Your entry was added.</p><br>
	<p>$_POST[FltDate] | $Make </p>";

//	$CnxSt = connection_status();
//	$display .= "Connection Status: $CnxSt<br><br>";

//	$display .= "$_POST[FltDate] / $_POST[Aircraft] / $_POST[ID] / $_POST[Sim] / $_POST[FARXCntry] / $_POST[ATPXCntry]";

}
	
?>
<html>
<head>
<title>Add Logbook Entry></title>
</head>
<body>
<?php echo $display; ?>
</body>
</html>

[mac_gyver]

your database connection details should only be known to your code and your database. you should not be using external submitted $_POST data as your database connection user and password. these values should exist only within your php code as either variables with assigned values or as defined constants.

[HeedAV8]

I am still trying to learn all of the security points, but at this point I am trying just to get the code running and I am connecting locally.

 

Do you have any idea why I can not connect and insert new data?

 

Thanks,

Heed

[Jacques1]

He just told you how to solve the problem: Hardcode the database credentials instead of waiting for the user to provide them (which makes absolutely no sense whatsoever; I wonder how you even got this idea).

 

Or are you saying you want to keep this very bug that's causing the trouble, somehow create a half-assed workaround for your local machine and finally throw everything away to write the real code? That sounds rather silly, don't you think? Why not write correct code from the beginning?

 

Besides that, I find it funny that so many programmers seem to view security as some kind of “bonus feature” which they can add at the very end (they never do). Programming doesn't work like this. Security is a fundamental part of writing code and shapes the structure of the program. You cannot “add it later”. If you write insecure code, you either end up with an insecure program, or you have to rewrite large parts of the code.

[HeedAV8]

Jacques1 - You're right.  I wanted to build in a security flaw, half-ass my way to a workaround and then start all over to put it on the web.  I sure am an idiot.  

 

I appreciate the help, but perhaps you could have delivered it without being so condescending.  If it wasn't obvious from my post above, I'M NEW to database/web programming.

 

Hardcoding solved my problem and it wasn't apparent from mac_gyver's post that was the root of my problem.  I thought he was pointing out a security flaw.

 

The idea for the code I wrote came from a library book and I've seen it on the web as an example as well.

 

So my next question would be how then do I secure the code so that only I can access the application from the web?  Hardcoding the username/pw works in my script, but obviously it is wide open.  Using mac_gyver's comments above, he seems to be indicating that I need to write one big script for the whole app (that way I don't need to pass user/pwd).  

 

I've seen quite a few code examples across the web that build separate PHP scripts to handle routine tasks.  How do you pass credentials without exposing a flaw?  Is that where sessions come in?  Or is that just bad security from the ground up?

 

Thanks, Heed

Edited May 9, 2014 by HeedAV8

[Clarkey]

Heed,

 

What usually happens is that you will have a config.php file which contains variables such as 

<?php
  $dbhost = "localhost";
  $dbuser = "dbusername";
  $dbpass = "dbpassword";
  $dbname = "mydatabase";
?>

Then, whenever you need to connect to the database in different files, you can use this include statment before your database connect function.

<?php include('config.php'); ?>

and then your variables will work in that file too.

 

 

Or, have I completely missed the point here?

[HeedAV8]

Thanks Clarkey.  That makes sense to me, I ran across the include statement troubleshooting my issue.  I'm guessing that would solve the problem of passing username/password through post.

 

I'm still stumped, but haven't read much yet, on the initial login entry point.  If I use the above and write the login script there, every time I use the "include" I am assuming I would have to reenter the username and password.

 

Since that doesn't happen with other sites, there is obviously a way to pass user credentials securely.  Since I solved my immediate problem with my first script module, I'm going to delve into the security side.  Although it was a snarkey post, Jacques1 had a valid point.  

 

Cheers!

[Ch0cu3r]

If you are allowing users to login to your site you don't use their login credentials as part of mysql_connect(). 

 

Instead you'd create a table in your database for storing the users profile, such as username, password, email address, etc. For logging in the user you'd create a script which will run a query and return the record that matches the users login credentials. If the query returns a matching record you'd then use sessions to create a logged in session variable. On any page you require the user to be logged you'd check for this session variable exists. If it does not exist you'd redirect the user to the login form.

 

This is the very basic mechanic any website uses for authenticating users.

[Clarkey]

Hmm, I'm a little confused...

 

Are you thinking that your visitors user / pass are passed to the mysql function?

 

If so, not at all. You have a special SQL user/pass that your website uses for SQL functions.

 

If you want a login system, then you have to create that, it's completely seperate.

Edited May 9, 2014 by Clarkey

[HeedAV8]

I guess I should be more clear. I will be the only user. I am using phpMyAdmin to administer the database. I created a username and password for the database and disabled all other accounts.

 

There won't be any public functions for the site and it has the sole purpose of being an electronic version of my flight logbook. Right now I use Access and own a Mac (used to have a PC). So I have to be at my computer, start the computer, start up windows on parallels, open Access and then enter my flight info or query for specific info. I looked at purchasing a Mac DB - too expensive and I still need to be at the computer. Ultimately, I want to be able to enter flight info right after the flight on my device. So I finally dove into MySQL.

 

Hopefully that clears up my database intentions.

 

I just need a way to provide that username/password from some sort of login method. What is clear now is that I need to read up on sessions.

 

I think what I may need is a login page that validates me as the user (perhaps through a table with one user & password; me) and then creating the "logged in" session. If that's the right way, then I guess I need to create a generic MySQL username and password that has the ability to connect (read only) and query the user table for validation.

 

Based on Ch0cu3r's comment about not passing credentials through mysql_connect(), I am slightly confused. Since the appropriate credentials need to be used to connect to the database, something needs to be in there, right? Or is there another way to provide MySQL with credentials?

 

Ch0cu3r - Did you mean a website's general login credentials vice SQL credentials? Another words, a user logs into a website that also happens to have database functionality with separate SQL permissions (usernames & passwords) in the database.

 

Thanks for all the responses. I think I am on the right path now and I just need to read up on what has been mentioned thus far.

 

Cheers!

[Jacques1]

The database users have nothing to do with the application users. Those are two entirely different things. For example, this forum has thousands of users, but of course there isn't a separate MySQL user for each one of us. That would be insane. We all connect to the forum database through the same MySQL user.

 

I think you should postpone the login stuff and concentrate on getting the database connection right. As Clarkey already said, you'll want a configuration script. This script simply contains all configuration data including the database credentials:

 

configuration.php

<?php

define('DATABASE_HOST', '...');
define('DATABASE_USER', '...');
define('DATABASE_PASSWORD', '...');
define('DATABASE_NAME', '...');

Then you'll want a database script which establishes a database connection using those credentials. Of course you could write this code into every single script, but this tedious and can lead to conflicting configurations. Better have one central script:

 

database.php

<?php

require_once __DIR__ . '/configuration.php';


// turn on exceptions in MySQLi driver
$database_driver = new mysqli_driver();
$database_driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

// connect to the database
$GLOBALS['database'] = new mysqli(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD, DATABASE_NAME);

// set character encoding
$GLOBALS['database']->set_charset('utf8');

And now you can simply include this database script in any file that needs a database connection. For example:

<?php

require_once __DIR__ . '/configuration.php';
require_once __DIR__ . '/database.php';


$test_query = $GLOBALS['database']->query('SELECT 1');
$test_result = $test_query->fetch_assoc();

var_dump($test_result);

When you got this running, you can move on to the login system.

Edited May 9, 2014 by Jacques1

[HeedAV8]

Thanks All!  My original issue with the connection has been solved and I have plenty to research on configuration scripts and sessions.

 

Jacques1 - your first paragraph is what I was trying to say, you said it better.  I thought that is what Ch0cu3r was trying to say when he stated don't pass user credentials in the mysql_connect().  Mac_gyver's post makes sense to me now as well.

 

I was looking at this problem knowing I was the sole user of my database, but I didn't share that initially.  The responders were looking at this as a multiple user application.  I think that is why I had such a hard time understanding the first couple of posts from the responders.  If I were building this as a multi-user application, I wouldn't also create numerous MySQL users; too tedious to maintain as you stated.  I assume their are at least three basic forum MySQL users; User, Moderator and Administrator.  The code assigns your level when you login and makes the appropriate connection to the database.

 

Thanks for the code examples above!

 

Cheers,

Heed