Using a stored procedure to select columns?

Question

Post reply

Using a stored procedure to select columns?

Viewing 5 posts - 16 through 19 (of 19 total)

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

sivaj2k Ten Centuries Points: 1188 More actions · Answer 1

Hi Paul,

What happens if I pass the value '1; DROP TABLE EMP; --' as the parameter?

--------------------------------------------------------------------------------

Here is an example.

CREATE TABLE [dbo].[test](

[id] [int] NOT NULL,

[dob] [nvarchar](max) NULL,

[test] [nvarchar](50) NULL

)

insert into test values (1,'03-11-2010','math')

insert into test values (2,'04-11-2010','science')

insert into test values (3,'05-11-2010','reading')

create PROCEDURE Dynamic_Query_Example (@p_category varchar(20))

as

begin

declare @sql_query varchar(max), @p_category_select varchar(max)

SELECT @SQL_QUERY = ' SELECT '+@P_CATEGORY+' FROM test'

print @sql_query

exec (@sql_query)

end

Query1 : exec dynamic_query_example 'dob'

output:

03-11-2010

04-11-2010

05-11-2010

Pl revert if any issues in above example.

Thanks

Siva Kumar J.

Paul White SSC Guru Points: 150467 More actions · Answer 2

sivaj2k (10/14/2010)
In this case sql injection is not going to occur.

Why not? Try:

exec dynamic_query_example '1;DROP TABLE TEST;--'

with your example code? It drops the test table!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sivaj2k Ten Centuries Points: 1188 More actions · Answer 3

Hi

May be this one is better solution.

CREATE PROCEDURE [dbo].[Dynamic_Query_Example] (@p_category varchar(20))

as

begin

declare @v_count int

declare @sql_query varchar(max), @p_category_select varchar(max)

select @v_count = count(*) from information_schema.columns where table_name = 'test' and column_name = @p_category

if @v_count >0

begin

SELECT @SQL_QUERY = ' SELECT '+@P_CATEGORY+' FROM test'

end

else

begin

print 'Invalid column name'

end

print @sql_query

exec (@sql_query)

end

Thanks

Siva Kumar J.

Paul White SSC Guru Points: 150467 More actions · Answer 4

sivaj2k (10/14/2010)
May be this one is better solution.

Nope.

CREATE SCHEMA InjectionAttack

CREATE TABLE Test ([1;DROP TABLE TEST;--] SQL_VARIANT NULL);

GO

EXECUTE dbo.Dynamic_Query_Example '1;DROP TABLE TEST;--';

...still drops the Test table!

Review the information in the link below.

Books Online: SQL Injection

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sivaj2k Ten Centuries Points: 1188 More actions · Answer 5

Hi Paul,

Thats great. Can u pls give me the best solution to come over the problem.

Thanks

Siva Kumar