MySQL Bugs: #42155: Issue with null and empty Strings .

Bug #42155	Issue with null and empty Strings .
Submitted:	16 Jan 2009 11:31	Modified:	16 Jan 2009 11:44
Reporter:	nse-dba dba	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.68	OS:	Solaris
Assigned to:		CPU Architecture:	Any
Tags:	Issue with null and empty Strings .

Description:
 Hi,
We are new to MySQl. We have an mysql application which is in testing phase. We have an application table where userid's and passwords are stored.
Please find details of the create script of the table. We are using innodb engine for all the application tables. The version of mysql is 5.0.68-enterprise-gpl.

CREATE TABLE `login_details` (
`USERID` varchar(50) NOT NULL,
`PASSWORD` varchar(50) NOT NULL,
`FIRSTNAME` varchar(50) default NULL,
`LASTNAME` varchar(50) default NULL,
`EMAIL` varchar(50) NOT NULL,
`MISC` varchar(50) default NULL,
PRIMARY KEY (`USERID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Now I am inserting blank strings into the table
insert into sras_login_details values ('','','','','','');

The issue here is :

I am able to login to my application without giving userid and password in the login screen.

Please tell us how this issue can be solved in db level.

We are aware that "NULL" , ' '<blank string> are different in mysql.

Please suggest us a solution ?

Thanks,
Nse-dba team

How to repeat:
CREATE TABLE `login_details` (
`USERID` varchar(50) NOT NULL,
`PASSWORD` varchar(50) NOT NULL,
`FIRSTNAME` varchar(50) default NULL,
`LASTNAME` varchar(50) default NULL,
`EMAIL` varchar(50) NOT NULL,
`MISC` varchar(50) default NULL,
PRIMARY KEY (`USERID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into login_details values ('','','','','','');

select * from login_details;

Suggested fix:
Please treat blank ( empty string ) as null. This is same as in oracle.

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug, likely. If you still think it is, please, provide a code from your application that works with this table.

According to SQL Standard NULL != '' (empty String). Oracle is not standard conform here. 

All directy supported DBMS from Sun are standard conform and you will find in all of them that NULL and '' is treated different.