March 16, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/tamethosestringspart9.asp
April 4, 2002 at 12:51 am
I found that if I create a stored procedure as follows
CREATE PROC usp_SetName @sName VARCHAR(50)
AS
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
INSERT Tbl_Name (Name)
VALUES (@sName)
GO
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
April 4, 2002 at 2:00 pm
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')
returns:
[Frank's Garage]
April 4, 2002 at 2:11 pm
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