Postgres digest function works inside procedure on pgadmin but not in Laravel

recently I have created stored procedure in Postgres Sql which deals about with hashing passwords. When used via PgAdmin tool it works like a charm as stated below.

I have also installed extension pgcrypto.

SELECT horeca_user.update_token(
    1, //user_id
    1, //type
    'sdsfsdfs' //token
)

It works when called via pgadmin tool, but not inside laravel app as stated below.

$token=\DB::select("SELECT * from \"horeca_user\".update_token('$type', '$user_id', '$token')");

and finally this is my procedure code

CREATE OR REPLACE FUNCTION horeca_user.update_token(tp integer,userid integer,token character varying)
    RETURNS character varying
    LANGUAGE 'plpgsql' VOLATILE
    PARALLEL UNSAFE
    COST 100
AS $BODY$ 
DECLARE uid INTEGER;
DECLARE tok character varying;
BEGIN
    uid:=0;
    Select count(*) as total from horeca_user.user_tokens where user_id=$2 into uid;

    if uid=0 THEN
       Insert into horeca_user.user_tokens(id, user_id, tip) values($3, $2, $1);
     else
       Update horeca_user.user_tokens set id=$3, tip=$1 where user_id=$2;
     END IF;

     SELECT encode(digest(token, 'sha256'), 'hex') as tt into tok;

     return tok;
END

$BODY$;

Thanks for attention.

I just checked that query with PDO connection in pure PHP. Query worked as expected. From here I come to decision that problem associated with Laravel not PosgreSql. Below is full code

$db = new PDO("pgsql:dbname=Horecami;host=127.0.0.1", "postgres", "1" );
$sql = 'Select horeca_user.update_token(?, ?, ?)';
$stmt = $db->prepare($sql);
$type=1;
$id=1;
$z='az';
$stmt->bindValue(1, $type, PDO::PARAM_INT);
$stmt->bindValue(2, $id, PDO::PARAM_INT);
$stmt->bindValue(3, $z, PDO::PARAM_STR);
$stmt->execute();
$res=$stmt->fetchAll();

But i cannot understand why Laravel PDO instance cannot do it?

But your Laravel query is different from the one you run in pgAdmin and PDO so how can you make a fair comparison? In Laravel you SELECT * from a function that returns a string - I don’t think you can SELECT FROM a string, you can only select from tables or from table data types - that would be possible if your function returned a table row but it returns a string. So first use the same query everywhere.

this procedure returns me string. And without this line as stated below

SELECT encode(digest(token, 'sha256'), 'hex') as tt into tok;

it works normally. So there is not abnormality in select.

There’s nothing wrong with your procedure. But look at your Laravel query:

SELECT * from \"horeca_user\".update_token('$type', '$user_id', '$token')

It’s fundamentally different from the one you send in pgAdmin or PDO.

I know my procedure works normally and I stated in my previous post

I just checked that query with PDO connection in pure PHP . Query worked as expected. From here I come to decision that problem associated with Laravel not PosgreSql . Below is full code

I know problem is with a Laravel and I need help.

But can’t you see you are using a different query in Laravel than in PDO? You showed us the query you used in PDO and it’s different from the one you used in Laravel - that’s what your code posted here shows. First, you have to make both the same. In short, the query you send with Laravel is invalid, it’s simply incorrect SQL and it’s not Laravel’s fault it doesn’t work. First make sure you send a valid query and then we will know if it’s really Laravel that is problematic.

The way of calling query in Laravel is valid. But may be there is some limitations with PDO configuration that arises problem.

Sorry, you are right, the query SELECT * FROM function is valid for Postgres so it must be something with Laravel. Maybe try getting rid of the double quotes or even the whole * FROM part, which seems redundant, anyway.

What errors do you get? You should be getting some errors unless you have silenced them.

What I would do is simply run the code with a debugger and inspect the relevant database code run by Laravel - I’m sure this way you could get to root of the problem.

this is error

[2019-03-31 18:49:30] local.ERROR: SQLSTATE[42883]: Undefined function: 7 ERROR:  function digest(character varying, unknown) does not exist
LINE 1: SELECT encode(digest(token, 'sha256'), 'hex') as tt
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT encode(digest(token, 'sha256'), 'hex') as tt
CONTEXT:  PL/pgSQL function update_token(integer,integer,character varying) line 14 at SQL statement (SQL: SELECT * from "horeca_user".update_token('2', '174', 'az')) {"userId":174,"email":"azegurb@gmail.com","exception":"[object] (Illuminate\\Database\\QueryException(code: 42883): SQLSTATE[42883]: Undefined function: 7 ERROR:  function digest(character varying, unknown) does not exist
LINE 1: SELECT encode(digest(token, 'sha256'), 'hex') as tt

Laravel problem is to read this line inside stored function
SELECT encode(digest(token, 'sha256'), 'hex') as tt into tok;

What might be the problem. I also upgraded Laravel version from 5.7 to 5.8. Still same problem exists

I think this is not Laravel’s problem. The error says that digest function does not exists, which implies the pgcrypto extension is not installed. I can think of three scenarios:

  1. With Laravel you connect to a different database than with pdo.
  2. Or, with Laravel you connect to the same database but as a different user which doesn’t have privileges to access the digest functtion.
  3. Or, the postgres seach_path is different on your Laravel connection and the procedure can’t find the digest function, in which case using the full name might help like public.digest.

I’d check the connection parameters for Laravel as I suspect they might be different from your pdo ones.

1 Like

I changed digest to public.digest then it worked.
Thank you very much. I was struggling about two weeks.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.