DB2 encrypt() problem with PHP and parameterised query

94 views Asked by At

I am trying to change some SQL code to use parameters instead of hard-coded data.

This code is causing difficulty:

Update mytable set passw=encrypt(?, 'mysecretkey') where ...

The passw field is VARCHAR(128) for BIT DATA. Replacing the old code (putting new password directly into SQL) with ? ended in an SQL error about missing type binding.

Using stackoverflow I found the possible solution:

Update mytable set passw=encrypt(CAST(? as VARCHAR(128) FOR BIT DATA), 'mysecretkey') where ...

However this update SQL runs ok, but the password is not recognised anymore.

The SQL reading it uses

select decrypt_char(passw, 'mysecretkey') as PW from...

and here something totally differnt comes out.

What did I do wrong?

Full example

Working (without parameters)

$options=array();
$conn = db2_connect('xx', 'yy', 'yy', array());

$sql="
    update SHOPUSERS 
    set PASSWD=encrypt('newpassword', 'mysecretpassword')
    where SHOPID=1 and 
    CUSTOMERNO='999'  
 ";

 $stmt = db2_prepare($conn, $sql);

$fireDB2=db2_execute($stmt, array());

$sql="
    Select decrypt_char(PASSWD, 'mysecretpassword') as NEWPASSWD from SHOPUSERS  
    WHERE   SHOPID=1 and CUSTOMERNO='999'
";
$query=db2_exec($conn, $sql);
$ret=db2_fetch_assoc($query);
var_dump($ret);

Output:

array(1) { ["NEWPASSWD"]=> string(11) "newpassword" } 

Not working (with parameters)

$options=array();
$conn = db2_connect('xx', 'yy', 'yy', array());

$sql="
    update SHOPUSERS   
    set PASSWD=encrypt(CAST(? as VARCHAR(128) FOR BIT DATA), 'mysecretpassword')
    where SHOPID=1 and 
    CUSTOMERNO='999'    
 ";

 $stmt = db2_prepare($conn, $sql);

$fireDB2=db2_execute($stmt, array("newpassword"));

$sql="
    Select decrypt_char(PASSWD, 'mysecretpassword') as NEWPASSWD from SHOPUSERS   
    WHERE   SHOPID=1  and CUSTOMERNO='999'
";
$query=db2_exec($conn, $sql);
$ret=db2_fetch_assoc($query);
var_dump($ret)

Output:

array(1) { ["NEWPASSWD"]=> string(19) ">ÁÏø/ËËÏ?ÊÀ" } 

Konfig:

PHP 8.1.27, Ubuntu 22.04.3 LTS, 
ibm_db2 driver Version 2.1.5, 
IBM DB2 i5/OS V7 Release 4 M0
0

There are 0 answers