Solved

PHP Login Script error: "Query failed. No database selected"

Posted on 2008-10-07
48
1,464 Views
Last Modified: 2013-12-13
I'm working to get a login script that connects to a mysql database set up. I believe i got a good config.php and opendb.php set up already, and the login.php also seems to look right.

I just can't find where the error is coming from. Any ideas?

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

//config.php contents////

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

<?php

// db properties

$dbhost = '****';

$dbuser = '****'; 

$dbpass = '****';

$dbname = 'tbl_auth_user';

?>
 

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

//opendb.php contents////

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

<?php

$conn = mysql_connect ($dbhost, $dbuser, $dbpass) or die ('I cannot connect to the database because: ' . mysql_error());

mysql_select_db ($dbname);

?>
 

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

//login.php contents/////

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

<?php

// we must never forget to start the session

session_start();
 

$errorMessage = '';

if (isset($_POST['txtUserId']) && isset($_POST['txtPassword'])) {

	include 'library/config.php';

	include 'library/opendb.php';

	

	$userId   = $_POST['txtUserId'];

	$password = $_POST['txtPassword'];

	

	// check if the user id and password combination exist in database

	$sql = "SELECT user_id 

	        FROM tbl_auth_user

			WHERE user_id = '$userId' AND user_password = PASSWORD('$password')";

	

	$result = mysql_query($sql) or die('Query failed. ' . mysql_error()); 

	

	if (mysql_num_rows($result) == 1) {

		// the user id and password match, 

		// set the session

		$_SESSION['db_is_logged_in'] = true;

		

		// after login we move to the main page

		header('Location: main.php');

		exit;

	} else {

		$errorMessage = 'Sorry, wrong user id / password';

	}

	

	include 'library/closedb.php';

}

?>

<html>

<head>

<title>Basic Login</title>

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

</head>
 

<body>

<?php

if ($errorMessage != '') {

?>

<p align="center"><strong><font color="#990000"><?php echo $errorMessage; ?></font></strong></p>

<?php

}

?>

<form action="" method="post" name="frmLogin" id="frmLogin">

 <table width="400" border="1" align="center" cellpadding="2" cellspacing="2">

  <tr>

   <td width="150">User Id</td>

   <td><input name="txtUserId" type="text" id="txtUserId"></td>

  </tr>

  <tr>

   <td width="150">Password</td>

   <td><input name="txtPassword" type="password" id="txtPassword"></td>

  </tr>

  <tr>

   <td width="150">&nbsp;</td>

   <td><input name="btnLogin" type="submit" id="btnLogin" value="Login"></td>

  </tr>

 </table>

</form>

</body>

</html>

Open in new window

0
Comment
Question by:bdubs85
  • 25
  • 15
  • 4
  • +2
48 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22662268
please try to change:
mysql_select_db ($dbname);

into:
mysql_select_db($dbname);
0
 
LVL 24

Expert Comment

by:glcummins
ID: 22662272
This statement is failing:

 mysql_select_db ($dbname);

Try changing it to this:
if (false === mysql_select_db ($dbname))

{

   die("Cannot select database '$dbname'. MySQL error is: " . mysql_error());

}

Open in new window

0
 
LVL 1

Author Comment

by:bdubs85
ID: 22662327
angelll...i changed it and i'm still getting the same error.
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22662350
glcummins: this is my current code...i believe i put it the way i should? (code snippet below)


Now i get a parse error:

Parse error: parse error, unexpected T_IF in /home/content/***/html/php/library/opendb.php on line 2
<?php

$conn = mysql_connect ($dbhost, $dbuser, $dbpass) if (false === mysql_select_db ($dbname))

{

   die("Cannot select database '$dbname'. MySQL error is: " . mysql_error());

}

?>

Open in new window

0
 
LVL 24

Expert Comment

by:glcummins
ID: 22662373
You are missing a semicolon after the mysql_connect() statement:
<?php

$conn = mysql_connect ($dbhost, $dbuser, $dbpass);

if (false === mysql_select_db ($dbname))

{

   die("Cannot select database '$dbname'. MySQL error is: " . mysql_error());

}

?>

Open in new window

0
 
LVL 3

Expert Comment

by:wktang83
ID: 22662394
Please post the error message you got so that we can better troubleshoot your problem.
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22662413
ok...got that straight....now i'm getting some kind of permission error???

Cannot select database 'tbl_auth_user'. MySQL error is: Access denied for user '*username*'@'%' to database 'tbl_auth_user'
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22662433
wktang83: this was the *original* error lol...

"Query failed. No database selected"
0
 
LVL 24

Expert Comment

by:glcummins
ID: 22662454
You will need the MySQL administrator to give you permission to that table.

Also, *username* does not appear to be a valid username. Did you modify it for posting here, or is that really what is contained in your script?
0
 
LVL 3

Expert Comment

by:wktang83
ID: 22662457
You do not have the correct permission to connect to the specific database.

You would need to allow permission by logging into the mysql terminal as root, or using some kind of database management tool like phpmyadmin.
0
 
LVL 3

Expert Comment

by:wktang83
ID: 22662467
bdubs85:
The database might have not been created yet.

You cannot connect to a database which doesn't exist.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22662476
good catch...

now:

Cannot select database 'tbl_auth_user'. MySQL error is: Access denied for user '*username*'@'%' to database 'tbl_auth_user'

means that that user does not have permissions to that database.
check out the GRANT syntax to solve that.
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22662504
glcummins: username was what i modified it to....

i set up the table using phpMyAdmin using this script:
CREATE TABLE tbl_auth_user (

user_id VARCHAR(10) NOT NULL,

user_password CHAR(32) NOT NULL,
 

PRIMARY KEY (user_id)

);
 

INSERT INTO tbl_auth_user (user_id, user_password) VALUES ('theadmin', PASSWORD('chumbawamba'));

INSERT INTO tbl_auth_user (user_id, user_password) VALUES ('webmaster', PASSWORD('webmistress'));

Open in new window

0
 
LVL 1

Author Comment

by:bdubs85
ID: 22662538
also, i modified the $dbname on a hunch...i remember godaddy's sql setup saying that the dbname is the same as the username....so now i'm just getting a different error...


Sorry, wrong user id / password


http://helenoliveri.com/php/login.php is the login script
0
 
LVL 24

Expert Comment

by:glcummins
ID: 22662540
Okay, but this does not grant you access to the tables. You will need to run a GRANT command as the MySQL administrator as angelIII suggested. Something like:

GRANT ALL ON tbl_auth_user.* TO 'theadmin'@'localhost' IDENTIFIED BY 'chumbawamba';
0
 
LVL 3

Expert Comment

by:wktang83
ID: 22662541
Now i know what's your problem!

tbl_auth_user is a table name... NOT a database name!

So, before you create a table, you need to create a database!

Then, you store the table under the database.
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22662632
ok...before i break something:

GRANT ALL ON tbl_auth_user.* TO 'theadmin'@'localhost' IDENTIFIED BY 'chumbawamba';


this database is hosted by godaddy, so i'll login and go to the virtual console and type in that command...

GRANT ALL ON tbl_auth_user.* TO 'username'@'????' IDENTIFIED BY 'password'

so the username will be what i se in the config.php, no? but what about the localhost part? since it's a website hosted by godaddy as well, can i use localhost?
0
 
LVL 24

Expert Comment

by:glcummins
ID: 22662755
Only a database administrator can issue a GRANT command. If your database is hosted by GoDaddy, they have already told you what username and password to use. Simply insert those into your code rather than 'theadmin' and 'chumbawamba'.
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22662796
tried to make that work, but i'm getting yet more errors tryin to use the GRANT command
SQL query:
 

GRANT ALL ON tbl_auth_user . * 'myuser'@'mysqladmin2.secureserver.net' IDENTIFIED BY 'mypassword'
 

MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''myuser'@'mysqladmin2.secureserver.net' IDENTIFIED BY 'mypassword'' at line 1 

Open in new window

0
 
LVL 24

Expert Comment

by:glcummins
ID: 22662826
The correct syntax would remove the does not have spaces after the database name before the asterisk.

Second, I believe that we determined that 'tbl_auth_user' is a table name. You use GRANT statements like this to GRANT access to databases, not tables. Instead of 'tbl_auth_user', you should use the name of the database that GoDaddy gave you.

Finally, I will reiterate my point that GRANT statements can only be executed by database administrators. Has GoDaddy given you this level of access to be able to run GRANT commands? If not, you can assume that GoDaddy has already granted you access, and you simply need to input your username and password into your script where you run the mysql_connect() command.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22662865
GRANT ALL ON tbl_auth_user.* TO 'myuser'@'mysqladmin2.secureserver.net' IDENTIFIED BY 'mypassword'
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22662935
glcummins:

yes...i am using the username and password i was given when i set up the sql database, so i did assume that username would be automatically granted administrative rights: i've not had problems on all my other databases.
0
 
LVL 24

Expert Comment

by:glcummins
ID: 22662961
Okay, then let's set aside the GRANT statement for now and go back to the original error. After you double-check the username, password, and hostname, run the script again and see what error is being produced.

If the error still occurs, please post it here along with the part of the script that connects you to the database (the mysql_connect function) as well as the actual line of the script that is failing.
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22663050
it's not an actual sql error i believe

check http://helenoliveri.com/php/login.php

and use either of the username/passwords i used to create the sql table:

INSERT INTO tbl_auth_user (user_id, user_password) VALUES ('theadmin', PASSWORD('chumbawamba'));
INSERT INTO tbl_auth_user (user_id, user_password) VALUES ('webmaster', PASSWORD('webmistress'));


so i don't know...maybe it can't access the database?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 24

Expert Comment

by:glcummins
ID: 22663103
Okay, first check the SQL statement to ensure that it contains the correct values.

Just before the mysql_query line that checks to see if the username/password combination exists in the database, add a line to echo the entire statment:

echo "$sql<br />";

Now copy that entire statement, and run it manually via the MySQL monitor or PHPMyAdmin. See what is returned. If no result is returned, check the data in the table to ensure that the table actually contains the data that you think it contains.
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22663313
hm...can't find the right statement to put in...

i tried a few...this was my latest:

echo "$sql = SELECT user_id
              FROM tbl_auth_user
                  WHERE user_id = 'webmaster' AND user_password = PASSWORD('webmistress')";
0
 
LVL 29

Expert Comment

by:fibo
ID: 22663329
Just a comment by a lurker... once the script will work (solving just a problem at a time)... you REALLY NEED TO PROTECT IT against SQL injection...

Examples and solutions
- http://en.wikibooks.org/wiki/Programming:PHP:SQL_Injection
- http://www.learnphponline.com/security/sql-injection-prevention-mysql-php
Again: solve your DB access first... but just after that build some defense line to a (up to then) wide-opne script
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22663331
also, are the passwords supposed to be hashed or something?

1        *4D480C135C1B32D8E708B33445917E4
1       *8EC8195E1F0FDB5808E87CE63B8643D

are showing as the passwords...
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22663372
fibo: thank you for the input...i will definately...like you said, one problem at a time lol...
0
 
LVL 24

Expert Comment

by:glcummins
ID: 22663577
You just need to echo the contents of the $sql variable:
<?php
 

// Instead of:

$sql = "SELECT user_id 

                FROM tbl_auth_user

                        WHERE user_id = '$userId' AND user_password = PASSWORD('$password')";

        

        $result = mysql_query($sql) or die('Query failed. ' . mysql_error()); 
 

// Use this:

$sql = "SELECT user_id 

                FROM tbl_auth_user

                        WHERE user_id = '$userId' AND user_password = PASSWORD('$password')";
 

echo "$sql<br />";

        

        $result = mysql_query($sql) or die('Query failed. ' . mysql_error()); 

Open in new window

0
 
LVL 1

Author Comment

by:bdubs85
ID: 22663641
still getting sql syntax error...could it be that md5 was enabled in the table, but not in the php script?
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22663666
as in :      

CREATE TABLE tbl_auth_user (
user_id VARCHAR(10) NOT NULL,
user_password CHAR(32) NOT NULL,
0
 
LVL 24

Expert Comment

by:glcummins
ID: 22663681
The point of the last post I made was to get the full output of the SQL statement. Did you get that output? Can you post it here? What happens when you run it in the MySQL monitor or PHPMyAdmin as I suggested?
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22663765
i got a syntax error like before.
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22663775
$sql = "SELECT user_id FROM tbl_auth_user WHERE user_id = 'theadmin' AND user_password = PASSWORD('chumbawamba')";

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$sql = "SELECT user_id
                FROM tbl_auth_user
                   ' at line 1
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22663786
this is running the code as you gave it to me....the last time i replaced the $user and #password....

this is the error when i input what you posted
SQL query: Documentation
 

$sql = "SELECT user_id FROM tbl_auth_user WHERE user_id = '$userId' AND user_password = PASSWORD('$password')";
 

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$sql = "SELECT user_id

                FROM tbl_auth_user

                   ' at line 1 

Open in new window

0
 
LVL 24

Expert Comment

by:glcummins
ID: 22663859
The part that I asked you to add is simply this:

echo "$sql<br />";

This should be on a line all by itself.

Continue running your your mysql_query() statement just as you were before. All I want is for you to echo the query first, so that we can see what the query contains verbatim.
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22663951
i added it into the login.php file (ahh...finally!! lol) and this is the output:

SELECT user_id FROM tbl_auth_user WHERE user_id = 'webmaster' AND user_password = PASSWORD('webmistress')
0
 
LVL 24

Expert Comment

by:glcummins
ID: 22663966
Okay, that statement looks syntactically correct. So now complete the rest that I mentioned:

"Now copy that entire statement, and run it manually via the MySQL monitor or PHPMyAdmin. See what is returned. If no result is returned, check the data in the table to ensure that the table actually contains the data that you think it contains."
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22663980
i run that in the sql console and it says:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0006 sec)

SQL query: SELECT user_id
FROM tbl_auth_user
WHERE user_id = 'webmaster'
AND user_password = PASSWORD( 'webmistress' )
LIMIT 0 , 30
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22663997
and when i queried the user_passwords in the phpadmin , it showed:

1        *4D480C135C1B32D8E708B33445917E4
1       *8EC8195E1F0FDB5808E87CE63B8643D

not plaintext....not sure if that matters
0
 
LVL 24

Expert Comment

by:glcummins
ID: 22664026
The MySQL command PASSWORD() creates a hashed version of the password. This should match the value that you find in your table in the user_passwords fields.

To check this, run the following from the MySQL monitor:

 SELECT PASSWORD('webmistress');

Compare the output of that command to the password that is stored in your table for the user 'webmaster'. If they are not the same, you will need to update your table to contain the proper password.

Do the same for the user 'theadmin' and the associated password.
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22664052
no...they are different lengths...


8EC8195E1F0FDB5808E87CE63B8643DACADAD7EA
8EC8195E1F0FDB5808E87CE63B8643D
0
 
LVL 24

Accepted Solution

by:
glcummins earned 100 total points
ID: 22664081
Sounds like you created the field with too small of a length. You will need to change the field to be able to contain the entire length of the password. It is currently being truncated at 32 characters, but needs at least 42 characters.

After you make the change, you will need to recreate the password hashes.
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22664108
ok...i'll recreate the table with a 42 character length and check it...

lol this is crazy. thanks for the help so far...i really appreciate it.
0
 
LVL 1

Author Comment

by:bdubs85
ID: 22664159
finally. lol. i took off the echo b/c it was throwing an error. it works beautifully now. thank you.
0
 
LVL 1

Author Closing Comment

by:bdubs85
ID: 31597321
Great assistance. Very patient. =)
0
 
LVL 24

Expert Comment

by:glcummins
ID: 22664257
Yea! I'm glad it is working. Enjoy!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now