wolocaw Posted May 22, 2012 Share Posted May 22, 2012 Hello. Through MySQL, I created a database using the following code: create database auth; use auth; create table authorized_users ( name varchar(20), password varchar(40), primary key (name) ); insert into authorized_users values ( 'username', 'password' ); insert into authorized_users values ( 'testuser', sha1('password') ); grant select on auth.* to 'webauth' identified by 'webauth'; flush privileges; Then I used the following code to set up a simple log in page. <?php $name = $_POST['name']; $password = $_POST['password']; if ((!isset($name)) || (!isset($password))) { //Visitor needs to enter a name and password ?> <h1>Please Log In</h1> <p>This page is secret.</p> <form method="post" action="secretdb.php"> <p>Username: <input type="text" name="name"></p> <p>Password: <input type="password" name="password"></p> <p><input type="submit" name="submit" value="Log In"></p> </form> <?php } else { // connect to mysql $mysql = mysqli_connect("localhost", "webauth", "webauth"); if(!$mysql) { echo "Cannot connect to database."; exit; } // select the appropriate database $selected = mysqli_select_db($mysql, "auth"); if(!$selected) { echo "Cannot select database."; exit; } // query the database to see if there is a record which matches $query = "select count(*) from authorized_users where name = '".$name."' and password = sha1('".$password."')"; $result = mysqli_query($mysql, $query); if(!$result) { echo "Cannot run query."; exit; } $row = mysqli_fetch_row($result); $count = $row[0]; if ($count > 0) { // visitor's name and password combination are correct echo "<h1>Here it is!</h1> <p>I bet you are glad you can see this secret page.</p>"; } else { // visitor's name and password combination are not correct echo "<h1>Go Away!</h1> <p>You are not authorized to use this resource.</p>"; } } ?> When I open up this page, above "Please Log In", I get this error: Notice: Undefined index: name in C:\xampp\htdocs\learning\secretdb.php on line 2 Notice: Undefined index: password in C:\xampp\htdocs\learning\secretdb.php on line 3 Moreover, when I type in an authorized username and password, I get another error: Warning: mysqli_connect() [function.mysqli-connect]: (28000/1045): Access denied for user 'webauth'@'localhost' (using password: YES) in C:\xampp\htdocs\learning\secretdb.php on line 19 Cannot connect to database. Does anyone know what I'm doing wrong? I'm fairly new to PHP and MySQL and I'm pretty stuck. Thanks. Quote Link to comment Share on other sites More sharing options...
darkfreaks Posted May 22, 2012 Share Posted May 22, 2012 for your MYSQL error you need to make sure your connection info matches up including db_name password and username. and make sure you use localhost, if your hosting uses a different MYSQL server might want to check there too. also make sure you are putting your post variables after you check if there empty or isset Quote Link to comment Share on other sites More sharing options...
wolocaw Posted May 22, 2012 Author Share Posted May 22, 2012 I think my second error might be occurring because of the first error. Where exactly should I put my post variables? I tried putting them in a couple different places but I still get the same error. For example, I still get the same error, just at different lines, when I change the code to: <?php if ((!isset($name)) || (!isset($password))) { //Visitor needs to enter a name and password ?> <h1>Please Log In</h1> <p>This page is secret.</p> <form method="post" action="secretdb.php"> <p>Username: <input type="text" name="name"></p> <p>Password: <input type="password" name="password"></p> <p><input type="submit" name="submit" value="Log In"></p> </form> <?php $name = $_POST['name']; $password = $_POST['password']; } else { // connect to mysql $mysql = mysqli_connect("localhost", "webauth", "webauth"); if(!$mysql) { echo "Cannot connect to database."; exit; } // select the appropriate database $selected = mysqli_select_db($mysql, "auth"); if(!$selected) { echo "Cannot select database."; exit; } // query the database to see if there is a record which matches $query = "select count(*) from authorized_users where name = '".$name."' and password = sha1('".$password."')"; $result = mysqli_query($mysql, $query); if(!$result) { echo "Cannot run query."; exit; } $row = mysqli_fetch_row($result); $count = $row[0]; if ($count > 0) { // visitor's name and password combination are correct echo "<h1>Here it is!</h1> <p>I bet you are glad you can see this secret page.</p>"; } else { // visitor's name and password combination are not correct echo "<h1>Go Away!</h1> <p>You are not authorized to use this resource.</p>"; } } ?> Quote Link to comment Share on other sites More sharing options...
Carbon Posted May 22, 2012 Share Posted May 22, 2012 You are setting $name and $password even when the post variables may not be set, you need to put them after the else: <?php if ((!isset($name)) || (!isset($password))) { //Visitor needs to enter a name and password ?> <h1>Please Log In</h1> <p>This page is secret.</p> <form method="post" action="secretdb.php"> <p>Username: <input type="text" name="name"></p> <p>Password: <input type="password" name="password"></p> <p><input type="submit" name="submit" value="Log In"></p> </form> <?php } else { $name = $_POST['name']; $password = $_POST['password']; // connect to mysql $mysql = mysqli_connect("localhost", "webauth", "webauth"); if(!$mysql) { echo "Cannot connect to database."; exit; } // select the appropriate database $selected = mysqli_select_db($mysql, "auth"); if(!$selected) { echo "Cannot select database."; exit; } // query the database to see if there is a record which matches $query = "select count(*) from authorized_users where name = '".$name."' and password = sha1('".$password."')"; $result = mysqli_query($mysql, $query); if(!$result) { echo "Cannot run query."; exit; } $row = mysqli_fetch_row($result); $count = $row[0]; if ($count > 0) { // visitor's name and password combination are correct echo "<h1>Here it is!</h1> <p>I bet you are glad you can see this secret page.</p>"; } else { // visitor's name and password combination are not correct echo "<h1>Go Away!</h1> <p>You are not authorized to use this resource.</p>"; } } ?> You should also be careful of sql injection there, you will want to use mysql_real_escape_string() around both of the user inputs, or you could look into PDO prepared statements, http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/ For your second problem, make sure that your credentials are correct for your database, if they are then try mysql_connect(), not mysqli_connect(). Quote Link to comment Share on other sites More sharing options...
wolocaw Posted May 22, 2012 Author Share Posted May 22, 2012 Okay. I got it to work by manually creating a new user "webauth" using localhost and password "webauth" and giving it SELECT privilege through phpMyAdmin. This is also the php code that I used: <?php if (!isset($_POST['name']) || !isset($_POST['password'])) { //Visitor needs to enter a name and password ?> <h1>Please Log In</h1> <p>This page is secret.</p> <form method="post" action="secretdb.php"> <p>Username: <input type="text" name="name"></p> <p>Password: <input type="password" name="password"></p> <p><input type="submit" name="submit" value="Log In"></p> </form> <?php } else { $name = $_POST['name']; $password = $_POST['password']; // connect to mysql $mysql = mysqli_connect("localhost", "webauth", "webauth"); if(!$mysql) { echo "Cannot connect to database."; exit; } // select the appropriate database $selected = mysqli_select_db($mysql, "auth"); if(!$selected) { echo "Cannot select database."; exit; } // query the database to see if there is a record which matches $query = "select count(*) from authorized_users where name = '".$name."' and password = sha1('".$password."')"; $result = mysqli_query($mysql, $query); if(!$result) { echo "Cannot run query."; exit; } $row = mysqli_fetch_row($result); $count = $row[0]; if ($count > 0) { // visitor's name and password combination are correct echo "<h1>Here it is!</h1> <p>I bet you are glad you can see this secret page.</p>"; } else { // visitor's name and password combination are not correct echo "<h1>Go Away!</h1> <p>You are not authorized to use this resource.</p>"; } } ?> 1. However, I don't understand why this works but not the original code that I had. I'm still using the post variables in the very beginning, just like before. 2. Moreover, how should I code my sql file so that I can simply import this file through phpMyAdmin to get this to work, instead of having to manually create the "webauth" user. Here is the sql file that I used that didn't work: create database auth; use auth; create table authorized_users ( name varchar(20), password varchar(40), primary key (name) ); insert into authorized_users values ( 'username', sha1('username') ); insert into authorized_users values ( 'testuser', sha1('testuser') ); grant select on auth.* to 'webauth' identified by 'webauth'; flush privileges; 3. Finally, how should I use mysql_real_escape_string() here? I haven't really dealt with SQL injection yet so I'm not sure. Quote Link to comment Share on other sites More sharing options...
wolocaw Posted May 22, 2012 Author Share Posted May 22, 2012 Oh. It seems that I have to set the "webauth" user to specifically use localhost, instead of any host. Why is this and how should I do this in an sql file? I'd like to change my 2nd question in my previous post to this one. Quote Link to comment Share on other sites More sharing options...
darkfreaks Posted May 22, 2012 Share Posted May 22, 2012 try using a SQL validator like mimer or yacker next time here is the validated versionaccrding to SQL 2003 server create SCHEMA auth; CLOSE auth; create table authorized_users ( name varchar(20), password varchar(40), primary key (name) ); insert into authorized_users values ( 'username', sha1('username') ); insert into authorized_users values ( 'testuser', sha1('testuser') ); grant select on auth to webauth identified by webauth; CLOSE privileges; Quote Link to comment Share on other sites More sharing options...
wolocaw Posted May 23, 2012 Author Share Posted May 23, 2012 Sorry. Let me clarify. The sql file that I'm using works in that it doesn't have an error. It's just that it creates a user "webauth" that uses any host. When I manually change it so that it specifically uses localhost, then my php code does what I want it to do. I'm confused as to why this is the case. I also want to know why this code works <?php if (!isset($_POST['name']) || !isset($_POST['password'])) { //Visitor needs to enter a name and password ?> <h1>Please Log In</h1> <p>This page is secret.</p> <form method="post" action="secretdb.php"> <p>Username: <input type="text" name="name"></p> <p>Password: <input type="password" name="password"></p> <p><input type="submit" name="submit" value="Log In"></p> </form> <?php } else { $name = $_POST['name']; $password = $_POST['password']; // connect to mysql $mysql = mysqli_connect("localhost", "webauth", "webauth"); if(!$mysql) { echo "Cannot connect to database."; exit; } // select the appropriate database $selected = mysqli_select_db($mysql, "auth"); if(!$selected) { echo "Cannot select database."; exit; } // query the database to see if there is a record which matches $query = "select count(*) from authorized_users where name = '".$name."' and password = sha1('".$password."')"; $result = mysqli_query($mysql, $query); if(!$result) { echo "Cannot run query."; exit; } $row = mysqli_fetch_row($result); $count = $row[0]; if ($count > 0) { // visitor's name and password combination are correct echo "<h1>Here it is!</h1> <p>I bet you are glad you can see this secret page.</p>"; } else { // visitor's name and password combination are not correct echo "<h1>Go Away!</h1> <p>You are not authorized to use this resource.</p>"; } } ?> but not this one. <?php $name = $_POST['name']; $password = $_POST['password']; if ((!isset($name)) || (!isset($password))) { //Visitor needs to enter a name and password ?> <h1>Please Log In</h1> <p>This page is secret.</p> <form method="post" action="secretdb.php"> <p>Username: <input type="text" name="name"></p> <p>Password: <input type="password" name="password"></p> <p><input type="submit" name="submit" value="Log In"></p> </form> <?php } else { // connect to mysql $mysql = mysqli_connect("localhost", "webauth", "webauth"); if(!$mysql) { echo "Cannot connect to database."; exit; } // select the appropriate database $selected = mysqli_select_db($mysql, "auth"); if(!$selected) { echo "Cannot select database."; exit; } // query the database to see if there is a record which matches $query = "select count(*) from authorized_users where name = '".$name."' and password = sha1('".$password."')"; $result = mysqli_query($mysql, $query); if(!$result) { echo "Cannot run query."; exit; } $row = mysqli_fetch_row($result); $count = $row[0]; if ($count > 0) { // visitor's name and password combination are correct echo "<h1>Here it is!</h1> <p>I bet you are glad you can see this secret page.</p>"; } else { // visitor's name and password combination are not correct echo "<h1>Go Away!</h1> <p>You are not authorized to use this resource.</p>"; } } ?> Quote Link to comment Share on other sites More sharing options...
rythemton Posted May 23, 2012 Share Posted May 23, 2012 Here, you are trying to USE a variable that isn't set, so it gives you a warning: $name = $_POST['name']; $password = $_POST['password']; While here, you are checking to see if the variable is set, but not actually using it for anything: if (!isset($_POST['name']) || !isset($_POST['password'])) { Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.