[thelist] SQL Update CORRECTION
Ken Schaefer
ken at adOpenStatic.com
Tue Jul 13 07:55:06 CDT 2004
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
More information about the thelist
mailing list