CakePHP 3: Nested SQL functions in a query

Question

After years of using CakePHP 2, I've migrated to CakePHP 3 a week ago. The new ORM is awesome, but it makes me stumble upon some basic things, which used to work back then and behave differently in the new version.

How am I supposed to build this query on the UsersTable in Cake's orgy of find(), where() and query() chain methods?

SELECT * FROM `users` WHERE `email` = "[email protected]" AND `password`= SHA1(MD5(CONCAT("somePassword", `salt`)))

I'll go without posting my trials of achieving this, I tried pretty much about adding expressions, conditions etc. Now I'm curious about how it really should be done.

Edit: This question is not about authentication, it is much more about how to perform such queries in CakePHP 3.

ndm · Accepted Answer · 2020-12-15 20:46:11Z

3

For the sake of completeness, nested functions can be created by simply nesting function expressions.

use Cake\Database\Expression\QueryExpression;
use Cake\Database\Expression\Cake\ORM\Query;

$email = '[email protected]';
$password = 'foo';
$salt = 'bar';

$Users = TableRegistry::get('Users');

$query = $Users
    ->find()
    ->where(function(QueryExpression $exp, Query $query) use ($email, $password, $salt) {
        return
            $exp->and_([
                'email' => $email,
                'password' => $query->func()->SHA1([
                    $query->func()->MD5([
                        $query->func()->CONCAT([
                            $password,
                            'salt' => 'identifier'
                        ])
                    ])
                ])
            ]);
    });

or, which might be considered a little more simple

$functionsBuilder = $Users->query()->func();

$query = $Users
    ->find()
    ->where([
        'email' => $email,
        'password' => $functionsBuilder->SHA1([
            $functionsBuilder->MD5([
                $functionsBuilder->CONCAT([
                    $password,
                    'salt' => 'identifier'
                ])
            ])
        ])
    ]);

It should be noted that this is just an example for nesting, when doing authentication, use authentication handlers and proper password hashers as mentioned by @AlexStallen

See also Cookbook > Database Access & ORM > Query Builder > Using SQL functions

edited Dec 15, 2020 at 20:46

answered Jul 7, 2015 at 15:12

ndm

60.6k9 gold badges73 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Liglo App Over a year ago

This way of nesting wasn't clear to me after I read the docs first. But still - we have to know salt first. So it is still not the solution of my question. Using pure SQL, you need only ONE query (as I pointed out in my question). It must be somehow possible in Cake too...

ndm Over a year ago

@BarthZalewski My bad, I've overlooked that salt is a column reference... I'll update my answer.

ndm Over a year ago

@BarthZalewski Answer updated, showing the use of the literal option.

Liglo App Over a year ago

That's it. You managed to give me a fishing pole rather than a fish - means: you taught me something important about Cake's ORM: how to use SQL functions, how to nest them and how to use literals. Thank you a lot.

Alex Stallen · Accepted Answer · 2015-07-07 09:14:56Z

2

You should really read this: http://book.cakephp.org/3.0/en/controllers/components/authentication.html and then you can do something like:

$hasher = $this->passwordHasher();
$hashedPassword = $user->get($fields['password']); //password from the database
      if (!$hasher->check('password_given_by_the_user', $hashedPassword)) {
            return false;
      }
//password is ok if we reach this point

Nesting SQL: http://book.cakephp.org/3.0/en/orm/query-builder.html

$this->Model->find()
    ->where([$conditions])
    ->where([$more_conditions]);

edited Jul 7, 2015 at 9:14

answered Jul 7, 2015 at 8:50

Alex Stallen

2,25216 silver badges17 bronze badges

1 Comment

Liglo App Over a year ago

Ok, looks better now. I'll give it a try. Thank you a lot for your efforts.

Collectives™ on Stack Overflow

CakePHP 3: Nested SQL functions in a query

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related