Re: Issue with null and empty Strings .
Hi nse-dba!
-------------------------------------------------------
> Now I am inserting blank strings into the table
> insert into 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.
> Please suggest us a solution ?
The obvious answer is: don't insert stuff you don't want in your db. At any rate my advice would be to add a check to the client application, and prevent such requests from being sent to the server at all. This check would also be used to inform the user they are doing something wrong.
Of course, there should also be a check at the server level to implement integrity. However, I do not think this must be a check on the db. I.E. the server should not trust input from its clients and reject invalid input.
You could, in MySQL >= 5.0 add a BEFORE INSERT TRIGGER to the table like this:
CREATE TRIGGER BIR_login_details
BEFORE INSERT ON login_details
FOR EACH ROW
BEGIN
IF USERID = '' THEN SET USERID = NULL END IF;
IF PASSWORD = '' THEN SET PASSWORD = NULL END IF;
IF EMAIL = '' THEN SET EMAIL = NULL END IF;
END;
Beware that this trigger will slow down inserts on the table considerably. Not sure if that's a big deal for your purpose.
Roland Bouman
http://rpbouman.blogspot.com/