[thelist] SQL Update CORRECTION

Ken Schaefer ken.schaefer at gmail.com
Tue Jul 13 18:46:44 CDT 2004


On Tue, 13 Jul 2004 12:07:31 -0300, Sarah Sweeney
<mr.sanders at designshift.com> wrote:
> 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.

Yes, you are correct - I supplied an extra '. Remove the extra ' and
the query runs.

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

There are plenty of other, bad, things that SQL Injection attacks can do.

For example, I can bypass a login screen if your query looks like:

SELECT UserName FROM Users WHERE Username = '" + username + "' AND
Password = '" + password + "'"

by supplying: ' OR 1=1-- as my username.

Or, I could add a new user, by appending an INSERT INTO query to the
end of the SELECT query above.

Or, consider the following:

SELECT CustomerName, InvoiceTotal FROM Invoices WHERE InvoiceID = " + number

What if I supplied:

1 UNION SELECT UserName, 1 FROM Users
-and-
1 UNION SELECT Password, 1 FROM Users

as the invoiceID? I would get a list of usernames and passwords
appended to the end of the result-set (which, presumably, would be
displayed on the screen).

SQL Injection attacks are nasty if you're application hasn't got it's
bases covered.

If you want some good reading material, check out:


http://www.nextgenss.com/papers/advanced_sql_injection.pdf
and
http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf


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


The OWASP Web Security guide is good reading. That's a platform
independent security guide available from www.owasp.org

For .NET, you can get the Building Secure ASP.NET applications book
from Microsoft for free:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/secnetlpMSDN.asp?frame=true

Cheers
Ken


More information about the thelist mailing list