Dynamic SP to create where clause - ''/'' issue

  • 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.


    "Life without progression is entropy"
    Sam Jaynes

  • Always do

    PRINT @strSQL 

    just before

    EXEC (@strSQL)

    Do it and you'll see your error by yourself.

    _____________
    Code for TallyGenerator

  • Found it... ''' was needed around the string in the SELECT statement


    "Life without progression is entropy"
    Sam Jaynes

  • That statement looks very prone to SQL injection attacks.

  • And remember to remove the Print statement once it's all working

  • 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?


    "Life without progression is entropy"
    Sam Jaynes

  • 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.

  • 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


    "Life without progression is entropy"
    Sam Jaynes

  • 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.


    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • 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?


    "Life without progression is entropy"
    Sam Jaynes

  • 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

  • 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>


    "Life without progression is entropy"
    Sam Jaynes

  • 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