[thelist] SQL Update CORRECTION

[David Landy]

Hi Ken,

> You have any number of issues with respect
> to SQL Injection attacks.

Really? How so? My code is (pseudo) like this:

if (total != oldtotal) {
    // field contents have changed
    sql = "UPDATE invoice SET total = '" + total + "'" WHERE invoiceno = " +
invoiceno;
    db.execute(sql);
}

What are you anticipating a user could type into an input box that would
compromise security?

I agree that stored procs are usually faster than ad hoc sql statements, but
how would you write a stored proc to update only those fields that have
changed? That was the original question. For starters, wouldn't it have to
take (n * 2) parameters, where n is the number of fields in the table?

Secondly, stored procs are not portable across different db's, whereas
standard sql updates are completely portable, so there is some method in my
madness (I know my db backend is going to change one-day-someday-soon).

> b) extracting schema information from an ADO Recordset object is an
> expensive operation - by doing this, I don't think you're saving
> anything if you're just looking at the difference between updating all
> fields -vs- updating a subset of fields + extracting schema
> information

Probably true. I wouldn't extract schema information at runtime in real
life, but it's theoretically possible. I prefer generating code by reading
the schema just once, but if that's too complex or overkill, people may
prefer to do the simpler thing and sacrifice some performance.

> c) I'm not exactly sure how you're lessened the server load - this is
> the bit that is confusing me. I see that you're performing quite a few
> more operations, each of which consumes more resources.

The database server load is reduced because only the fields that have
changed are updated. In average tables, though, perhaps you are right -- the
extra code to work out the query string may make it run slower in the end.
But perhaps not. Some performance testing is definately warranted!

To be honest, I'd never thought of this - in my last system, performance
wasn't even an issue (about 50 users access the site once a day, to make 2
or 3 updates) and I wanted to do the elegant and "correct" thing -- to
update only those field values that have changed.

Ideas, anyone?

David.
--