sql injection articles
-
I would be curious to know the sql injection defense that sql server central uses. Especially considering this forum is full of posts with things like, drop this table, xp_cmdshell that etc etc.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Not that we're the most dilligent, but we don't have any real information about anyone on this site (no SSNs, credit cards, etc). Any billing we do for http://www.sqlserverstandard.com is outsourced so we don't hold it. You shipping address goes in another write only table, so it's fairly secure.
The user that connects to the db has limited rights (it's not sa!), and we upgraded these forums to ASP.NET with lots of stored procs, which helps to limit the damage that injection can do.
That being said, a thorough secuity audit would be a great thing if we could ever find the time
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Thanks I was mostly concerned with beefing up my sql injection defense. My current sites are exclusivly on stored procedures, no dynamic sql from asp at all.
I check the parameters client side and then check them again server side before I add them to the procs param list. I felt fairly secure. Then I read a few messages on this other site that was dedicated to penetration testing and my hair stood on end. It seemed that all that was doing was not enough
I'll keep working on it.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Hey, post your suggestions, results. Or better yet, write an ariticle or two about what you've done
(articles@sqlservercentral.com)Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
I'll be migrating to a new server soon, and I have spent a greate deal of time thinking about my availability,monitoring,recovery, and security plan. I will try to find the time to consolidate my notes into something reasonably legibility.
cheers
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
SQL Injection is only an issue if you are using dynamic SQL. The only real place you should use this is to perform performant searching pbased on lots of optional criteria, and then it is only an option.
Great article lineked from http://www.sqlserverfaq.com/content/forum/MsgForumFrame.aspx?P1N=FID&P1=37&PN=1&STXT=Y&R=N&GUID=836A7C51-34EA-48EC-A1BC-235AA1579128&MID=276260&BCK
on dynamic sql
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
Im not entirely sure about that simonsabin. That was my assumption but then I found this post on:
http://seclists.org/lists/pen-test/2001/Mar/0105.html
Here is the relevent section that challenged my assumptions:
> So, i can inject statements into the stored proc call, but
> not into the select calls inside it, as using 's and the like is
> not "carried" in to the procedure. An example:
>
> PROC_FOOBAR 'par1', 'par2', 'par3'
>
> inside this procedure is the real select call... but my input
> forms are par1, par2 and par3. I can inject stuff, passing an
> input of:
>
> par1 = foo', 'foo2', 'foo3'; [not parsed correctly by forum see article above]
>
> The foo2 and foo3 are just so that the stored proc doesn't
> complain that it hasnt been passed all the parameters. That, as
> would be expected, comes out to be:
>
> PROC_FOOBAR 'foo', 'foo2', 'foo3'; [not parsed correctly by forum see article above] 'par2', 'par3'
>
> So, i can effectively inject code after the PROC_FOOBAR call,
> including selects and execs of other stored procs.
If you read the conversation at the link, he does go one to describe the rest of the challenges such as the ASP page only expecting one recordset etc.
So according from this poster he was able to inject via a stored procedure. I havn't actualy tested his approach and it could be that he is only assuming that his injection will be parsed as he expects. It could be that the proc will just treat his injection as a literal and not do anything harmful.
I just don't know, and thats the problem
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
lol well if nothing else his injection code was enough to stump the forum parser, I had to snip part of his code out to have it display correctly. The forum was reading the "select" in his injection as an html select and it mangled the display.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
In the example the person is building a call to an SP in dynamic SQL whether it is in TSQL or VB it is still putting strings together thus the problem. If you are using ADO and a command object with parameters then SQL is passed to the SQL engine slightly differently each parameter value is identified to the engine directly. i.e. @parameter1 = myvalue. You also benefit from typing, i.e. you can't put a string in an integer parameter.
if you look at the command text of the command object you will see
{? call mysp ?, ?, ?}
each ? represents a parameter (the first being the return value)
There is no way using this that you can inject code. (given the sp doesn't use dyanmic sql)
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
The other benefit of this is that it is the most performant way of calling an SP. The mechanism allows the SQL engine to very efficiently produce the parameterised query that is used to determine reuse later on. i.e. two calls using this method are more likely to reuse the sam equery plan. Unlike two calls where the SQL is built dynamically.
You can also do this with normal SQL to avoid injection
use a command text of 'select * from orders where orderid = @orderid' and create a parameter called @orderid and set the value.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
Hmm I assumed that his notation was simply shorthand and that the web form was using parameter ado objects...I should reply to his thread.
It does sound like your saying with confidence that if I use ADO parameter objects to pass my param's then sql would not be fooled by any form of injections.
For example: if I had a web form field called 'comment' and someone entered
'blah blah; drop table snee'
So although my @comment ADO parameter object would accept this as a valid varchar and pass it to the sp; the sp would only parse it as a literal varchar and not fall prey to any form of injection.
I just did some quick test and this seems to be true even when calling the proc as:
exec someProc @param1='this;drop table table1'
Well thank you that is a relief.
I use paramertized stored procedure almost exclusivly and I had long assumed this to be the case but that pen test article had me a bit shaken. Dave
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Try this to drop the table the user enters
';drop table table1;print'
exec someProc @param1='';drop table table1;print''
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
Ya that failed to inject as well. Looks like I am reasonably secure the way I am doing things.
I do validate my forms both client side and server side, and use paramertized sp's with a low permission account...is there some other vunerability I am not aware of when calling sp's from ADO?
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Not that I am aware of
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
If you're using paramerized stored procedures, this probably goes without saying, but just to be safe... ensure the permissions are issued only against the stored procedures and take advantage of ownership chaining so you don't have permissions on the base tables/views. Reason being if permissions are granted against base tables and views an attacker could potentially query them directly should the web server get compromised. Granted such an attack is no longer SQL Injection, but it's something to keep in mind when dealing with any app but especially web-based ones.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply