CODEWITHSUNDEEP
sqlt-sqlstored-procedures
xxyyxx
xxyyxx

Reputation: 2396

Alternatives to using dynamic sql

I have a sp which takes input @featuretype. @featuretype will be equal to either "mobile", "login", or "index", and will correspond to a column in the db.

In my sp I have:

EXEC(
    'select TOP 3 * from featuredtypes_v where'+' featuredtypes_v.'+@featuretype+'Page=1'+
    ' order by featuredtypes_v.priority desc'
    )

However, I've been told this opens up the db to a sql injection. My two questions are, why is this, and how else can I write this query in order to avoid this?

Upvotes: 9

Views: 2676

Answers (4)

Alen.Toma
Alen.Toma

Reputation: 4870

You all not answering the quation guys. here is somthing sql injection could happend with

txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;

now if txtUser = 105 OR 1=1 then the sql statment will be like this 

SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1;

you avoid sql Injection by using sql parameters. it validate the string and the sql execute alwaus like it suppose to

Upvotes: 0

Farhan
Farhan

Reputation: 2575

Why don't you use case?

select TOP 3 * 
from featuredtypes_v F
where
    case
        when @featuretype = 'mobile' then F.MobilePage
        when @featuretype = 'login' then F.LoginPage
        when @featuretype = 'index' then F.IndexPage
    end
    = 1

Upvotes: 7

TheTechGuy
TheTechGuy

Reputation: 17354

One approach would be this. Make sure the column exists in the table, then execute dynamic sql, otherwise not.

if Exists(select * from sys.columns where Name = N'@featuretype'  
            and Object_ID = Object_ID(N'tableName'))
begin

   EXEC(
    'select TOP 3 * from featuredtypes_v where'+' featuredtypes_v.'+@featuretype+'Page=1'+
    ' order by featuredtypes_v.priority desc'
    )

end

Upvotes: 2

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32690

Your procedure is open to injection if the user supplies the value passed into the variable, or if someone finds a way to execute the stored procedure passing in specially-crafted, malicious code. Google my user name for an amusing comic based on this.

Since you're in a stored procedure, you can check the variable, and then do your SELECT statement based on the variable supplied:

IF @featuretype = 'mobile'
BEGIN
    select TOP 3 * 
    from featuredtypes_v 
    where featuredtypes_v.MobilePage=1
    order by featuredtypes_v.priority desc
END
IF @featuretype = 'login'
BEGIN
    select TOP 3 * 
    from featuredtypes_v 
    where featuredtypes_v.LoginPage=1
    order by featuredtypes_v.priority desc
END
-- etc...

Alternately, you can put the criteria in your WHERE clause in one query:

select TOP 3 * 
from featuredtypes_v 
where (featuredtypes_v.MobilePage=1 AND @featuretype = 'Mobile') OR 
    (featuredtypes_v.LoginPage=1 AND @featuretype = 'Login') OR
    (featuredtypes_v.IndexPage=1 AND @featuretype = 'Index')
order by featuredtypes_v.priority desc

Upvotes: 3

Related Questions