473,836 Members | 1,608 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Select * returns no data

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?
Jul 23 '05 #1
3 2884
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.
Jul 23 '05 #2
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', '43e9a4ab75570f 5b',
'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.

Jul 23 '05 #3
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_matche s
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_matche s.

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.
Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
1589
by: D. Shifflett | last post by:
Hi all, I am trying to run a Python program on my Win XP box, that I brought over from a Linux box. Of course, on Linux it works fine, but not so on Win XP. The program has a button to cause a packet to be sent to a server, the program also has a thread to read packets from the server. I send a packet, I get one back, pretty simple. I am using select() to wait for packets
1
2456
by: D. Shifflett | last post by:
Hi all, I am having trouble with a program that ran fine on Python 2.0 (#0, Mar 1 2001, 01:47:55) on linux2 but will not work on Python 2.3.2 (#1, Oct 8 2003, 17:33:47) on linux2
0
3840
by: Tanamon | last post by:
Hello All, I am a MySQL newbie trying to write a query that selects file_name records possessing the highest numbered version for that unique file_name. I show sample data and two trial queries below. Logically I want to use max(version) as a constraint in a Where Clause. However, the max() function is not allowed directly in a where clause. I have contemplated a second table to track the max version for each file name. I would like...
7
2830
by: Sunny K | last post by:
Hi guys, whilst working on a project which I thought was nearly complete I have come across a problem which was some how over seen, which I am hoping one of you guys know how to resovle. Firstly here is the script to create the table and insert some sample data:
3
3330
by: Earl Teigrob | last post by:
I am considering writing a Class that Selects, Adds, Updates and Deletes Nodes in an XML File but do not what to reinvent the wheel. (See XML file below) That data format would emulate records in a Database Table with a Primary Key for each Record (see xml sample below) and a flat file structure. I would use a class to manipulate this type of data structure extensivly if I had one. Does anyone know of such an animal?
4
5672
by: VK | last post by:
Hello, I have a dataset which has one datatable in it. The dt has over 3000 rows in it. Now I would like to get the rows where the StartDate is 15 Feb 2005, so I did the following: ds.Tables(0).Select("StartDate = '15/02/2005'") However this returns me only 3 rows. I have tried other possiblities like:
2
2182
by: alok | last post by:
Hi I have opened a serial port and then blocked on a select system call . but select returns even if no data is comming from other end. So when I read the buffer after select returns, I only found 0s nad this continues . So the problem is select returns even if there is no data in serial port buffer. void main() {
3
25691
by: Jeff | last post by:
hey ..NET 2.0 I have a datatable which consist of 3 columns (int, date, value). This DataTable have 3 rows, the values of the "date" ("date" column is of datetime datatype) column is: 2007-09-15 00:00:00.000 2007-10-15 00:00:00.000 2007-11-15 00:00:00.000
1
8277
by: remya1000 | last post by:
Iím using VB.net 2003 application program. I am trying to do a select statement whereby I'm searching between 2 datetime values that are being stored as datetime. records are stored inside Access. For example, Iím searching between 2 datetime So I need to find all the records in between 10:00 AM and 12:30 AM on 2/23/2009. i tried this code
0
10852
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10553
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10596
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10255
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6980
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5651
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4459
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4021
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.