SQL Injection Attacks

Question

SQL Injection Attacks

David.Poole

SSC Guru

Points: 75994
More actions
May 22, 2003 at 9:39 am

#151839

quote:
I'm sure you know this articles from http://www.appsecinc.com named Manipulating Microsoft SQL Server Using SQL Injection. Or Advanced SQL Injections in SQL Server Applications by http://www.ngssoftware.com.
I understand the principle of the above article, I just don't understand how someone could submit the commands to the server unless they already had access to the server. Can anyone throw some light on this?

LinkedIn Profile

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

You're piggy-backing on legitimate access. In other words, a database call that is authorized is being made. You jump in on this call and add to it.

Think of it in these terms: I have a building that requires badge access. I wait for someone to badge in, catch the door before it closes, and I'm in. The reason the door opened was because someone with legitimate access badged in. Because said person didn't make sure the door closed properly with no one else slipping through, I'm in.

With SQL Injection, the input coming in to a web page (such as GetInfo.asp, for instance), isn't properly checked. Imagine where GetInfo.asp gets a field, ID. So the link looks something like:

http://www.myserver.com/GetInfo.asp?id=7

And the code just takes the 7 and does something similar to:

SELECT * FROM Users WHERE ID = ?

And the ? is of course replaced by whatever is coming in. If I append the following (I'll use regular characters instead of URL encoded):

http://www.myserver.com/GetInfo.asp?id=7;EXEC sp_addlogin 'MyLogin', 'MyPassword'

SQL Server will actually get the following passed to it:

SELECT * FROM Users WHERE ID = 7

as well as

EXEC sp_addlogin 'MyLogin', 'MyPassword'

The attacker is using a legitimate connection and hitching a ride.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

By the way, an excellent web-cast that's a step-by-step walk through on how an attacker scopes out and attacks a box using SQL Injection:

http://www.microsoft.com/usa/webcasts/ondemand/1765.asp

This was presented by SQLServerCentral.com's Brian Knight. Best SQL Injection web cast I've seen (and I've seen quite a few).

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Robert W Marda SSChampion Points: 13413 More actions · Answer 3

That was my question too. It looked like to use those injection attacks the user would have to have access similar to Query Analyzer. What is the best way to prevent this kind of injection attack? Are there ways to make sure the door closes before the injected SQL can get attached?

Robert W. Marda

SQL Programmer

bigdough.com

The world’s leading capital markets contact database and software platform.

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

Obviously, you lock down rights as much as possible.

If the developer isn't doing proper input validation and isn't using proper coding technique, there isn't a whole lot you can do. The semi-colon (;) and the double dash (--) really kill you. For instance, you can't stop the fact that this gets passed:

EXEC usp_mystoredprocedure 'Test';EXEC sp_password @loginame='sa', @new='password'

SQL Server is going to read that semi-colon as a statement separator and break up the two statements. So if you tried to test in usp_mystoredprocedure, it does no good. Of course, with the proper security model, sp_password doesn't get executed. But whatever the user has the ability to do can be done. So as a DBA, you are heavily reliant on the developer.

I'll be writing a security article on input validation that describes a real world issue I came across that dealt with a similar type of problem.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hi David,

quote:
I understand the principle of the above article, I just don't understand how someone could submit the commands to the server unless they already had access to the server. Can anyone throw some light on this?

just came across the thread. Maybe someone else above has already mentioned, but here are some of my thoughts.

Consider a website where you validate yourself as legitimate user via a login page. This could be a great starting point for sql injection. If validation of user input AND error handling in your app is not properly, sql injection is not that hard to do, meaning you do not have to be an bitkid. Most of the ADO Error Messages are very descriptive.

In my apps I don't use Adoconnection.Execute("SELECT....")

If it comes to database access first thing is proper validation at app level.

If validation fails, nothing happens. Next the input is passed to a Stored Proc. This means, better control on privileges, execution and so on. Here plugs in my original question whether you can use nested stored procs to validate input at a db level.

In addition to Brian I would mention the single ' mark. When I was new to SQL Server it had given me plenty of headache until I recovered that all I need to do is replace ' by ''.

I don't know exactly how to prevent SQL Injection. If your db is exposed to web you must make a trade-off between security and performance by definition. I think the articles I've posted are full of good advices.

Has anyone ever been injected?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Another great site to keep up to date with security issues is http://www.securityfocus.com . They offer also a great variety of mailing lists

Cheers,

Frank

Edited by - a5xo3z1 on 05/26/2003 08:46:14 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

quote:
Has anyone ever been injected?

While I haven't, I have done security consulting after the fact. A friend of mine owns an ISP and he had a customer complaining that my friend's servers were insecure because data was appearing and disappearing in his database unexpectedly and not as his application would handle it. My friend, security paranoid that he is, knew the guy was running an ASP-based site using SQL Server as a back-end. My friend also knew the web server and the SQL Server were secure. So he called me in.

It only took about two minutes of looking to see that his code was vulnerable to SQL Injection. I sent to my friend the links from NGSSoftware as well as a sample couple of links that demonstrated a successful SQL Injection attack on the web site. He quickly passed this on to the person in question.

Like I said, as a DBA, you're really hand-cuffed if the developer doesn't build the application securely. Hence the reason for code reviews. Pair programming, a la Extreme Programming, ain't a bad practice, either, so long as one of the programmers is versed in defensive programming.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Hi Brian,

quote:
Like I said, as a DBA, you're really hand-cuffed if the developer doesn't build the application securely. Hence the reason for code reviews. Pair programming, a la Extreme Programming, ain't a bad practice, either, so long as one of the programmers is versed in defensive programming.

not only SQL Security or app security is relevant. If you're talking about ASP ISP one really huge security hole is the provider himself and his knowledge about the Windows OS he is using. I have a script utilizing the FileScriptingObject I used to test my provider and he fails the test. If it is wanted I will post the script (of course, only for demonsration purposes only!!!!)

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

geoff_ba Grasshopper Points: 17 More actions · Answer 9

As a developer-cum-dba, my training (and unfortunate self experience) has proven that all database access should be done with command objects and stored procedures, and not dynamic SQL. This prevents the SQL injection attacks, and gives you better application performance and maintainability as well.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 10

quote:
all database access should be done with command objects and stored procedures, and not dynamic SQL

Indeed. Unfortunately, there's a ton of code out there that isn't using Command objects. That was the root of the recommendation I made for my friend to pass on.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 11

quote:
If you're talking about ASP ISP one really huge security hole is the provider himself and his knowledge about the Windows OS he is using. I have a script utilizing the FileScriptingObject I used to test my provider and he fails the test.

Another indeed. Any well-known web server is vulnerable straight out of the box. The IIS Lockdown Tool is a start. It is not the cure-all. However, if sysadmins run it, it'll eliminate most all of the vulnerabilities script kiddies are going to target with their pre-built and downloaded programs.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Frank Kalis SSC Guru Points: 111183 More actions · Answer 12

Hi Brian,

quote:
Another indeed. Any well-known web server is vulnerable straight out of the box. The IIS Lockdown Tool is a start. It is not the cure-all. However, if sysadmins run it, it'll eliminate most all of the vulnerabilities script kiddies are going to target with their pre-built and downloaded programs.

some time ago I had a discussion with our network admins on vulnerabilities. Correct me, if I'm wrong. What I remember from this was:

With an out of the box Windows2000 installation there are not specific user permission installed, that means the users can do everything unless he is denied this privilege. Now, if that (even partially) is true, I'd prefer the *NIX approach to deny a user everything unless he is granted permission to.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 13

quote:
With an out of the box Windows2000 installation there are not specific user permission installed, that means the users can do everything unless he is denied this privilege.

No, this isn't exactly correct. While the default permissions on the file system is Everyone - full control (although this gets tightened automatically in some cases, such as when you promote to a DC), users don't have full admin rights. They have normal user rights. This is true, of course, if they have a login to the system. Then they can access files but they can't do things like change network settings, stop/start services, change system properties, etc.

Servers tend not to be an issue. The reason is because in order to gain access to the entire file system, one has to be able to log on locally (no shares for non-admin users by default). If they can, that's a physical security domain issue. Of course, if the user can physically get to the server, you're system is pretty much compromised right there (just as would be on most any OS).

Workstations, on the other hand, can be. Join it to a domain and by default any authenticated user can log on (but then we've passed the definition of out of the box). There should be appopriate lock-down policies whether formal procedures or group policies or what-have-you from that point forward.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Frank Kalis SSC Guru Points: 111183 More actions · Answer 14

Hi Brian,

thanks for correcting me!

With out of the box installation the normal user has at least enough rights to crash his box ultimately

What I take from this, is that you need highly-skilled admins to get this job efficiently done. So you have three risk factors, the software, the hardware, and the 'human factor'.

Nonetheless, I'd prefer the SQL Server (and *NIX) approach to deny the normal user 'anything' unless it is explicitly granted.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]