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

Tony Johansson wrote:
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.

Tony Johansson (johansson.andersson (AT) telia (DOT) com) writes:
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 (AT) sommarskog (DOT) 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