Injection Attack - Guru Needed.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Injection Attack - Guru Needed.

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2007-01-19 : 11:13:26

Hello all,

I have a question on whether the following stored precedure would be open to an SQL Injection attack. Assume that a string query would be passed to the SP.

I am told that because the password parameter is only varchar(8) that it is safe.

Can someone prove this wrong?

Thanks....

I have added sample code below.


CREATE TABLE [dbo].[JB_Test](
	[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_JB_Test_Name]  DEFAULT (''),
	[Email] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_JB_Test_Email]  DEFAULT (''),
	[Password] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_JB_Test_Password]  DEFAULT (''),

) ON [PRIMARY]
GO

Insert dbo.JB_Test (Name, Email, Password )
values ('John', 'asdf@asdf.com', '2345')
Insert dbo.JB_Test (Name, Email, Password )
values ('Paul', 'asdf@asdf.com', '2345')
Insert dbo.JB_Test (Name, Email, Password )
values ('Geroge', 'asdf@asdf.com', '2345')
Insert dbo.JB_Test (Name, Email, Password )
values ('Ringo', 'asdf@asdf.com', '2345')

GO

Create procedure dbo.JB_Test_Login

@Username varchar(100),
@Password varchar(8)

AS

select Name
from dbo.JB_Test
where Name = @Username
and Password = @Password
GO


--Clean Up Your Mess
--Drop procedure dbo.JB_Test_Login
--GO

--Drop Table dbo.JB_Test
--GO

JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
• Position SEO can provide your company with SEO Services at an affordable price
› As far as myself... I do this for fun!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 12:18:48

It is never a good idea to build dynamic SQL on client side. You can always add a "; SELECT * FROM Users" or similar.

This is one safe way to go with parameters
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70783

Peter Larsson
Helsingborg, Sweden

JBelthoff
Posting Yak Master

173 Posts

Posted - 2007-01-19 : 13:50:00

Yes I understand that but I am looking for a particular attack on this query. Sort of to win a bet. Person A says that because the password is only varchar(8) than it is immune from SQL Injection.

So what I am looking for is an Sql Injection that can attack with a string that is 8 chars or less.

Did that make sense?

JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
• Position SEO can provide your company with SEO Services at an affordable price
› As far as myself... I do this for fun!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-19 : 14:07:07

That stored procedure is not open to an injection attack because it isn't using dynamic SQL, the length of the password has nothing to do with it.

JBelthoff
Posting Yak Master

173 Posts

Posted - 2007-01-19 : 14:15:49

Are you saying that the SP above is not exposed to Sql Injection?

JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
• Position SEO can provide your company with SEO Services at an affordable price
› As far as myself... I do this for fun!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 14:23:13

One can always try to add

or 1 =1 ;

in the password text boxes and see what happens...

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-19 : 14:26:38

Correct - in order to be open to injection it would need to be doing something like this

declare @sql varchar(2000)
set @sql = 'select Name
from dbo.JB_Test
where Name = ''' + @Username + '''
and Password = ''' + @Password + ''''
exec(@sql)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-19 : 14:32:03

If someone passed a query into the @Password variable, then it would look like this:

select Name
from dbo.JB_Test
where Name = 'SomeUser'
and Password = 'SomePassword; DELETE FROM SomeTable;'

So all that would happen is that the query would return 0 rows since there is no row that has a password of "SomePassword; DELETE FROM SomeTable;".

Tara Kizer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 14:46:33

Even if typing

' or 1 = 1; select '

???

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-19 : 14:52:45

Yes. It still will just be a string. You need dynamic SQL inside a stored procedure in order to do bad things.

Tara Kizer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 15:18:45

I think you are wrong Tara. See these four examples.
I used sp_configure for two reasons

1) The stored procedure takes two parameters just as an ordinary login would have
2) As far as I can tell, there is no dynamic sql in that procedure

use master
go

declare	@username varchar(100),
	@password varchar(100),
	@sql varchar(8000)

-- normal operation with fully dynamic built query
select	@username = 'l',
	@password = '0'

select	@sql = 'select * From master..spt_values where type = ''' + @username + ''' and status = ''' + @password + ''''

print @sql
exec(@sql)

-- injection with fully dynamic built query
select	@username = 'l',
	@password = ''' or ''1'' = ''1'

select	@sql = 'select * From master..spt_values where type = ''' + @username + ''' and status = ''' + @password + ''''

print @sql
exec(@sql)

-- normal operation with dynamic use of stored procedure
select	@username = 'show advanced option',
	@password = '1'

select	@sql = 'sp_configure ''' + @username + ''', ''' + @password + ''''

print @sql
exec(@sql)
print ''

-- injection with dynamic use of stored procedure
select	@username = 'show advanced option',
	@password = '1''; select * from spt_values where ''1'' = ''1'

select	@sql = 'sp_configure ''' + @username + ''', ''' + @password + ''''

print @sql
exec(@sql)

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-19 : 15:20:49

quote:
Originally posted by Peso

I think you are wrong Tara. See these four examples.

You are using dynamic SQL everywhere, so of course that works. The poster's stored procedure isn't using dynamic SQL, so it's not vulnerabe.

Tara Kizer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 15:24:22

sp_configure does not use dynamic sql.
And if you check my code, the only thing I change between calls is the text in the "password text box".
It all depends what the dynamic sql is built of.

Above I have proved that is does not matter if application build the select from scratch or calling a stored procedure. You can always type text in password text box to do exactly was I have done above.

As long as the call is dynamic at all, the risk is very present.
The only way to be safe is to use the command object as demonstrated in the link I provided earlier.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 15:26:32

quote:
Originally posted by tkizer

The poster's stored procedure isn't using dynamic SQL, so it's not vulnerabe.

No, not the stored procedure itself.
But as long as the parameters used for the stored procedure is executed with dynamic sql on the client side.

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-19 : 15:27:52

I didn't say sp_configure uses dynamic SQL. Your examples are using dynamic SQL and therefore are at risk.

Tara Kizer

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-19 : 15:29:12

The poster didn't say how they are building their call to the stored procedure, so I can't make that leap of faith that their code is at risk until I see the actual code. What the poster has posted so far is correct.

Tara Kizer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 15:30:31

I think we are talking about the same thing.
It is the client side that exposes the injection risk, not the stored procedure.

I have seen so many web pages, at client sites, calling the login stored procedure with dynamically built sql queries.

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-19 : 15:33:19

JBelthoff,

Your stored procedure is fine how it is. In order to say that your application isn't vulnerable though, we would need to see that code (the part associated with the parameters, calling the stored procedure, etc...).

Tara Kizer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 15:39:41

quote:
Originally posted by JBelthoff

Assume that a string query would be passed to the SP.

I did this in my code above (I used sp_configure instead). Your SP is fine and safe, but the client side can have problem executing string to call a stored procedure.

Peter Larsson
Helsingborg, Sweden

JBelthoff
Posting Yak Master

173 Posts

Posted - 2007-01-19 : 16:09:33

quote:
Originally posted by tkizer

JBelthoff,

Your stored procedure is fine how it is. In order to say that your application isn't vulnerable though, we would need to see that code (the part associated with the parameters, calling the stored procedure, etc...).

Tara Kizer

The application code does not use the command object and it uses a string to send the quey such as: dbo.SP_Name Variable1, Variable 2

My question is if someone enters a string into the variable without using the command object is it vunerable?

JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
• Position SEO can provide your company with SEO Services at an affordable price
› As far as myself... I do this for fun!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-19 : 16:14:03

Yes!

Check out the link Peso posted earlier.

Tara Kizer

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources