0

I am new to writing MySQL stored procedures and I am having some issues.

This is the stored procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS login$$

CREATE PROCEDURE login(
    IN usern VARCHAR(11),
    IN pw VARCHAR(30),
    OUT msg VARCHAR(50)
)
login_proc:
BEGIN
    SELECT COUNT(*) as voter
    FROM regVoter 
    WHERE studentID = usern AND password = pw;

    IF voter = 1 THEN
        SET msg = "login successfully";
    ELSE
        SET msg = "login failed";
    END IF;

    LEAVE login_proc;
END $$

DELIMITER ;

The following is the PHP file in which the stored procedure is to be called: 

<?php 
    ini_set('display_errors','1'); error_reporting(E_ALL);
    include_once 'dbConnect.php';

    $username = $_POST['username'];
    $password = $_POST['password'];

    if (connect()){
        global $conn;

        $query = $conn->query("CALL login('$username','$password',@msg)") or die("Query failed: " . $conn->error);

        $result = $conn->query("SELECT @msg AS data");
        $row = $result->fetch_assoc();
        echo $row['data'];
    }
    else{
         $data = "connection error!";
    }   
?>

The error I am getting is "Uncaught Error: Call to a member function fetch_assoc() on boolean" which means one of the queries is returning false.

What am I doing wrong here?

Mag
  • 89
  • 1
  • 11
  • Is this a standard method for verifying a login? – Strawberry Feb 15 '18 at 08:09
  • Are you storing plain text passwords? – Scriptman Feb 15 '18 at 08:09
  • @Strawberry Yes, it is verifying login – Mag Feb 15 '18 at 08:10
  • @Scriptman Yes, the passwords are plaintexts right now (but we plan to hash them later on, of course) – Mag Feb 15 '18 at 08:11
  • I know. Is it the standard approach? – Strawberry Feb 15 '18 at 08:13
  • @Strawberry I'm sorry. I do not understand your question. – Mag Feb 15 '18 at 08:17
  • This http://php.net/manual/en/faq.passwords.php is how you should handle passwords when programming in PHP. DROP and forget the stored procedure verifying – Raymond Nijland Feb 15 '18 at 08:20
  • Have you seen other people successfully using this method to verify logins? – Strawberry Feb 15 '18 at 08:24
  • @Strawberry Yes, one of my friends used this method before and it worked – Mag Feb 15 '18 at 08:28
  • 1
    It'll work yes, but it is very unsafe. Modern day password hashing should be done in your application as MySQL only has quick hashing algorithms not secure ones. If I were you I'd stop what you're doing, read up on login systems with PHP, because you'll end up having to rewrite this part of your application anyways. Follow @RaymondNijland link for more information on PHP and passwords. Also passing the password plaintext to your database could make it end up in query loggers and then you're even further from home. – Niels Feb 15 '18 at 08:34
  • @Niels I was advised to proceed this way to prevent SQL Injection Attacks from happening. – Mag Feb 15 '18 at 08:39
  • 2
    @Mag Stored procedures aren't there to prevent SQL injection as it's still possible with them. If you want to prevent SQL injection read up on Prepared Statements (either with PDO or Mysqli) which really should be the default these days http://php.net/manual/en/mysqli.quickstart.prepared-statements.php – Niels Feb 15 '18 at 08:42

0 Answers0