[thelist] mssql stored procedure-using a variable for 'AND' and'OR'

Ken Schaefer Ken at adOpenStatic.com
Mon Aug 25 22:11:40 CDT 2008

> -----Original Message-----
> From: thelist-bounces at lists.evolt.org [mailto:thelist-bounces at lists.evolt.org]
> On Behalf Of Jay Turley
> Subject: Re: [thelist] mssql stored procedure-using a variable for 'AND'
> and'OR'
> On Mon, Aug 25, 2008 at 4:58 PM, Joel D Canfield <joel at bizba6.com> wrote:
> >> Note: if you are building an SQL statement dynamically inside
> >> your sproc, and then executing that string, then you are
> >> defeating the purpose of using parametised queries - you
> >> might as well just use inline SQL.
> >
> > makes sense
> Just wanted to point out that - while I didn't include the script -
> the parameters were passed in classic ASP through command object
> parameters to provide the type and injection protection that that
> inline sql can't.

But you're still executing some text that you're creating inside the sproc. If someone passes in (as their search parameter):

Searchterm1, searchterm2, ';DROP thisTable--

Then you're going to end up splitting that text up, building an SQL statement, and then executing it. The ADO command parameter at the ASP page level isn't going to help - it's just going to preserve the integrity of the parameter value you are passing in :-)

> This was not the best example because there was not
> a strong need to build the SQL inside the proc, whereas in other very
> special cases, there was no other solution I could find.

Oh - I absolutely agree - there are cases where dynamic SQL in your sproc (or inline in the page) is an order of magnitude easier than any other possible alternative.

> >> If this is ASP.NET
> >
> > it's not, it's classic ASP. what next?
> >
> If you still trust me after Ken's stinging rebuke I'm willing to help.

Hey - I'm not rebuking you. :-)

Just trying to point out to Joel that if the purpose of using a sproc is to protect against SQL injection, then he's not actually gaining anything substantial if he has to build an arbitrary string in his sproc and then execute it.


More information about the thelist mailing list