Select * returns no data

[Hendry Taylor]

I have a problem where if I issue a select * from against a database it
returns no data,

but if I select column from it returns the data.

Why would the * not be working as a wildcard?

[Bill Karwin]

Hendry Taylor wrote:

I have a problem where if I issue a select * from against a database it
returns no data,

but if I select column from it returns the data.

Why would the * not be working as a wildcard?

That's certainly unexpected.

Questions:
- What version of MySQL are you using?
- What environment are you using to execute the query? e.g. mysql
command-line tool, MySQL Query Browser gui application, Java program,
Perl script, C++, Microsoft ODBC client...
- Does this behavior occur in other query environments? Try it in the
mysql command-line tool and see if you get the same strange result.

Regards,
Bill K.

[Hendry Taylor]

I am using this query from a PHP script but I am also testing it from
the windows gui.

The mysql server runs on a sun sparc with solaris 10, the mysql version
is 4.1.12
php 5.0.4

one thing i forgot to mention is that it only occurs when i have a where
clause. if i do just a select * from tabel it works fine. The result of
my where clause is a single row, but i want all the columns.

My table definition looks as follows:

CREATE TABLE clients (
clientid tinyint(4) NOT NULL auto_increment,
name varchar(50) NOT NULL,
password varchar(50) NOT NULL,
email varchar(255) NOT NULL,
ref varchar(50) NOT NULL,
title varchar(255) NOT NULL,
PRIMARY KEY (clientid)
);

#
#
#

INSERT INTO clients VALUES( '1', 'admin', '43e9a4ab75570f5b',
'y***@email.com', '', 'admin');

my select statement is as follows:

select * from clients where name = 'admin' and password = PASSWORD('admin')

I am trying to match the password given in the web page to php to the
one store in mysql.
If i change the select to only select a single column it works, put the
* back and it no longer works.

I tried it via the mysql interactive tool on the sun box and it works.
Bill Karwin wrote:

Hendry Taylor wrote:

I have a problem where if I issue a select * from against a database it
returns no data,

but if I select column from it returns the data.

Why would the * not be working as a wildcard?

That's certainly unexpected.

Questions:
- What version of MySQL are you using?
- What environment are you using to execute the query? e.g. mysql
command-line tool, MySQL Query Browser gui application, Java program,
Perl script, C++, Microsoft ODBC client...
- Does this behavior occur in other query environments? Try it in the
mysql command-line tool and see if you get the same strange result.

Regards,
Bill K.

[Bill Karwin]

Hendry Taylor wrote:

select * from clients where name = 'admin' and password = PASSWORD('admin')

I am trying to match the password given in the web page to php to the
one store in mysql.
I would do this in the following way:

SELECT c.clientid, c.name, c.email, c.ref, c.title,
IF(c.password = PASSWORD(?), 1, 0) AS password_matches
FROM clients AS c
WHERE c.name = 'admin'

Then plug in the user's input in place of the ? above.
If i change the select to only select a single column it works, put the
* back and it no longer works.

I tried it via the mysql interactive tool on the sun box and it works.

I believe the PHP interface to MySQL uses the MySQL 4.0 client library,
which has a different encryption algorithm for the PASSWORD function in
MySQL 4.1.1 and later. See the note about PHP on this page:

http://dev.mysql.com/doc/mysql/en/old-client.html

This would not explain why the query works when you select one column
and doesn't work when you select all columns, though. Unless there's
some sort of query result caching going on. Have you tried the PHP
query with specifying all the columns explicitly, instead of using "*"?

By the way, the PASSWORD function is supposed to be used only by MySQL;
the docs recommend that we do not use it in our own applications, for
the compatibility reason above. Their recommendation is to use a
hashing function like MD5 when we want to store things like passwords
for web application. See the note under the PASSWORD docs on this page:

http://dev.mysql.com/doc/mysql/en/en...functions.html

I made this mistake too, in a project a couple of years ago. I used the
PASSWORD function for the user accounts in a Perl web application. But
that made it hard to upgrade to MySQL 4.1. So I changed the user
account creation and modification page to store the password as MD5() of
the user's password input, and then my password-checking expression in
the login page to:

IF(c.password = MD5(?) OR c.password = PASSWORD(?), 1, 0) AS
password_matches.

So over time, people will gradually convert their passwords to MD5
hashes as they update their passwords. Then the app can be upgraded to
MySQL 4.1 seamlessly.

Regards,
Bill K.