2

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
8
  • 1
    Can you show the PHP code you're using as well please? We need a minimal reproducible example of the issue. P.S. Passwords are supposed to be hashed, not encrypted. Commented Mar 1, 2024 at 15:35
  • Beware (1) 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 the set encryption password statement, (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. Commented Mar 1, 2024 at 16:04
  • I added a full example with PHP. Thanks for the hint about encrypt deprecated, unfortunately I am not able to change the database structure. Commented Mar 3, 2024 at 8:28
  • 1
    Yes, the question needs editing to correct the misleading description and title, and to add exact details of the environment, versions, which drivers being used etc. Regardless of that, both supplied code fragments yield the same output in my environment. Linux x64, db2 v11.5 , php 7.2.24, ibm_db2 2.0.8. So there is something amiss with the configuration or the fragments of code differ substantially from what is really running. Commented Mar 3, 2024 at 11:32
  • 1
    Most likely a driver problem. The support for IBM i by the pecl php ibm_db2 is deprecated, and IBM recommends that users instead use the PDO_ODBC or odbc extension with the i-access-driver . See github.com/php/pecl-database-ibm_db2?tab=readme-ov-file Commented Mar 4, 2024 at 9:45

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.