Re: what is wrong with this stored procedure?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 12/03/04 

Date: Fri, 03 Dec 2004 10:11:42 +0100

On Thu, 2 Dec 2004 08:07:06 -0800, Robert Davis wrote:

>I would suggest following Aaron's advice, but just wanted to throw out
>another alternative. You could execute the query dynamically:

Hi Robert,

That would be extremely dangerous!

>CREATE PROCEDURE SP_CheckStatus2
> @mylist varchar(1000)
>as
>Declare @SQL varchar(2000)
>Set @SQL = 'select name, emailname from tbl_person where name in (' +
>@mylist + ') or emailname in (' + @mylist + ')'
>
>Exec(@SQL)
>GO

Just try what happens if you call this procedure as follows:

CALL SP_CheckStatus2 '''Jerry'', ''Tom''); DROP TABLE tbl_person; --'

(Hint: change Exec to print first)

http://www.sommarskog.se/dynamic_sql.html

Also (to the OP): you should not start your procedure names with sp_; this
name gets special treatment from SQL Server.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Reinstall IE6 in W2000
    ... I am unable to pursue your "hint" at the moment as I am working from home ... with a machine running W98. ... clarify what I am trying to "find before and after the above command". ... Robert Aldwinckle ...
    (microsoft.public.windows.inetexplorer.ie6.setup)
  • Re: driving_site hint in Materialized View definition
    ... a distributed DML statement must execute on the database ... The DRIVING_SITE hint cannot override this. ... I think if you search metalink, you'll find a note that makes ...
    (comp.databases.oracle.server)
  • Re: Transaction deadlock
    ... if i don't execute 2 same SELECT statement.. ... > One thing you might try is to execute the SELECT with the nolock hint. ... >>selecting records within Transaction?? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: NY Governor Jokes
    ... Not at all, Robert. ... Patrick McGoohan said it best in his ... I never will understand the concept of associating money with sex. ... What good is being an executive if you never get to execute anyone?- Hide quoted text - ...
    (alt.fan.letterman)
  • Re: GetProcessId API call
    ... ProcessId = VBA.Shell("Notepad", vbNormalFocus) ... I was thinking about why Robert is getting 'random' numbers. ... as Explorer.exe do when we execute a sentence like ... randomly works and fails with any executable. ...
    (microsoft.public.vb.winapi)