1

I'm writing a registry form and wanna check if inserted data exists.

I wrote my own function to do that. This Function should check tables and if rows exist, return FALSE and if not exist then return TRUE and will insert the data to the tables.

I have 2 tables: 'users' and 'passwords'.

USERS(
  user_id INT AUTO_INCREMENT,
  user_login VARCHAR(30) NOT NULL,
  user_email VARCHAR(255) NOT NULL,
  join_date TIMESTAMP NOT NULL,

  PRIMARY KEY - user_id
)

PASSWORDS(
  password_id INT AUTO_INCREMENT,
  user_id INT NOT NULL,
  hash_password VARCHAR(255) NOT NULL,

  PRIMARY KEY - password_id,
  FOREIGN KEY - user_id REFERENCES USERS(user_id)
)

My SQL CODE:

CREATE FUNCTION `create_user`(`login` VARCHAR(30), `password` VARCHAR(255), `email` VARCHAR(255)) RETURNS BOOLEAN DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN
    IF (SELECT EXISTS(SELECT user_login,user_email FROM users WHERE user_login = login OR user_email = email LIMIT 1) OR (SELECT EXISTS(SELECT hash_password FROM passwords WHERE hash_password = password LIMIT 1)))
    THEN
        RETURN FALSE;
    ELSE
        INSERT INTO users(user_login,user_email)
        VALUES(
            login,
            email
            );
         INSERT INTO passwords(user_id, hash_password)
         VALUES(
             (SELECT MAX(user_id) FROM users),
             password
             );
         RETURN TRUE;
    END IF;
END

MY PHP CODE:

 public static function createUser($user,$password,$email,$DbDependency)
        {
            self::setDependency($DbDependency);
            $query = "CALL create_user('$user','$password','$email')";
            try
            {
                if(self::getDependency() -> query($query))
                {
                    new UserManager($user,$password,$email);
                    header("Location: ../../Views/congratulations");
                }
                else
                {
                    echo "bad";
                }

            }
            catch(PDOException $error)
            {
                echo $error -> getMessage();
            }

        }

MySQL function 'create_user' should return FALSE if data exists OR TRUE if not exists,insert data to tables and PHP. IF function works then it should create a new userManager object and go to next page.

Instead of this page, it shows me "bad" and tables are still empty.

Jitendra Ahuja
  • 749
  • 3
  • 9
  • 22
Sebastian Miklaszewski
  • 23
  • 5
  • IF (SELECT EXISTS - is an odd way to put it why not If exists..AND does the function work purely in mysql? – P.Salmon Sep 12 '19 at 10:41
  • Yes this function is in mysql. I wanted in this way reduce the executing time. I found this way "IF (SELECT EXISTS... " in another question on stack overflow and i tried to apply that – Sebastian Miklaszewski Sep 12 '19 at 10:51
  • does the query work using pure SQL? can you get the function to work solely using MySQL? – delboy1978uk Sep 12 '19 at 11:21
  • 1
    A `procedure` gets `call`-ed. A `function` cannot be `call`-ed but it is executed by using it in an expression, e.g. `select create_user(..., ..., ...)`. But you probably want a procedure with an [`out-parameter`](https://stackoverflow.com/q/1113579). A side note (not really relevant to your problem): storing the password in a separate table is uncommon, you may just want to store it in the `users`-table. It's also uncommon to exclude someone from having the same password as someone else. – Solarflare Sep 12 '19 at 11:31
  • So I should put passwords in users table and let to having the same password by diffrent users? – Sebastian Miklaszewski Sep 12 '19 at 12:30
  • You don't have to have just one table. Your approach works (it specifically is not wrong!), might just make things more complicated than they have to be (e.g. you need to get the user id to add the password). (Unless there are reasons you need it, e.g. to prevent to reuse a password you had recently). Comparing to other users passwords is a different matter, relevant for security and you shouldn't do it (and with proper hashing: cannot do it). You may want to ask (or check if its already answered) on http://security.stackexchange.com for the reasons (too long/off-topic for a comment). – Solarflare Sep 12 '19 at 14:22

0 Answers0