April 15, 2011 at 12:54 pm
When a variable in my stored procedure contains an apostrophe, it returns me an error.
CREATE PROCEDURE sp_GetEZQueryList @@sUser as nchar(500), @@sSupervisor as char(1), @@sAllowed as varchar(8000) AS
--set @@sUser = REPLACE(@@sUser, '''''','''''')
IF @@sSupervisor <> 'Y'
SELECT
EzqDateMod, EzqFlxID, EzqUserName, EzqName, EzqData, EzqDescrip
FROM EZQuery
WHERE (EzqUserName=@@sUser)
OR (EzqUserName='SYSTEM' AND Cast(EzqFlxID as varchar(10)) IN (select * FROM intlist_to_tbl(@@sAllowed)))
ELSE
SELECT
EzqDateMod, EzqFlxID, EzqUserName, EzqName, EzqData, EzqDescrip
FROM EZQuery
GO
The SP is retrieving @@sUser from VB and its getting the value Do'Sullivan.
When I ran a trace I found it is executing the SP like this
exec sp_getezquerylist 'DO' + CHAR(39) + 'SULLIVAN', 'n', '100'
Think + CHAR(39) + is causing the error
Thanks
Waiting for your suggestions
April 17, 2011 at 6:12 am
If you are placing a single quote within a string like this : 'O'Sullivan'. It actually has to be marked up like this: 'O''Sullivan' Note, that is two single quotes, not a double quote. Two single quotes in a row inside of a set of single quotes is translated as a single quote.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 17, 2011 at 7:09 am
How are you calling this from VB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2011 at 3:08 am
Hello,
Grant, it is true that single quotes create troubles but when they are used in dynamic SQL queries; in this case the query is static, single quotes should not produce an error.
nikhil_2501, you say that SQL Server returns an error... colud you specify what error?.
Supposing that EZQuery is a query and not a table, you can try to substitute EZQuery by its definition; it is, you can try to change "FROM EZQuery" by "FROM (SELECT ...) AS EZQuery" and run it, maybe it gives you a clue.
Regards,
Francesc
April 18, 2011 at 10:20 am
An easy way around this is to replace the offending character when receiving the input from the user. I used to like to replace any special characters with a different special character that doesn't cause issues or leave the application open to injection. For instance, replace the single quote/apostrophe with a tilde ~ in the front end and translate it back with a character string token on the back end. This way you are sure to avoid broken queries and injection is mitigated even when using dynamic queries.
I've recently switched to doing someting even better. I am now using unicode representations, which for the single quote is U+0027.
http://www.joelonsoftware.com/articles/Unicode.html
So you would do a replace on the front end to DoU+0027Sullivan, and possibly even store the data that way on the backend, or just
REPLACE('DoU+0027Sullivan', 'U+0027', CHAR(39))
It seems like a lot of extra work, but I now have reusable code to check all of my form data for special characters and always store them using unicode representations. Writing them back to the application is as simple as reversing U+0027 to a single quote.
string.Replace("U+0027", Convert.ToChar(39))
The main idea is that you use character set tokens rather than the actual character.
Joshua Perry
http://www.greenarrow.net
April 18, 2011 at 10:32 am
Your error is very simple: You cannot use an expression as a parameter of a stored procedure
Change this:
exec sp_getezquerylist 'DO' + CHAR(39) + 'SULLIVAN', 'n', '100'
to this:
declare @@sUser as nchar(500)
set @@sUser = 'DO' + CHAR(39) + 'SULLIVAN'
exec sp_getezquerylist @@sUser, 'n', '100'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply