CDbExpression and MySQL function

Archives Yii 1.0.x Discussions
1

I want to insert a new User instance in the database, having as password the sha1 hash of the password input. In the controller, I do this:

$user->password = new CDbExp​ression("SHA1('" . $_POST['User']['password']) . "')";

However, this does not work. What am I missing here?

2

In your case I would use the beforeSave function of your model and use the php version of sha1:

public function beforeSave()

{

  $this->password = sha1($this->password);

  return parent::beforeSave();

}

3

OK, but what about another MySQL function? sha1() happens to be implemented in php, but I would need the "generic" solution for this.

4

2 letscode: I think your approach is not really good, what will be if user will want to change only email and password left as is, after save his password hash will be corrupted.

2 GoofyX: I'm not sure, but if you want to use mysql hashing function to hash password you will nedd to manually create sql for inserting. I think model can't help you with that. But I'm not sure…

5

@Goofyx: What is the problem you are facing? What is the generated SQL? Did you try directly executing the SQL? Also, your original code is subject to SQL injection attack because you are directly putting the password input into the raw SQL without any escaping.

6

OK, according to my code, SQL injection is a possibility, I will fix it, thanks for pointing that out. The problem I am facing is that if for example I print out the value of the CDbExp​ression object, I get a string. That is, after I save the model, the value in the password field of the db table is

SHA1('my_password')

instead of the output of the SHA1 MySQL function.

7

That is expected. What is the complete SQL?

8

I'm using AR to save the model. The code follows:

$user->attributes = $_POST['User'];


$user->date_modified = new CDbExp​ression('NOW()');


$user->password = sha1($_POST['User']['password']);


if(isset($_POST['User']['groups'])) {


	$user->setGroups($_POST['User']['groups']);


}


if ($user->save()) {


	$this->redirect(Yii::app()->baseUrl);


}

As you can see, in the line

$user->password = sha1($_POST['User']['password']);

I would like to use CDbExp​ression, instead of the built-in sha1 PHP function. To call a stored procedure, I must use CDbCommand?

9

I mean the generated SQL statement (you may turn on logging to see it).