Password Encryption - Using PostgreSQL function

mxyoung

I have a user management page that currently does this:

->on( 'preEdit', function ( $editor, $id, $values ) {
            $editor          
                ->field('users.passwd')
                ->setValue( sha1($values['passwd']) );
    } )

I need to change that to stop using sha1 and start using Postgresql's CRYPT function. The command in the psql console is:

CRYPT( $values['passwd'], GEN_SALT('md5'))

If I understand preEdit correctly, the sha1 encyption I used to use is occuring on the client side, and now I want to change it so that the encryption occurs on the server side. Is there a way I can just edit that preEdit function? Or is there another best practice for passing database commands like this? I haven't found a syntax that will pass this on to the server.

allan

Hi,

It isn’t ideal, since it requires an extra query, but what you could do is:

->on(‘preEdit’, function ($editor, $id, $values) use ($db) {
  $query = $db->raw(“SELECT CRYPT( :passwd, GEN_SALT(‘md5’)) as crypt”);
  $query->bind(‘:passwd’, $values[‘passwd’]);
  $res = $query->exec();
  $row => $res->fetch();

  $editor
    ->field(‘users.passwd’)
    ->setValue($row[‘crypt’]);
})

I’m using an iPad at the moment, so I’ve not tested that code I’m afraid, and it is quite possible I’ve made some daft mistake, but hopefully that should set you on the right path.

Allan

mxyoung

Hmmm. I really appreciate your reply. It looks like the right path, but I can't quite get it to work. I cleaned up the quotation marks, and now have this:

            $query = $db->raw("SELECT CRYPT( :passwd, GEN_SALT('md5')) as crypt");
            $query->bind(':passwd', $values['passwd']);
            $res = $query->exec();
            $row = $res->fetch();

                $editor
                    ->field('users.passwd')
                    ->setValue($row['crypt']);

The result is:

SQLSTATE[HY093]: Invalid parameter number: :passwd

From googling, it seems the single quotes around 'md5' may throw this off. I found another post on this site where someone else was having trouble with something similar.

I decided to work around it by creating a custom ajax page in PHP, and that gets me where I need to be. Posting here in case it helps anyone else.

Ajax.php:

$postdata = @$_POST['data'];

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $id = array_keys($postdata)[0];
    $action = $_POST['action'];
    $username = pg_escape_string($postdata[$id]['username']);
    $passwd = pg_escape_string($postdata[$id]['passwd']);

And then do my UPDATE statement and return the appropriate json to datatables. This way is limited (updates a single row only, for instance) but it works for my needs today.

allan

Interesting - I'm not sure immediately why that didn't work. I'll look into that!

Good to hear you have a workaround for now though.

Allan