Path: csiph.com!usenet.pasdenom.info!gegeweb.org!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail
From: "Bob Barrows" <reb01501@NOyahooSPAM.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: how to use case in sql query
Date: Mon, 9 Jul 2012 15:13:26 -0400
Organization: A noiseless patient Spider
Lines: 54
Message-ID: <jtfaja$vnu$1@dont-email.me>
References: <jtf216$633$1@dont-email.me>
Injection-Date: Mon, 9 Jul 2012 19:14:50 +0000 (UTC)
Injection-Info: mx04.eternal-september.org; posting-host="NUmboHSNYG4cX5Ys9j8TNw"; logging-data="32510"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX1+c8ASt9x5NKM7t7uXSqw1thX99NcvAac4="
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.2001
X-Newsreader: Microsoft Outlook Express 6.00.2800.2001
Cancel-Lock: sha1:emH3v3HryUPhl2Av5Zx7fQWbO2g=
X-Priority: 3
X-MSMail-Priority: Normal
Xref: csiph.com comp.databases.ms-sqlserver:1167

Tony Johansson wrote:
> Hello!
>
> The passed orderBy parameter string can consist of one of these  8
> possibilities
> The code is at the end.
>
> TicketID ASC
> TicketI DESC
> Priority ASC
> Priority DESC
> CreatedDate ASC
> CreatedDate DESC
> Owner ASC
> Owner DESC
>
> I want to sort in the way that the given parameter orderBy is
> specified. It would be easy to use string.format but I hope that I
> can avoid that because of sql injection
>
> I hope somebody know how to fix this in an easy way without using
> string.format.
> I tried below but I get error probably of not understanding this
> fully.
> It must be an easier way to use case then I have done below.
> It seems like a lot of writing whan I have the sort expression ready
> to be used.
>

The best solution  is to dynamically build the order by clause /based on/,
but _not using_,  the contents of the parameter.
Really.
Why?
1. It avoids the risk of sql injection
2. It avoids the kludge of using the CASE expression which can give the
query optimizer an insoluble problem.

Something like this (I don't know if c++ has a select case construct - it's
aircode anyways):
------------------------------------------------------
+     "ORDER BY "
if (orderBy=="TicketID ASC") query += " TicketID ASC";
elseif (orderBy=="TicketI DESC") query += " TicketI DESC";
...
else query += " TicketID ASC";
------------------------------------------------------

This way, there is no risk of sql injection since you are not concatenating
the actual untrusted data into your sql statement.

There really is no other easier solution. Parameters can be used to pass
only data values, not sections of the sql statement.