[Pugged] SQL Security

[ciapug@ciapug.org]

Stephen Langasek <vorlon@netexpress.net> said:

> On Wed, Sep 11, 2002 at 03:58:31PM -0500, Tim Perdue wrote:
> > Exploitable:
> > >       $sql = "SELECT * FROM frmForums WHERE frmID = $f";
> > Secure:
> > >       $sql = "SELECT * FROM frmForums WHERE frmID = '$f'";
> 
> Not secure.
> 
> $f = '1123\'; DROP TABLE frmForums; SELECT \'';
> $sql = "SELECT * FROM frmForums WHERE frmID = '$f'";
> 
> gives you
> 
> SELECT * FROM frmForums WHERE frmID = '1123'; DROP TABLE frmForums; SELECT '';
> 

Ideally, the web application is running as a SQL user that has limited permissions. So at least they can't drop tables or databases.

If you want to get even more fine-grained, you can limit some queries to use a SQL user that can only do selects, and can't update tables.

To avoid the example above to some extent, you can NOT pass form vars in the URL string... use form vars instead. Then you use the form vars array... and put something in the header to check to see that the referrer page came from your site, and not someone else's.

None of this is totally fool-proof... but it will help.

For more info, Google for "sql injection". Scary stuff.

If you keep an eye on MS-SQL server security advisories, a large # of them have to do with people using the SA account with no password. Then you can do cool stuff like run stored procedures that allow you to execute arbitrary commands on the SQL server's host OS.

Why do people do this? Because if you cut & paste example code out of the help files or MS KB articles with user="SA", pass="", so that's what they do. Learn by example, right?

-dc