[thelist] SQL Update CORRECTION

[Ken Schaefer]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "David Landy" <davidlandy at clara.co.uk>
Subject: Re: [thelist] SQL Update CORRECTION


: 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?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Try this (for the simplest of attacks, but also the easiest to detect):

<%
total = "'1'; TRUNCATE TABLE invoice;--"

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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: 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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I would probably write a sproc to update all fields. For a small project
it's not worth doing it any other way. That's what I'm saying.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: 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).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

a) inline SQL is a nightmare to maintain
b) inline SQL does not give you good performance
c) inline SQL is vulnerable to SQL Injection attacks
d) stored procedures give you access to a lot of features that inline SQL
doesn't
e) SQL isn't entirely portable - the basic stuff is, but anything remotely
fancy isn't (eg SELECT @@IDENTITY)
f) If you're worried about portablity, you should develop loosely coupled
systems (and I would argue - still keep your sprocs). Sure, you'd need to
rewrite your procedures in another DB, but the app itself doesn't need
changing.

Cheers
Ken