Re: SQL Injection - Stored Procedures - Updated

From: Bob Lehmann (nospam_at_dontbotherme.zzz)
Date: 02/18/05 

Date: Thu, 17 Feb 2005 17:46:59 -0700

Would it kill you to set your mail to wrap at a reasonable number of
characters? It is almost impossible to read your posts without resizing the
window.

Bob Lehmann

"Bã§TãRÐ" <nobody@msn.com> wrote in message
news:62aa11dh2rn54vfpkrnve9fukf8s1cgsl9@4ax.com...
> Its obvious Dave Anderson and I disagree on some things but thats OK. It's
what, makes this kind of forum the way it is. After some considerable
testing today on the nature of Stored Procedures and
> applying an Injection attack, I've exposed an exploit that isn't from a
buffer overflow or a security hole in the SQL Server application itself but
more along the lines of taking advantage of SQL and
> stored procs. The exploit deals with permission settings on the SP's
themselves. Now if you've been paying attention to this thread at all,
you'll notice that Dave limits the permissions on the SP's
> and I'm sure that some other DBA's follow suit. However, I am also sure
that there is a large portion of the world that doesn't, as you may have
noticed the spread of the Slammer virus using 'sa'
> password to propagate itself. Whether it be laziness, incompetence or what
ever, I'm sure there are literally thousands of server running millions of
SP's without any regard to permissions. With that
> in mind this makes anything you try a pretty likely event it will succeed.
But also bear in mind that certain things also need to be in place, like the
site has to be vulnerable to CSS attacks etc
> etc.
>
> I wont go into details but will more or less outline the beginning of what
I think is a proof of concept. Granted my knowledge of SP's is pretty
limited so bear with me:
>
> The proof of concept is as follows.
>
> I CSS the site exposing the name of a SP dealing with login information.
> get_user_login_info
>
> So, this particular SP will accept VARCHAR for username and password.
>
> Step 1:
> I've created a dynamic SQL statement that will create a Stored Procedure
with the same name of the one that was exposed.
>
> SET @var = 'CREATE STORED PROCEDURE.....'
> SET @vat + @var = 'SELECT * FROM INFORMATION_SCHEMA.TABLES'
> etc etc...
>
> Step 2: I've created a Delete SP statement that I will Inject into the SQL
> EXEC get_user_login_info 'delete procedure [dbo].[get_user_login_info]'
>
> Step 3:
> I fire the EXEC statement at itself telling to delete itself. (This
actually happened today while testing.)
> You can tell a SP to delete itself. Where the exploit per say comes into
play.
> This could easily be defeated by restricting permissions on the SP, but
bear in mind that my guess is most people wouldn't do that for any number of
reasons making it pretty readily available.
>
> Step 4:
> Continuing with the delete statement, you Execure the dynamic query that
you made earlier, after you delete the original stored Procedure.
>
> Because the web page is always calling the SP, you essentially deleted the
original SP, and created one of your own, with your own variables, and would
be able to access the SCHEMA tables or anything
> else for that matter via your dynamic SQL statement. The combination of
deleting and re-writing the SP would allow you access to anything in the
database. Again, all of your ducks would have to be in
> a row with permissions etc but again this is more of a proof of concept
anyhow.
>
>
> Any feedback?
> ~***
>
>
>
> On Thu, 17 Feb 2005 16:56:19 -0600, "Dave Anderson"
<GTSPXOESSGOQ@spammotel.com> wrote:
>
> >Bã§TãRÐ wrote:
> >>>> SQL Injection is most easily defeated using regular
> >>>> expressions to validate all of your data.
> >>>
> >>> Not really. I can use ADODB.Command and NEVER validate any data, and
> >>> still be 100% protected from SQL injection.
> >>>
> >> You have to realize that alot of the poeple here are not SQL experts.
> >> Most everyone learned SQL by creating these dynamically built SQL
> >> strings including myself. I've just recently started working with
> >> SP's.
> >
> >My comments directly rebut your "most easily defeated" assertion, and do
not
> >attempt to disparage data validation.
> >
> >As a side note, exact preservation of the original input data is
> >occasionally not only preferable, but *required* (regulatory or liability
> >reasons, for example). The requirement to preserve the original, in
effect,
> >forbids validation. So how are you going to protect yourself from SQL
> >injection if validation is off the table?
> >
> >
> >
> >> RegularExpressions are a great defense against SQL injection
> >> and a great way to validate data before it goes into the dB. Just
> >> because you validate your data in the SQL (if you even do that) just
> >> means you have to write more SQL and depending on what you're doing
> >> with it could slow things down.
> >
> >Again, I never said not to validate data before sending it to the
database.
> >I just don't think regular expressions should be your last line of
defense.
> >
> >
> >
> >> The #1 call tech support gets is What is my password? It is generally
> >> thought that to keeps costs and the amount of phone calls down,
> >> limiting the characters allowed in the PW field to numbers and
> >> characters was the user friendly way to handle it.
> >
> >This "hacker's wet dream" horrifies me on so many levels. Passwords are
> >inconvenient, so let's not make 'em too tough to remember.
> >
> >
> >
> >>> Easily defeated: http://livehttpheaders.mozdev.org/
> >>>
> >> So I suppose, you would just rather not do it?
> >
> >Yes. I would not do it. I would rather assume the user can change
*ANYTHING*
> >in the request.
> >
> >
> >
> >> I think its wrong to say Dont do something because
> >> its easily defeated or takes a special tool to do so.
> >
> >1. I do not consider Firefox extensions "special tools" as much as
browser
> >features, many of which will find their way into all manner of future
> >browsers.
> >
> >2. I think the use of HTTP_REFERER for security is a bad practice because
it
> >lends a false sense of security despite having no reliability whatsoever.
I
> >can absolutely think of good uses for it, but they *enhance* or
*supplement*
> >the user's experience rather than impede it.
> >
> >
> >
> >> Not all developers are dB admins. In fact is most case I've been
> >> involved with, the dB servers are run by other people, some smart
> >> (will limit who can run what SP, preventing escalation) and others
> >> are not (in one case cant even set up a FTP server properly) so it
> >> depens on the environment you're working in. Suggestion # 1 is a
> >> valid point but inpractical in my case 700-900 stored procedures and
> >> #5 I'm working on!
> >
> >If the developer is not a DBA, then someone else certainly is. And I
> >guarantee that DBA would like nothing better than removing SELECT, INSERT
> >and UPDATE from the web application's login -- and almost certainly would
> >prefer to be an essential part of the change management/migration
process.
> >
> >And FWIW, db_securityadmin is sufficient in SQL Server for the developer
to
> >manage SP permissions. DBA is overkill.
>