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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy