I need to use variable in sql query on order by but get error “A constant expression was encountered in the ORDER BY list, position 1.”
strcom = string.Format("select Id,Number,Organization,Title,PublishYear from Document where Organization='{0}' AND Number like '%{1}%' AND Title like '%{2}%' AND PublishYear like '%{3}%' Order by '{4}'", orgs, nums, keys2, year, PageOrder);
5
The existing code is very much unsafe and WILL get your system hacked. It is NEVER okay to use string.Format()
to substitute data into a query.
Instead, you need access to the SqlCommand
object, or similar object provided by whatever data access library you use, so you can set parameter values. It might look something like this:
var sql = @"
SELECT Id,Number,Organization,Title,PublishYear
FROM Document
WHERE Organization= @Org AND Number like '%' + @Num + '%'
AND Title like '%' + @Title + '%'
AND PublishYear like '%' + @PubYear + '%'
ORDER BY ... ";
using var cn = new SqlConnection("connection string here");
using var cmd = new SqlCommand(sql, cn);
// use actual column types and sizes from the database
// Do NOT be tempted by the AddWithValue() option
cmd.Parameters.Add("@Org", SqlDbType.NVarChar, 30).Value = orgs;
cmd.Parameters.Add("@Num", SqlDbType.VarChar, 5).Value = nums;
cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 30).Value = keys2;
cmd.Parameters.Add("@PubYear", SqlDbType.VarChar, 4).Value = year;
cn.Open();
using var rdr = cmd.ExecuteReader();
// ...
These parameter values are never substituted directly into the SQL command string, not even on the database server. They are sent to the server in a separate data block and it is as if you had declared variables to use instead. In this way you are completely protected against injection issues.
But the Order By
is still a problem. You can’t supply the order by using query parameters, because it’s a expecting a column name instead of a specific value. If you have dynamic sorting, it’s often easier to do it in the client code. If you must set this in the SQL command string, do not accept direct user input for the value. Instead, use a switch
or similar to put hard-coded values into the SQL command.
Finally, I need to talk about those leading wildcards. For example:
AND PublishYear like '%' + @PubYear + '%'
This technique breaks the ability for the database to use indexes, which cuts to the core of database performance. Without exaggeration, it can be a difference of multiple orders of magnitude… queries might go from running effectively instantly to taking multiple minutes or even longer. When you need it, you need it, but it should not be the default way you do a search. Avoid offering “contains” searches like this, and when you absolutely need them look into fulltext indexing instead.