perl DBI placeholder update stmt issue-CodePudding

A dbi placeholder has been used in the below sql 'update' queries to avoid sql injections recommended by perl doc. while testing , I noticed the new queries not working properly. I have been looking at stack-overflow and other recourses to identify if anything is missing or not.

#1
$sth = $dbh->do("UPDATE $dbstore SET Classes='" . $c_classes . "', zzzz='" . $c_zzzz . "', Timestamp='" . $timestamp . "' WHERE Host='" . $h_host . "'"); #old - working as expected
#$sth = $dbh->do("UPDATE $dbstore SET Classes=? , zzzz=? , Timestamp=? WHERE Host=? ", undef, $c_classes, $c_zzzz, $timestamp, $h_host); #new

#2
$sth = $dbh->do("UPDATE $dbstore SET Classes='" . $c_classes . "', zzzz='" . $c_zzzz . "', Timestamp='" . $timestamp . "' WHERE Host='" . $h_host . "'"); #old - working as expected
#$sth = $dbh->do("UPDATE $dbstore SET Classes=? , zzzz=? , Timestamp=? WHERE Host=?", undef, $c_classes, $c_zzzz, $timestamp, $h_host); #new

#3
$sth = $dbh->do("UPDATE $dbstore SET Warning='" . $st_warning . "' WHERE IDSID='" . $remote_user . "' AND Host='" . $st_host . "'"); #old - working as expected
#$sth = $dbh->do("UPDATE $dbstore SET Warning=? WHERE IDSID=? AND Host=?", undef, $st_warning, $remote_user, $st_host); #new

#4
$sth = $dbh->do("UPDATE $dbstore SET Warning='" . $st_warning . "' WHERE IDSID='" . $remote_user . "' AND Host='" . $st_host . "'"); #old - working as expected
#$sth =  $dbh->do("UPDATE $dbstore SET Warning=? WHERE IDSID=? AND Host=?", undef, $st_warning, $remote_user, $st_host); #new

#5
$sth = $dbh->do("UPDATE $dbxxxx SET Nodes='Submitted (" . $timestamp . " by " . $remote_user . ")', Configuration='" . $st_configuration . "' WHERE Host='" . $st_host . "'"); #old - working as expected
#$sth =  $dbh->do("UPDATE $dbxxxx SET Nodes='Submitted (? by ?)', Configuration=? WHERE Host=?", undef, $timestamp, $remote_user, $st_configuration, $st_host); #new

#6
$sth = $dbh->do("UPDATE $dbxxxx SET Classes='" . $n_classes_update . "' WHERE Host='" . $st_host . "'");  #old - working as expected
#$sth =  $dbh->do("UPDATE $dbxxxx SET Classes=? WHERE Host=?", undef, $n_classes_update, $st_host); #new

based on my understanding and reading (might be wrong), there is no need to surround the variables with a single/double quote. Is this a true statement?

I am most suspicious to query number 5 'Submitted (? by ?)' part. would having the ? inside single quote cause a problem in this case? if yes, what is the recommended solution?

Do you by chance see any issue on other queries?

Thanks

CodePudding user response：

You've got it, #5 is the problem.

$dbh->do("UPDATE $dbxxxx SET Nodes='Submitted (? by ?)'", $timestamp, $remote_user)

The above sets the string Submitted (? by ?).

A placeholder is for a single SQL value. You build the string and pass it in.

You'll also want to use quote_identifier on the table name. This will quote and escape any identifiers, columns and table names, to avoid SQL injection that means.

my $q_table = $dbh->quote_identifier($dbxxxx);

$dbh->do("UPDATE $q_table SET Nodes=?", {}, "Submitted ($timestamp by $remote_user)");

Note that you should have gotten a warning or error about passing in too many arguments to the statement (I could be wrong, it's been a long time since I've used DBI), so you'll want to investigate why that did not happen. If at all possible, turn RaiseError on so you get these SQL failures as errors immediately.

my $dbh = DBI->connect(
  $data_source, $username, $password, { RaiseError = 1 }
);

SQL design note. Avoid storing formatted data. It makes searching slow and complicated, increases table size, and limits your output formatting.

Format it on the way out. For example...

update some_table set submitted_at=?, remote_user=?

select concat('Submitted (', submitted_at, ' by ', remote_user, ')')
from some_table;