phpmyadmin Question posted in
The official documentation can be found here.

How to fix this procedure? – Phpmyadmin

This is my first time creating a sql procedure, and i need some help.
What i am trying to do is to create a procedure that returns true or false if the user has correctly inputed his email and password

DELIMITER $$

CREATE PROCEDURE login(IN email varchar(50), IN password varchar(30))
BEGIN
    DECLARE @email VARCHAR(50);
    DECLARE @password VARCHAR(50);
    IF((SELECT COUNT(id) FROM users WHERE email = @email AND password = @password) = 0, 'true', 'false');
END$$

DELIMITER ;

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘@email VARCHAR(50);
DECLARE @password VARCHAR(50);
IF((SELECT COUNT(id’ at line 3

This is the error message, i am using phpMyAdmin

Tags:

2

Answers


  1. 0

    You want your procedure to return something but you don’t use ‘return’ in it

    https://wiki.ispirer.com/sqlways/mysql/techniques/return-value-from-procedure

    beside that, your code look very much vulnerable to SQL Injection

    https://security.stackexchange.com/questions/68701/how-does-stored-procedure-prevents-sql-injection

    Maybe you could consider doing this check in your application instead of inside the database

    Login or Signup to reply.
  2. 0

    Maybe this example will help you: 

    create table Test(Pass varchar(100), Mail varchar(100));
insert into Test(Pass, Mail) values('Pass1', "Email1");
insert into Test(Pass, Mail) values('Pass2', "Email2");
insert into Test(Pass, Mail) values('Pass3', "Email3");


DROP FUNCTION IF EXISTS loginFunction;
DELIMITER go
CREATE FUNCTION login(p_Pass char(100), p_Mail char(100)) RETURNS boolean
BEGIN

 declare countUser int;    
 DECLARE flag boolean;


 SET flag = IF( (select count(*) 
 from Test 
 where Pass = p_Pass and Mail = p_Mail) > 0, 1, 0);
 RETURN flag;

END;
go
DELIMITER ; 

SELECT login('Pass1', 'Email1');

    Demo:
    https://paiza.io/projects/sioJelUeAuqfrStgCe2h5w?language=mysql

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search