0
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_register`(Fullname VARCHAR(50), Email VARCHAR(150), Pass VARCHAR(50))
BEGIN

DECLARE s VARCHAR(20);
DECLARE returned_id varchar(100);

SET returned_id = (SELECT email FROM users WHERE email = Email);

IF (returned_id IS  NULL) THEN
    INSERT INTO users (`name`, `email`, `password`, `create_time`) 
        VALUES (Fullname, Email, Pass, CURRENT_TIMESTAMP());

SET s = 'success';

call log_msg(s); #for testing


ELSE
        SET s = 'exists';
        call log_msg(s); #for testing

END IF;

END

When doing this stored procedure, it seems to add a user to the user table if the table is empty. Then, no matter what variables I input, the user table always says that the user exists inside the user table.

Ex) I call the procedure with ('ss', 'ss', 'ss') on an empty user table and it will succesfully update the parameters in the table with ss, ss, and ss.

Then, if I call the procedure with ('123','123','123') on the user table where ss,ss,ss is already a user. The procedure thinks that 123,123,123 is already in the table and won't update it.

Please help, I need a stored procedure for user registration and it doesn't seem to work and I can't find a way for it to work. I believe my code works logically, I don't get why it is not updating it.

Hamza Mohamed
  • 1,373
  • 1
  • 12
  • 28
Steven Choi
  • 25
  • 5
  • 1
    A) Why are you creating a prepared statement for a simple insert? B) You really **cannot** save plain-text passwords in a database, the risk is way too high. At the absolute least you **must** hash them with a password-specific hash that's designed to be resistant to brute-force attacks like [Bcrypt](https://en.wikipedia.org/wiki/Bcrypt). – tadman Nov 02 '18 at 04:46
  • PSA: An email address can be ridiculously long, it's technically allowed, so 150 characters is actually [too short](https://stackoverflow.com/questions/386294/what-is-the-maximum-length-of-a-valid-email-address). Try and use `VARCHAR(255)` as a default "string" type field as that's usually good enough and only adjust it to longer or shorter when strictly necessary. This goes doubly for passwords and names where too-short fields really, really frustrate people. – tadman Nov 02 '18 at 04:50
  • PSA(2): 255 in a byte per char character set for the email seems well justified. Otherwise it just seems like a arbitrary number based on [historical patten of using an old limit](https://openquery.com.au/blog/text-varchar-inefficiencies-db-schema) and sorter/longer has no significant database impacts. Passwords should be a known length because most common hashes output are a fixed length :-). Totally agree with short name fields. Likewise the application shouldn't enforce a minimum name length (who is it to tell me what my name can be!). – danblack Nov 02 '18 at 05:55
  • As you've found stored procedures are significantly hard to debug. Re-evaluable why you need them, application code is generally as secure and more manageable. I've written SQL for a number of years and have written only one or two. – danblack Nov 02 '18 at 05:58

0 Answers0