Updating lots of database fields in a single row

[William Blunn]

On 23/01/2013 11:09, Abigail wrote:
> On Wed, Jan 23, 2013 at 10:53:16AM +0000, William Blunn wrote:
>> On 23/01/2013 10:21, Jérôme Étévé wrote:
>>> Something critical is missing in your code though: quoting:
>>>
>>> Replace $field = '$hash->{$field}' with " $field
>>> =".$dbh->quote($hash->{$field})
>>>
>>> The DBI quote method will 'do the right thing to avoid screwing up
>>> your queries'. http://search.cpan.org/dist/DBI/DBI.pm#quote
>> We shouldn't be doing anything to encourage people to include variable
>> values directly into queries.
>>
>> If we feel we must mention quoting helper methods, this should be
>> clearly qualified with words to the effect that including variable
>> values directly into queries is considered poor practice, and best
>> practice is to use placeholders and bindings.
>
> I'd say that dogmas are poor practise.
>
>
> Good practise is actually *knowing* when you should use placeholders,
> and when there's no need.
>
> Because someone who knows can actually be trusted to do variable
> interpolation in places where placeholders cannot be used. Unlike
> someone who goes "variable interpolation is baaaaaaad".

A person asks a question. We give an answer. We can only say so much. A 
person can only take on board so much. So we have to make that answer as 
good as possible in the limited scope available.

Including values in the SQL is prone to error in a way which 
placeholders and binding isn't, especially for beginners.

You can't make an experienced Perl developer overnight. People need to 
learn things a bit at a time.

If people are to learn about the two approaches of (a) placeholders and 
binding, and (b) including values directly into the SQL; then I think 
it's best to teach placeholders and binding first.

If people never get around to learning the other approach, then perhaps 
their code won't be as ideal as it might otherwise have been. But at 
least their software won't suffer from the problems of interpolation.

So I say we should actively encourage placeholders and binding for 
people new to Perl and/or DBI programming. If people need to use 
interpolation, then they'll figure that out for themselves as they get 
more experience.

(Even if some approach might be "best" in some sense in a particular 
piece of code, there is still the question of how that code may be read 
by more junior developers and be used as a template for other pieces of 
code.)

Regards,

Bill