I want to create a SQL stored procedure that checks passwords at login. This should be a very common question however I could not find an answer anywhere to my specific question, which is about data types, or perhaps something like that. I am using a MySQL database but I am unsure what type of SQL I am using specifically. Here's my SQL:

CREATE PROCEDURE SelectAllUsers @Password varchar(12)
AS
SELECT * FROM Logins WHERE Password = @Password
GO;

This image shows where I get an error for some reason, which is beyond me

I want to create a SQL stored procedure that checks passwords at login. This should be a very common question however I could not find an answer anywhere to my specific question, which is about data types, or perhaps something like that. I am using a MySQL database but I am unsure what type of SQL I am using specifically. Here's my SQL:

CREATE PROCEDURE SelectAllUsers @Password varchar(12)
AS
SELECT * FROM Logins WHERE Password = @Password
GO;

This image shows where I get an error for some reason, which is beyond me

• sql
• mysql
• authentication
• stored-procedures
• passwords

• 5 Please note that storing plain-text passwords in the database is extremely bad practice, and checking for the password alone is beyond any reasonable understanding: believe me, passwords are not unique, they are very often very weak and easy to guess, and different people are likely to come up with the same password independently. Password should be checked against a (salted, with enough iterations, intended for password hashing such as PBKDF2 or Argon2) hash retrieved by UNIQUE username/email/whatever. – STerliakov Commented Nov 17, 2024 at 1:11
• Which database are you using ? – Amit Verma Commented Nov 17, 2024 at 8:02

2 Answers 2

If you are using MySQL, navigate to Stored Procedure under you DB Schema, right click and then click 'Create Stored Procedure'. Copy below query. Change definer (root@localhost) accordingly.

CREATE DEFINER=`root`@`localhost` PROCEDURE `SelectAllUsers`(IN in_password VARCHAR(12))
BEGIN
SELECT * FROM Logins WHERE Password = in_password;
END

You don't specify the SQL dialecct that you are using, and the syntax can be different between them.
The following should work for Postgres:

CREATE PROCEDURE SelectAllUsers (IN in_password varchar(12))
BEGIN
    SELECT * FROM Logins WHERE Password = in_password;
END;

You can find more information about the syntax in the docs: https://www.postgresql./docs/current/sql-createprocedure.html