Executing a Stored Procedure the right way
-
Hello,
In QA if I call a procedure as
EXEC stpMySproc ' WHERE CompanyName LIKE ' + Char(39) + 'haas%' + Char(39)
It fails with this error...
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '+'.
however if I instead do this:
Declare @X as varchar(255)
SET @X = ' WHERE CompanyName LIKE ' + Char(39) + 'haas%' + Char(39)
EXEC stpMySproc @X
it runs fine... why would that be?
I want to just call it in a single line as I'm Calling it for an MS Access ADP Listbox.
Thanks,
-Francisco
-Francisco -
You cannot create a parameter from multiple parts in the stored procedure call. You have to build it ahead of time. Architectural limitation.
Steve Jones
-
quote:
You cannot create a parameter from multiple parts in the stored procedure call. You have to build it ahead of time. Architectural limitation.Thanks for your quick reply... i'm on a short deadline and little things like this are extending my timeline
Since the EXEC stpMySproc is going to be called from code... as "Exec stpMySproc" I should be able to provide the parameter at that step right? such as instead of saying ' WHERE CompanyName LIKE ' + Char(39) + 'haas%' + Char(39) I should be able to say WHERE CompanyName LIKE 'haas%' as the paramter right? however that for some reason doesn't work... what do you think?
-Francisco
-Francisco -
I think I understand what you are doing...I assume your stored proc is executing a dynamic SQL statement using a parameter as part of the WHERE clause.
I can't say that I recommend this method...anyway, your problem is that you have single quotes imbedded in the parameter. You need to do it like this:
EXEC stpMySproc ' WHERE CompanyName LIKE Char(39) + ''haas%'' + CHAR(39)'
Notice the use of 2 single quotes together.
-Dan
-Dan -
DJ,
Thanks for helping take this to the next level... after playing with it it turns out that the command must be executed in this manner...
EXEC stpMySproc ' WHERE CompanyName LIKE ''haas%'''
quote:
I assume your stored proc is executing a dynamic SQL statement using a parameter as part of the WHERE clause.I can't say that I recommend this method...
I'm concerned with this statement... why do you say this? Am I introducing a security hole? I know the potential for failure in this method with a malformed Where Clause... but was just wondering...
-Francisco
-Francisco -
Sorry about the CHAR(39) thing, I though you were looking for that value(') in the column itself, i.e.-'haas%'
The reasons that I say it's not recommended are that the server cannot optimize dynamic SQL, it must create an execution plan each time. The system procedure sp_executesql may be used instead of exec(). And you're right...it could cause a potential security risk. If somebody managed to pass "WHERE CompanyName LIKE ''ABC%'' TRUNCATE TABLE XYZ", you'd be in big trouble. If you totally control the parameters, then this is less of a problem.
-Dan
Edited by - dj_meier on 10/31/2002 2:50:45 PM
-Dan -
Ouch!
I see what you're saying... in a sense I'm leaving the entire database wide open! I suppose it would be better to script the columns to digits so the input paramter would read something like MySproc 1, ''haas%''. Thanks for pointing out this vulnerability.. I'm returning to the sproc sometime this afternoon and will test out this theory... but I will modify the paramters just because I'd rather be safe than sorry....
quote:
Sorry about the CHAR(39) thing, I though you were looking for that value(') in the column itself, i.e.-'haas%'The reasons that I say it's not recommended are that the server cannot optimize dynamic SQL, it must create an execution plan each time. The system procedure sp_executesql may be used instead of exec(). And you're right...it could cause a potential security risk. If somebody managed to pass "WHERE CompanyName LIKE ''ABC%'' TRUNCATE TABLE XYZ", you'd be in big trouble. If you totally control the parameters, then this is less of a problem.
-Dan
-Francisco
-Francisco
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply