update-after hook to track user who made last modification

[jscott]

I am trying to automatically update a field containing username of the user who most recently modified a record. To do this, I want to use an update-after hook.

[pre]
$hooks['sample_log']['update']['after'] = 'dadabik_update_username_sample_log';

function dadabik_update_username_sample_log($id)
{
global $conn;
$sql = "UPDATE sample_log SET updated_by = ".$quote.$current_user.$quote." WHERE id = :id";
$res_prepare = prepare_db($conn, $sql);
$res_bind = bind_param_db($res_prepare, ':id', $id);
$res = execute_prepared_db($res_prepare,0);
}
[/pre]


When tested with on sample_log.id = 3, The hook elicits the following error: [08] Error: during query execution. The DBMS server said: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = '3'' at line 1.

As far as I can tell, the sql parses correctly, so I'm not sure where I'm going wrong. I expect it is something really simple, but the solution has bedeviled me.

----
DaDaBIK version 9.2-Monterosso enterprise, installed on 12-31-2018 (installation code: 142345c29be87db0ca), the latest version of DaDaBIK is 9.4-Monterosso released on 07-23-2019
PHP Version: 7.2.19-0ubuntu0.18.04.2
mysql version: 5.7.27-0ubuntu0.18.04.1
Web server: Apache/2.4.29 (Ubuntu)
Client: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.75 Safari/537.36

 

[eugenio]

Hello,
I think you just missed to mention $current_user as global variable:

global $conn, $current_user;

Best,

 

[jscott]

Thanks very much Eugenio, that was part of the problem, but I am still encountering an error. Here's my 'modified' code:

[pre]
$hooks['sample_log']['update']['after'] = 'dadabik_update_username_sample_log';

function dadabik_update_username_sample_log($id)
{
global $conn, $current_user, $quote;
$sql = 'UPDATE sample_log SET updated_by = '.$quote.$current_user.$quote.' WHERE id = :id';
$res_prepare = prepare_db($conn, $sql);
$res_bind = bind_param_db($res_prepare, ':id', $id);
$res = execute_prepared_db($res_prepare, 0);
}
[/pre]

The revealed sql generated (debug on) is:

UPDATE `sample_log` SET `id` = '3', `job_id` = NULL, `sample_type` = NULL, `location` = 'test3', `number` = NULL, `medium` = NULL, `material` = NULL, `comments` = NULL, `updated_by` = 'jscott' where `id` = '3'

The following error message is generated:

[08] Error: during query execution. The DBMS server said: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'jscott' in 'field list'

When I copy/paste and execute the sql directly, it works fine.

 

[eugenio]

Hello,
you should use $quote for table and field names, not for field values.

I would rewrite the query as

... updated_by = :current_user ...

and then I would add

$res_bind = bind_param_db($res_prepare, ':current_user', $current_user);

Best,

 

[jscott]

Thanks so much Eugenio. I was able to get everything working. I expanded the idea a bit to make the hook function for multiple tables (as long as the primary key has the same name, in this case `id`). For any users looking for a functional hook to do this, the working code is included below:

[pre]
$hooks[$table_name]['update']['after'] = 'dadabik_update_username';

function dadabik_update_username($id)
{
global $conn, $table_name, $current_user;

if ($table_name != 'schema_migrations' || $table_name != 'versions') {

$sql = 'UPDATE `:table_name` SET `updated_by` = :current_user WHERE `id` = :id';
$res_prepare = prepare_db($conn, $sql);
$res_bind = bind_param_db($res_prepare, ':table_name', $table_name);
$res_bind = bind_param_db($res_prepare, ':current_user', $current_user);
$res_bind = bind_param_db($res_prepare, ':id', $id);
$res = execute_prepared_db($res_prepare, 0);

}
}
[/pre]

 

[eugenio]

You are welcome!

PS you cannot use a placeholder for table_name, you can use placeholders just for field values.

Best,

 

[LurkingKiwi]

I think it would be safe to use
[pre]
$sql = 'UPDATE '.$quote.$table_name.$quote.' SET `updated_by` = :current_user WHERE `id` = :id';
[/pre]
to get the same effect because $table_name is not user-entered. Or does $table_name need to be possibly escaped?

 

[eugenio]

LurkingKiwi Wrote:
-------------------------------------------------------
> I think it would be safe to use
> [pre]
> $sql = 'UPDATE '.$quote.$table_name.$quote.' SET `
> updated_by` = :current_user WHERE `id` = :id';
> [/pre]
> to get the same effect because $table_name is not
> user-entered. Or does $table_name need to be possi
> bly escaped?

Yes, UPDATE '.$quote.$table_name.$quote.' is fine.

Best,

 

[Jaroslav]

Hi,
I have problem with first line of hook function:
Code is in \include\custom_functions\operational_hooks.php:

[pre]
$hooks[$table_name]['update']['after'] = 'dadabik_update_username';
[/pre]

PHP Error: Undefined variable: table_name.

Any idea ?

 

[Jaroslav]

OK, I resolved it with "stupid" calculated function that I set up for field "updated by"
because I do not want to always run 2 updates

[pre]
function dadabik_return_logged_user($parameters_ar){
global $current_user;
return $current_user;
}
[/pre]