[FIXED] how to execute stored procedure in cakephp 3?

Issue

This is my function for login i call a stored procedure login for match user name and password, but it doesn't work for me it give me all the rows:

public function login($email,$password)
    {
    $consumers = TableRegistry::get('Consumers');
    $result=$consumers->query("Call login('".$email."','".$password."')");
    pr($result->toArray());die;
    }

My stored procedure in phpmyadmin is below:

BEGIN
  SELECT * FROM consumers WHERE email = email_id AND password =     md_password;
END

When i am executing query it give me object but after converting this object into array it give me all the row of table.output is:

<pre class="pr">Array
(
    [0] => Cake\ORM\Entity Object
        (
            [_properties:protected] => Array
                (
                    [id] => 1
                    [name] => jeevan
                    [email] => j@gmail.com
                    [password] => asdf
                    [phone_no] => 8447726137
                    [ota] => cde
                    [status] => 0
                    [created_on] => Cake\I18n\FrozenTime Object
                        (
                            [date] => 2016-07-08 17:28:52
                            [timezone_type] => 3
                            [timezone] => UTC
                        )

                    [token_access] => 
                    [device_type] => 1
                    [push_id] => abc
                    [want_news] => 1
                    [postal_code] => 263136
                    [registration_type] => 1
                )

            [_original:protected] => Array
                (
                )

            [_hidden:protected] => Array
                (
                )

            [_virtual:protected] => Array
                (
                )

            [_className:protected] => 
            [_dirty:protected] => Array
                (
                )

            [_new:protected] => 
            [_errors:protected] => Array
                (
                )

            [_invalid:protected] => Array
                (
                )

            [_accessible:protected] => Array
                (
                    [*] => 1
                )

            [_registryAlias:protected] => Consumers
        )

    [1] => Cake\ORM\Entity Object
        (
            [_properties:protected] => Array
                (
                    [id] => 2
                    [name] => jack
                    [email] => jack@gmail.com
                    [password] => 123
                    [phone_no] => 7409757656
                    [ota] => chb
                    [status] => 1
                    [created_on] => Cake\I18n\FrozenTime Object
                        (
                            [date] => 2016-07-20 06:10:14
                            [timezone_type] => 3
                            [timezone] => UTC
                        )

                    [token_access] => ghcvhgv
                    [device_type] => 0
                    [push_id] => hgnjh
                    [want_news] => 1
                    [postal_code] => 263136
                    [registration_type] => 1
                )

            [_original:protected] => Array
                (
                )

            [_hidden:protected] => Array
                (
                )

            [_virtual:protected] => Array
                (
                )

            [_className:protected] => 
            [_dirty:protected] => Array
                (
                )

            [_new:protected] => 
            [_errors:protected] => Array
                (
                )

            [_invalid:protected] => Array
                (
                )

            [_accessible:protected] => Array
                (
                    [*] => 1
                )

            [_registryAlias:protected] => Consumers
        )

)</pre>

that mean the stored procedure not working, any idea really help me, thanks in advance!

Solution

You can call a stored procedure using the ConnectionManager's execute() method. It's important to remember to prepare the query so that you remove the risk of SQL injection:-

$this->connection = ConnectionManager::get('default');
$results = $this->connection->execute(
    'CALL login(?, ?)', 
    [$email, md5($password)]
)->fetchAll('assoc');

When CakePHP runs execute() it will substitute the ? with the escaped and quoted values of $email and md5($password). For further details check out the official docs on Preparing a statement.

Answered By - drmonkeyninja