Tame Those Strings - Part 9

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/tamethosestringspart9.asp

  • I found that if I create a stored procedure as follows

    CREATE PROC usp_SetName @sName VARCHAR(50)




    INSERT Tbl_Name (Name)

    VALUES (@sName)


    Then executing the stored proc

    exec usp_SetName "O'Reilly" gets around a lot of thorny problems.

    The annoying thing is that I have found that the sp_dboption quoted identifier is not the same as the SET QUOTED_IDENTIFIER.

    I'm using SQL2K SP2

  • Instead of using replace, you could use the function QUOTENAME() which does the same thing and is built into SQL Server.

    select QUOTENAME('Frank''s Garage')


    [Frank's Garage]

  • It's also important to note that quite a bit of damage can be caused if this check is not done.

    Imagine the following situation where we have dynamic sql:

    'select count(*) from usertable where username = ''' + @username + ''' and password = ''' + @password + ''''

    and the user enters the data for a username:

    UserName: sa' or '1'='1

    Password: gobbledeegook

    That will get translated into dynamic sql as:

    select count(*) from usertable where username = 'sa' or '1'='1' and password = 'gobbledeegook'

    This select will always return the number of rows in usertable as '1'='1' will always be true. If the string was properly quoted, you'd get the following scenario:

    select count(*) from usertable where username = 'sa'' or ''1''=''1' and password = 'gobbledeegook'

    which would evaluate to false.

Viewing 4 posts - 1 through 3 (of 3 total)

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