[thelist] SQL Update CORRECTION

[Sarah Sweeney]

Ken Schaefer wrote:
> 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've been wondering for a while exactly how an SQL injection attack 
would be done - I guess this answers it. Although I wonder - shouldn't 
the user account which the web site uses to access the database not have 
permissions to execute a trucate query? Also, in this case, wouldn't the 
query end up as (given 3 as in invoiceno):

UPDATE invoice SET total = ''1'; TRUNCATE TABLE invoice;--' WHERE 
invoiceno = 3

Looks like this query is going to fail anyway.

I guess I'm nitpicking a bit, and I'm sure that injection attacks are 
possible, even if I'm not convinced this particular one would work :) My 
question is, what is/are the best method(s) you and others would 
recommend for preventing these attacks? Do you have any recommendations 
for applications that do not use stored procedures?

-- 
Sarah Sweeney
Web Developer & Programmer
Portfolio :: http://sarah.designshift.com
Blog, etc :: http://hardedge.ca