April 22, 2012 at 2:40 am
Hello,
I wrot this simple code to retrive results in a table but I get an error
The code is:
Declare @UserName nvarchar(100)
Declare @userid Uniqueidentifier
set @UserName = 'yes'
SELECT @userid = [UserId] FROM [aspnet_Users] WHERE [UserName] = @UserName
declare @myTable table
(
RoleId uniqueidentifier
)
insert into @myTable
exec (N'select [RoleId] FROM [aspnet_UsersInRoles] WHERE [UserId]= ' + @userid)
select * from @myTable
this is the error:
Incorrect syntax near 'D3DA4E2'.
Notice:
the value 'D3DA4E2' is a part of the @userid which I got in the first statement
please explain the solution
Regards
April 22, 2012 at 3:24 am
Couple of points:
• Please verify if variable @userid has any special character that SQL Server uses for different purpose.
• Declare a variable @SQL and store the SQL command in it. It will help you in debugging the SQL string that is been generated at runtime.
• Use sp_executesql to minimize SQL injection risk.
April 22, 2012 at 4:36 am
Sorry, I missed that part. Please see the modified code below, it works on my system.
Declare @UserName nvarchar(100)
Declare @userid Uniqueidentifier
set @UserName = 'yes'
SELECT @userid = [UserId] FROM [aspnet_Users] WHERE [UserName] = @UserName
Declare @SQL nvarchar(1000)
set @SQL = N'select [UserId] FROM [aspnet_Users] WHERE [UserId]= ''' + cast(@UserID as varchar(50)) + ''''
declare @myTable table
(
RoleId uniqueidentifier
)
insert into @myTable
--exec (N'select [RoleId] FROM [aspnet_UsersInRoles] WHERE [UserId]= ' + @userid)
exec (@SQL)
select * from @myTable
April 22, 2012 at 5:39 am
There is absolutely no need to use dynamic SQL here. All you're doing by using dynamic SQL is making it more complex and less secure. There's also no need for two queries.
Declare @UserName nvarchar(100);
set @UserName = 'yes';
declare @myTable table (
RoleId uniqueidentifier
);
insert into @myTable (RoleID)
select uir.RoleId
FROM aspnet_UsersInRoles uir
inner join aspnet_Users u ON u.UserID = uir.UserID
WHERE u.UserName = @UserName;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2012 at 5:45 am
Perfect solution
Thank you so much
so I missed the 'cast' function and I think it works as you have used it in this example
Thanks Dev
April 22, 2012 at 5:54 am
Mando_eg (4/22/2012)
Perfect solutionThank you so much
so I missed the 'cast' function and I think it works as you have used it in this example
Please don't use dynamic SQL. You're just learning bad habits. See my non-dynamic example.
Dynamic SQL should be reserved for when the problem cannot be solved without dynamic SQL. This problem can.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2012 at 6:07 am
Hello GilaMonster,
in this example I don't get any inputs from the user I just get his username programmatically by code so I don't think he has a chance to inject a sql statements to hack the database, Am I right ?
I still in the beginning in the SQL Server so I don't have much experience to secure my DB
Thanks
April 22, 2012 at 6:11 am
Mando_eg (4/22/2012)
in this example I don't get any inputs from the user I just get his username programmatically by code so I don't think he has a chance to inject a sql statements to hack the database, Am I right ?
In this case, yes. But still, it's about learning to do things correctly from the start. If you are a beginner, you should be careful to learn good practices not bad. Using dynamic SQL where it's not required falls into the bad practices category.
Even if we ignore the security, the dynamic version is more complex and can have impact on database performance (via huge numbers of one-use adhoc query plans in cache)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2012 at 6:34 am
I'm 100% accept what you said, But
I'm a beginner and I don't know how to use Inner Join, Outer Join and such functions so I think I have to work on the simple way to understand how the functions works then I move the complex codes to save the time and work.
Thank you so much for your advices and your help
Regards
April 22, 2012 at 6:43 am
Happy I could help!
Though I agree with Gail on minimizing the use of Dynamic SQL, I do resist classifying it as ‘bad practice’. It’s very powerful to handle the scenarios that can’t be handled otherwise. You should understand how it works, with all pros & cons.
You would be biased on any options available to you if you don’t understand its pros & cons.
April 22, 2012 at 7:14 am
Mando_eg (4/22/2012)
I'm a beginner and I don't know how to use Inner Join, Outer Join and such functions so I think I have to work on the simple way to understand how the functions works then I move the complex codes to save the time and work.
To be honest then, you need to learn before you do any serious SQL work. Don't avoid the use of joins, they are absolutely critical to any querying that you do. If you try and work around joins because you don't know them, you will find that even the most trivial query is impossible for you to do.
http://msdn.microsoft.com/en-us/library/ms191517%28v=sql.100%29.aspx
http://www.tek-tips.com/faqs.cfm?fid=4785
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2012 at 7:31 am
Right, now let's take a look at how this works:
select uir.RoleId
FROM aspnet_UsersInRoles uir
inner join aspnet_Users u ON u.UserID = uir.UserID
WHERE u.UserName = @UserName;
Both tables have a UserID column. A row in the first table is said to match a row in the second table when the UserID they have is the same. That's what the INNER JOIN does. It says 'find the rows in aspnet_UsersInRoles and match them to the rows in aspnet_Users by using the UserID column.
Then the filter that is in the query eliminates all rows except the row (now consisting of columns from both tables) that has the specified userID.
That's not how SQL physically processes the operation (which is not important at your current level), but it's the logical query processing method.
Does that help a bit?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply