October 23, 2006 at 2:57 pm
I am creating a SP to validate users to an area, each area is defined in the UsersTable as a bit. (Non-relational because there is only 4 areas) Anyway, the SP has two variables to receive the user id, and the area acronym. The user id contains a '\' for active directory logons. The SP will compile, but when executed, it fails. The issue is line 2, as I have commented it out and added the WHERE clause to line 3, and it works fine
Set @strSQL = 'SELECT FullName as UserFullName FROM Users'
Set @strSQL = @strSQL + ' WHERE USERID = ' + @ID
Set @strSQL = @strSQL + @WhereClause
The @ID contains an '\' and the error message says (EXEC ValidateUser 'AreaA', 'AD\JohnDoe') :
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '\'.
I have also received the incorrect column name too when playing around with the construct. Why would it be trying to parse the string? I have tried adding STR(@ID) with no luck.
October 23, 2006 at 3:22 pm
Always do
PRINT @strSQL
just before
EXEC (@strSQL)
Do it and you'll see your error by yourself.
_____________
Code for TallyGenerator
October 23, 2006 at 3:30 pm
Found it... ''' was needed around the string in the SELECT statement
October 23, 2006 at 4:23 pm
That statement looks very prone to SQL injection attacks.
October 24, 2006 at 6:57 am
And remember to remove the Print statement once it's all working
October 24, 2006 at 8:14 am
Could you elaborate on your statement? I created the stored procedure so the validation is not done through a select statement against the table itself, and the SP executed through an ASP intranet page. Suggestions to minimize risk?
October 24, 2006 at 9:17 am
With this statement:
Set @strSQL = 'SELECT FullName as UserFullName FROM Users'
Set @strSQL = @strSQL + ' WHERE USERID = ''' + @ID + ''''
Set @strSQL = @strSQL + @WhereClause
Imagin what the result would be if @id was ' OR 1=1 --
SELECT FullName as UserFullName FROM Users WHERE USERID = '' OR 1 = 1 -- ...
why not just use
SELECT FullName AS UserFullName FROM Users WHERE USERID = @ID
Not sure what you do for the @whereclause part, would need to know what was being passed here. The idea is to avoid using dynamic sql unless necessary, thus avoid using EXEC if at all possible.
October 25, 2006 at 3:06 pm
Thanks for your message Chris... very interesting, and thought invoking. The @ID represents the Session("AUTH_USER") variable passed when authenticating a user in a ASP page, and then passes that name to the table to see if they have been set up. Your '' or 1=1 is a concern, but I am not sure how else to use SQL Server to lock down an ASP page.
You stated "why not just use: SELECT FullName AS UserFullName FROM Users WHERE USERID = @ID", and I am not sure what you mean by that since that is the first part of the SELECT Statement.
The @WhereClause refers to an page/folder which is just a bit, so @whereclause could equal " AND SiteA = 1"
I am interested in your thoughts about limiting the exposure to injection attacks
October 25, 2006 at 8:20 pm
Sam,
I think what Chris was hinting at was why are you building a dynamic SQL statement and running it, instead of just running the statement "SELECT FullName AS UserFullName FROM Users WHERE USERID = @ID" without using the exec?
It's the dynamic SQL that leaves you open to an injection attack because your @ID is a string and obviously passed into your stored proc. If that was direct from a form of some sort, the user could physically enter '' or 1=1 or a bunch of nasty stuff for that matter.
If you don't have a specific need for the dynamic SQL statement, it's easier to use raw TSQL.
October 27, 2006 at 10:38 am
Thanks for your comments... I would prefer using the WHERE USERID = @ID, and it works just fine; however, the @whereclause part was my effort to utilize the same SP for three different apps/areas. The @whereclause adds to the where statement ' AND Area1 = 1', or ' AND Area2 = 1' etc... Since I can not created the statement as "SELECT FullName as UserFullName FROM Users WHERE UserID = @ID @WhereClause... how would I create this? Could I use a CASE statement in the select statement?
October 28, 2006 at 1:36 pm
While code reuse is a good goal to have, you shouldn't sacrifice the security of your site to attain it.
Before you consider using dynamic sql for anything, you should read and understand this page:
http://sommarskog.se/dynamic_sql.html
It should answer many if not all of your questions about dynamic sql.
btw, the '1=1' thing is nothing compared with other attacks you can do with a site that's vulnerable to injection. I won't give you an example because that would be innapropriate. suffice it to say you server could easily be rooted if xp_cmdshell is enabled. and even if it's not, an attacker can enable it for themselves if your connection has priveleges to do so.
---------------------------------------
elsasoft.org
October 31, 2006 at 8:36 am
Thank you for the link... I am using the sp_execute SP now, and see some of the benefits in it. However, since the userID contains '\' (domain\username), how is that passed to the stored procedured without opening it up to injection attacks... doesn't it need to be enclosed by " ' " to accept the \?
Also, is there a way to see what the compiled sp_executesql looks like to see if the parameters are passed correctly>
October 31, 2006 at 9:42 am
the \ character does not need to be escaped in t-sql. only the single quote character ' needs to be escaped. for example, try this:
select '\', 'abc\def', '', '''', 'she said: ''isn''t this cute?'''
You are probably thinking of C/C++/C# where \ is the escape character, so it DOES need to be escaped there.
not sure waht you mean by "see what the compiled sp_executesql looks like". Do you mean you want to look at the query plan?
---------------------------------------
elsasoft.org
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply