Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1167

Re: how to use case in sql query

Show all headers | View raw

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.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

how to use case in sql query "Tony Johansson" <johansson.andersson@telia.com> - 2012-07-09 18:48 +0200
  Re: how to use case in sql query "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-07-09 15:13 -0400
  Re: how to use case in sql query Erland Sommarskog <esquel@sommarskog.se> - 2012-07-09 23:47 +0200

csiph-web