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

Groups > comp.databases.ms-sqlserver > #1165 > unrolled thread

how to use case in sql query

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1165 — how to use case in sql query

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.

public DataSet GetTicketDetail(ListItem owner, string orderBy)
        {
            SqlCommand cmd;
            if (owner.Text == "*") // All tickets that has an owner
            {
                string query = "SELECT Tickets.TicketID, Tickets.HeadLine, 
Tickets.Description, " +
                            "Tickets.Priority, Tickets.CreatedTicket, 
Users.Owner, Tickets.Complete, TicketType.Name " +
                            "FROM Tickets " +
                            "JOIN Users ON " +
                            "Users.UserID = Tickets.UserID " +
                            "JOIN TicketType ON " +
                            "TicketType.TicketTypeID = Tickets.TicketTypeID 
" +
                            "where Tickets.UserID != @UserID " +
                            "ORDER BY " +
                            "CASE WHEN @orderBy = 'TicketID ASC' " +
                               "THEN TicketID END ASC, " +
                            "CASE WHEN @orderBy = 'TicketID DESC' " +
                               "THEN TicketID END DESC, " +
                            "CASE WHEN @orderBy = 'Priority ASC' " +
                               "THEN Priority END ASC, " +
                            "CASE WHEN @orderBy = 'Priority DESC' " +
                               "THEN Priority END DESC, " +
                            "CASE WHEN @orderBy = 'CreatedDate ASC' " +
                               "THEN CreatedDate END ASC, " +
                            "CASE WHEN @orderBy= 'CreatedDate DESC' " +
                               "THEN CreatedDate END DESC, " +
                             "CASE WHEN @orderBy= 'Owner ASC' " +
                               "THEN Owner END ASC, " +
                             "CASE WHEN @orderBy= 'Owner DESC' " +
                               "THEN Owner END DESC " +
                            "SELECT 
TicketDetail.TicketDetailID,TicketDetail.TicketID, TicketDetail.Action, " +
                            "Users.Owner, TicketDetail.CreatedDate, 
TicketDetail.UserID,TicketDetail.Status " +
                            "FROM [TicketDetail] " +
                            "JOIN Users ON " +
                            "Users.UserID = TicketDetail.UserID";

                cmd = new SqlCommand(query);
                cmd.Parameters.AddWithValue("@UserID", 
GetIDForNonAssignedTickets());
                return FillDataSet(cmd, "Tickets");
            }
}
//Tony

[toc] | [next] | [standalone]

#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.

[toc] | [prev] | [next] | [standalone]

#1169

Tony Johansson (johansson.andersson@telia.com) writes:
> I tried below but I get error probably of not understanding this fully.

And the error message is? At a glance it looks correct, but you
know error messages are damned helpful at times.

Then again, Bob's suggestion is worth pursuing, since that could
permit the optimizer to use a plan that evades a sort. With long
ORDER BY you have, the sort is inevitable.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web