[dba-SQLServer]Passing the SQL Setting to a stored procedure

That's not my understanding and not my experience either.  Sprocs are
designed to accept input parameters and still compile even though the
value for the parameter changes every time it's run.  This is one of the
reaons why you can't do some more complicated SQL manipulation (such as
"where myfield in ('abc', 'def', 'ghi')") in a sproc and still have it
compile.  You can do that in a sproc, but you have to do it with more
dynamic SQL and then it doesn't compile and is in fact slower in my
experience.  Depending on the number of users and the amount of data,
this may or may not be an issue for you.

That said, if you want to address the security hold that others are
talking about and still use your method, some simple error handling
could addres this.  You could check the string that is passed for some
key words and execute it only if you find it safe.  Be sure to check for
the presenece of a semicolon as this sometimes allow a new sql statement
to execute and I have heard of hackers wiping out database by typing in
"select * from mytable; drop table mytable".  

--Susan


>  -----Original Message-----
> From: 	dba-sqlserver-admin at databaseadvisors.com
> [mailto:dba-sqlserver-admin at databaseadvisors.com]  On Behalf Of John
> W. Colby
> Sent:	Tuesday, March 04, 2003 5:26 PM
> To:	dba-sqlserver at databaseadvisors.com
> Subject:	RE: [dba-SQLServer]Passing the SQL Setting to a stored
> procedure
> 
> But what I am reading is that if you are talking about a situation
> like I am discussing, where the filter changes every time the sproc is
> used, the compile is actually counterproductive.
> 
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
> 
> 
> I've stopped 9,258 spam messages. You can too!
> Get your free, safe spam protection at
> http://www.cloudmark.com/spamnetsig/
> 
> -----Original Message-----
> From: dba-sqlserver-admin at databaseadvisors.com
> [mailto:dba-sqlserver-admin at databaseadvisors.com]On Behalf Of Susan
> Zeller
> Sent: Tuesday, March 04, 2003 6:15 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: RE: [dba-SQLServer]Passing the SQL Setting to a stored
> procedure
> 
> 
> Another big problem is that this is like tying the hands of the sproc.
> Part of the power of the sproc is its efficiency from compiling.
> Sprocs
> that execute dynamic sql do not compile.  
> 
> --Susan
> 
> -----Original Message-----
> From: David McAFee (Home) [mailto:dmcafee at pacbell.net] 
> Sent: Tuesday, March 04, 2003 4:49 PM
> To: dba-sqlserver at databaseadvisors.com; AccessD
> Subject: RE: [dba-SQLServer]Passing the SQL Setting to a stored
> procedure
> 
> 
> Yes, but why not build it in the back, then any changes that would
> need
> to be made, could be made in the BE (by modifying the SPROC) without a
> need for a FE update? There is also a security risk involved, if
> anyone
> was to ever figure out the sproc's name you could possibly send a
> TRUNCATE command or some other ill script (or at least that's what I
> was
> told when I wanted to do this :) )
> 
> HTH
> 
> David McAfee
> -----Original Message-----
> From: dba-sqlserver-admin at databaseadvisors.com
> [mailto:dba-sqlserver-admin at databaseadvisors.com]On Behalf Of John W.
> Colby
> Sent: Tuesday, March 04, 2003 2:36 PM
> To: AccessD-SQLServer; AccessD
> Subject: [dba-SQLServer]Passing the SQL Setting to a stored procedure
> 
> 
> I just had a fascinating idea.  I do a lot of building up of SQL
> Strings
> then executing them in A2K.  Would it be possible to build a stored
> procedure where the parameter passed in is the SQL statement to be
> executed? IOW, do the same thing we do now, manually build a SQL
> string
> with the actual values of controls in where clauses etc., then pass
> that
> string to a stored procedure and have the stored procedure execute the
> SQL string and hand back the data?
> 
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 
> 
> 
> ----------------------------------------------------
> Is email taking over your day?  Manage your time with eMailBoss.  
> Try it free!  http://www.eMailBoss.com