Проблема DB2 encrypt() с PHP и подготовленными операторами ⇐ Php

[Anonymous]

I change code from normal SQL to prepared statements (not to early I know). We use PHP8.1 and IBM DB2 One place looks tricky:

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) by ? 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 do I do wrong?

Full example

Working (without Prepared Statements)

$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 Prepared Statements)

$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


Источник: https://stackoverflow.com/questions/780 ... statements