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
encrypt()is deprecated and might be removed in future Db2 releases (2) better practice is to OMIT the key and to supply it separately via a hostvariable with theset encryption passwordstatement, (3) the returned item from enrypt() is LONGER than the plain-text (by up to 15 bytes or so , meaning you have to double check that it fits and (4) on Db2-LUW the max length of they key is 127 bytes.